drop down box and search

[siamsunset]

Hi everyone,
I have set up a search form that searches information in a table related to whatever is typed into a text box, it returns the matches in a list box and i can then double click the result and open it in another form. We are a printing firm and i use this to search for jobs by name, number or type of printing job. It works fine with words in text boxes but what i would love to do is to be able to do is show jobs that are completed or not completed using a drop down box with yes/no values. If no value is selected it shows all jobs completed or not. If yes is selected is shows just completed jobs ect.....I am a total access noob so im not sure how to go about this the right way. Here is a list of the code i have for the text box search.

Option Compare Database

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT jobsinfo.JobNumber, Jobsinfo.JobNumber, jobsInfo.FirstName, jobsinfo.LastName, jobsinfo.company,  jobsinfo.jobtype " & _
"FROM jobsinfo"

strWhere = "WHERE"

strOrder = "ORDER BY jobsinfo.JobNumber;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtFName) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (Jobsinfo.JobNumber) Like '*" & Me.txtFName & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtLName) Then
strWhere = strWhere & " (jobsinfo.LastName) Like '*" & Me.txtLName & "*'  AND"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & " (jobsinfo.Company) Like '*" & Me.txtCity & "*'  AND"
End If

If Not IsNull(Me.txtState) Then
strWhere = strWhere & " (jobsinfo.jobtype) Like '*" & Me.txtState & "*'  AND"
End If


'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub



Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open tblinfo based on the ID from lstCustInfo listbox

DoCmd.OpenForm "jobsform", , , "[Jobnumber] = " & Me.lstCustInfo, , acDialog

End Sub

Private Sub txtCity_BeforeUpdate(Cancel As Integer)

End Sub




Thanks alot in advance
Ben

 

[Norie]

Ben

How are you determining whether a job has been completed?