Search Criteria (1 Viewer)

[nitda1]

I would be extremely grateful if any member can help me with a problem I am having. I have been constructing a data base to hold information about recipes (not the actual recipe itself) in my wife’s 241 cookery books. There are a number of ways the information can be retrieved, one of which is “A Search by Criteria” form. This form has five controls , mainly category, course, country, region and speciality. Each control has a drop down list for selection purposes. My problem is with the country and region controls. The Country control lists country of recipe (when one is given). The Region control lists regions of the world i.e. Africa, Asia, Europe, Middle East, North America, South America. South East Asia and Sub Continent. Obviously it is possible to have a recipe from Asia but not from a specific country whilst if the selected country is China then the region must be Asia. The default for the country is “NONE GIVEN” I would like the region control to be available only if no country is selected or for a region to be automatically entered depending on the Country entered. Either way would be acceptable. Once the criteria is entered a search button is activated and the results are display at the bottom of the form. The search botton has the following code on the “On Click” property.

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 tblRECIPE.NUMBER, tblRECIPE.RECIPES, tblRECIPE.BOOK, tblRECIPE.BOOKNUMBER, tblRECIPE.BOOKCASE, tblRECIPE.SHELF, tblRECIPE.PAGE " & _
"FROM tblRECIPE"

strWhere = "WHERE"

strOrder = "ORDER BY tblRECIPE.NUMBER;"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form

If Not IsNull(Me.txtCATAGORY) Then '<--If the textbox txtCATAGORY contains no data THEN do nothing
strWhere = strWhere & " (tblRECIPE.CATAGORY) Like '*" & Me.txtCATAGORY & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCOURSE) Then
strWhere = strWhere & " (tblRECIPE.COURSE) Like '*" & Me.txtCOURSE & "*' AND"
End If

If Not IsNull(Me.txtCOUNTRY) Then
strWhere = strWhere & " (tblRECIPE.COUNTRY) Like '*" & Me.txtCOUNTRY & "*' AND"
End If

If Not IsNull(Me.txtREGION) Then
strWhere = strWhere & " (tblRECIPE.REGION) Like '*" & Me.txtREGION & "*' AND"
End If

If Not IsNull(Me.txtSPECIALITY) Then
strWhere = strWhere & " (tblRECIPE.SPECIALITY) Like '*" & Me.txtSPECIALITY & "*' AND"
End If

If Not IsNull(Me.txtRECIPES) Then
strWhere = strWhere & " (tblRECIPE.RECIPES) Like '*" & Me.txtRECIPES & "*' AND"
End If

If Not IsNull(Me.txtBOOK) Then
strWhere = strWhere & " (tblRECIPE.BOOK) Like '*" & Me.txtBOOK & "*' AND"
End If

If Not IsNull(Me.txtBOOKNUMBER) Then
strWhere = strWhere & " (tblRECIPE.BOOKNUMBER) Like '*" & Me.txtBOOKNUMBER & "*' AND"
End If

If Not IsNull(Me.txtPAGE) Then
strWhere = strWhere & " (tblRECIPE.PAGE) Like '*" & Me.txtPAGE & "*' AND"
End If

If Not IsNull(Me.txtBOOKCASE) Then
strWhere = strWhere & " (tblRECIPE.BOOKCASE) Like '*" & Me.txtBOOKCASE & "*' AND"
End If

If Not IsNull(Me.txtSHELF) Then
strWhere = strWhere & " (tblRECIPE.SHELF) Like '*" & Me.txtSHELF & "*' 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
I am very new to database construction and I have been playing the above code trying combinations of If…..Then statements. But getting nowhere. Sorry if this is a bit long winded.

Thank you in anticipation of assistance

Regards Nitda1

 

[John Big Booty]

Perhaps the sample posted here will be of interest to you.

 

[John Big Booty]

P.S. When posting code please use the Code wrapper to enclose you code and make it easier to follow.

 

[nitda1]

Thank you very much for the code and on advice on using threads. Shall remember next time. As it is now 1:20 am here in Europe I shall be trying the code first thing. Thanks once again
Nitda1