Filtering on form

[boss hogg]

Newbie with not much scripting skill - please treat me gently


I am using the following code to filter a form (by an option group) to
show records. Currently, all records are shown in the non-filtered
state. However, i need to only shows jobs that have been dispatched
(GONE field = YES). How do I add this into this code?

Another option is that I have a query called 'booking out filter'. I
could use this as the record source because this filters out jobs
where GONE = NO.

I'd really appreciate any help - 6 hours spent on it so far, and still
no closer to getting it to work !

Script is.........


Private Sub Command75_Click()

'Variable to hold filtered SQL string
Dim strFilterSQL As String

'Set default record source of form

Const strSQL = "SELECT [jb-2001].JobNo, [jb-2001].DATE,
[jb-2001].CUSTOMER, [jb-2001].CONTACT, [jb-2001].JOB_REF,
[jb-2001].ORDER_NO, [jb-2001].WORKTYPE, [jb-2001].IN_TIME,
[jb-2001].DUE, [jb-2001].GONE FROM [jb-2001]"

Select Case Me!Frame58
'Filter record source dependant on option checked
Case 1
strFilterSQL = strSQL & " Where [WORKTYPE] =
'Photo-Plot';"
Case 2
strFilterSQL = strSQL & " Where [WORKTYPE] =
'Photo-Mask';"
Case 3
strFilterSQL = strSQL & " Where [WORKTYPE] = 'Scanning';"
Case 4
strFilterSQL = strSQL & " Where [WORKTYPE] = 'PCB';"
Case 5
strFilterSQL = strSQL & " Where [WORKTYPE] = 'Film';"
Case 6
strFilterSQL = strSQL & " Where [WORKTYPE] = 'CAD/CAM';"
Case 7
strFilterSQL = strSQL & " Where [WORKTYPE] = 'Other';"



'If filter applied with no option selected use default record
source
' Case Else
' strFilterSQL = strSQL & ";"
End Select

' Set record source with filtered SQL
Me.RecordSource = strFilterSQL
Me.Requery

End Sub


Private Sub Command76_Click()

' Restores the Record Source back to that of
'of the original form

Me.RecordSource = strSQL

End Sub





regards,

JD