Search Function (2000)

Topic

New Reply

This is a follow on from post 300379, I have a combo box to search for records in a form, this works fine when searching by one specific field as one would expect. How can I search by a combination of fields?

Thanks Darren.

Reply | Quote

Replies

Say that you want to search on a combination of two fields Field1 and Field2. You could create two combo boxes, one for each field. Do you want to allow the user to leave a combo box empty, or do you want to search only if an item has been selected in both combo boxes?

Reply | Quote

Thanks Hans, yes I would like the search to be either on both fields selected or just one and the other blank.

Reply | Quote

Here is example code for three combo boxes; it can easily be adapted for two combo boxes or for more than three combo boxes. The names of the combo boxes are cbxField1, cbxField2 and cbxField3, and they let the user select values for fields named Field1, Field2 and Field3, respectively. For illustration purposes, the first is numeric, the second text and the third a date field. Of course, you must substitute the appropriate names, and adjust for field types.

The following function must be called in the After Update event of each of the combo boxes.

Private Function DoSearch()
Dim rs As Object
Dim strFilter As String

‘ Assemble filter string

‘ Field1 is numeric
If Not IsNull(Me.cbxField1) Then
strFilter = strFilter & _
” And [Field1] = ” & Me.cbxField1
End If

‘ Field2 is text
If Not IsNull(Me.cbxField2) Then
strFilter = strFilter & _
” And [Field2] = ” & Chr(34) & Me.cbxField2 & Chr(34)
End If

‘ Field3 is a date
If Not IsNull(Me.cbxField3) Then
strFilter = strFilter & _
” And [Field3] = #” & Format(Me.cbxField3, “mm/dd/yy”) & “#”
End If

If strFilter = “” Then
‘ Nothing to search for
Exit Function
End If

‘ Get rid of first ” And ”
strFilter = Mid(strFilter, 6)

‘ This is the search code
Set rs = Me.Recordset.Clone
rs.FindFirst strFilter
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End Function

Reply | Quote

I am trying to set up a form, frmFolio, which will be used as the search criteria for qryFolio. The form will have 2 combo boxes: cbxCombinedName and cbxRoomNumber. I want to be able to run the query based on just the CombinedName or just the Room Number or based on both CombinedName and RoomNumber.

At present my form has just the CombinedName combo box and a Command Button, Run Select Query. The On Click Event is as follows:

Private Sub Run_Select_Query_Click()
On Error GoTo Err_Run_Select_Query_Click

Dim stDocName As String

stDocName = “qryFolio”
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_Select_Query_Click:
Exit Sub

Err_Run_Select_Query_Click:
MsgBox Err.Description
Resume Exit_Run_Select_Query_Click

End Sub

I have tried to adapt the code in post 301130 but thus far have been unsuccessful. The After Update event in cbxCombinedName is as follows:

Private Sub cbxCombinedName_AfterUpdate()
Private Function DoSearch()
Dim rs As Object
Dim strFilter As String

‘ Assemble filter string

‘ Field2 is text
If Not IsNull(Me.cbxCombinedName) Then
strFilter = strFilter & _
” And [Field2] = ” & Chr(34) & Me.cbxCombinedName & Chr(34)
End If

If strFilter = “” Then
‘ Nothing to search for
Exit Function
End If

‘ Get rid of first ” And ”
strFilter = Mid(strFilter, 6)

‘ This is the search code
Set rs = Me.Recordset.Clone
rs.FindFirst strFilter
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End Function

End Sub

The criteria in the criteria field of qryFolio is Forms![frmFolio]![cbxCombinedName]

When the frmfolio is opened and a name is selected from the combo box, I get thefollowing error message:

Compile Error
Expected End Sub

Reply | Quote

Hello Tom,

In the first place, you can’t nest a function definition within a procedure definition:

Private Sub cbxCombinedName_AfterUpdate()
Private Function DoSearch()
…
End Function
End Sub

The idea was to define a function

Private Function DoSearch()
…
End Function

and call it from the event procedures:

Private Sub cbxCombinedName_AfterUpdate()
DoSearch
End Sub

In the second place, you’re trying to use the code from post 301130 for a purpose it wasn’t intended for. The purpose of the code is to filter the form containing the controls; it can’t be used to filter a query. In my databases, I never let the user work with a table or query directly, all interaction is through forms. I would suggest that you do the following (test on a copy of your database!):

– Remove the criteria from the query.
– Create a continuous form based on the query.
– Put the combo boxes in the form header or footer.
– Use the code to filter the form.

Reply | Quote

Hans,

I have a frmFolio which has a text box, txtRoomNumber and a combo box, cbxCombinedName. The form is the source for the selection criteria on qryFolio. The query works fine as long as both values are entered. What I am trying to do is to get it to work when only one value is entered. The query ultimately will the source of a report.

Any suggestions would be welcomed.

Tom

Reply | Quote

Try the following on a copy of the query:

– In the first empty column in the query design grid, enter [Forms]![frmFolio]![txtRoomNumber]
– Clear the Show check box for this column.
– In the Criteria row for this column, enter [RoomNumber] Or Is Null (I assumed that the text box is meant to filter a field named RoomNumber.)

– In the next empty column in the query design grid, enter [Forms]![frmFolio]![cbxCombinedName]
– Clear the Show check box for this column.
– In the Criteria row for this column, enter [CombinedName] Or Is Null (I assumed that the text box is meant to filter a field named CombinedName.)

– Remove the original criteria in the columns for RoomNumber and CombinedName.

Reply | Quote

Many thanks. Works like a charm.

I modified the Command Button on frmFolio which opens qryFolio and then opens rptFolio in preview to give the operator a chance to review the report before printing.

Thanks again.

Reply | Quote

Many thanks. Works like a charm.

I modified the Command Button on frmFolio which opens qryFolio and then opens rptFolio in preview to give the operator a chance to review the report before printing.

Thanks again.

Reply | Quote

Try the following on a copy of the query:

– In the first empty column in the query design grid, enter [Forms]![frmFolio]![txtRoomNumber]
– Clear the Show check box for this column.
– In the Criteria row for this column, enter [RoomNumber] Or Is Null (I assumed that the text box is meant to filter a field named RoomNumber.)

– In the next empty column in the query design grid, enter [Forms]![frmFolio]![cbxCombinedName]
– Clear the Show check box for this column.
– In the Criteria row for this column, enter [CombinedName] Or Is Null (I assumed that the text box is meant to filter a field named CombinedName.)

– Remove the original criteria in the columns for RoomNumber and CombinedName.

Reply | Quote

Hans,

I have a frmFolio which has a text box, txtRoomNumber and a combo box, cbxCombinedName. The form is the source for the selection criteria on qryFolio. The query works fine as long as both values are entered. What I am trying to do is to get it to work when only one value is entered. The query ultimately will the source of a report.

Any suggestions would be welcomed.

Tom

Reply | Quote

Hello Tom,

In the first place, you can’t nest a function definition within a procedure definition:

Private Sub cbxCombinedName_AfterUpdate()
Private Function DoSearch()
…
End Function
End Sub

The idea was to define a function

Private Function DoSearch()
…
End Function

and call it from the event procedures:

Private Sub cbxCombinedName_AfterUpdate()
DoSearch
End Sub

In the second place, you’re trying to use the code from post 301130 for a purpose it wasn’t intended for. The purpose of the code is to filter the form containing the controls; it can’t be used to filter a query. In my databases, I never let the user work with a table or query directly, all interaction is through forms. I would suggest that you do the following (test on a copy of your database!):

– Remove the criteria from the query.
– Create a continuous form based on the query.
– Put the combo boxes in the form header or footer.
– Use the code to filter the form.

Reply | Quote

I am trying to set up a form, frmFolio, which will be used as the search criteria for qryFolio. The form will have 2 combo boxes: cbxCombinedName and cbxRoomNumber. I want to be able to run the query based on just the CombinedName or just the Room Number or based on both CombinedName and RoomNumber.

At present my form has just the CombinedName combo box and a Command Button, Run Select Query. The On Click Event is as follows:

Private Sub Run_Select_Query_Click()
On Error GoTo Err_Run_Select_Query_Click

Dim stDocName As String

stDocName = “qryFolio”
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_Select_Query_Click:
Exit Sub

Err_Run_Select_Query_Click:
MsgBox Err.Description
Resume Exit_Run_Select_Query_Click

End Sub

I have tried to adapt the code in post 301130 but thus far have been unsuccessful. The After Update event in cbxCombinedName is as follows:

Private Sub cbxCombinedName_AfterUpdate()
Private Function DoSearch()
Dim rs As Object
Dim strFilter As String

‘ Assemble filter string

‘ Field2 is text
If Not IsNull(Me.cbxCombinedName) Then
strFilter = strFilter & _
” And [Field2] = ” & Chr(34) & Me.cbxCombinedName & Chr(34)
End If

If strFilter = “” Then
‘ Nothing to search for
Exit Function
End If

‘ Get rid of first ” And ”
strFilter = Mid(strFilter, 6)

‘ This is the search code
Set rs = Me.Recordset.Clone
rs.FindFirst strFilter
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End Function

End Sub

The criteria in the criteria field of qryFolio is Forms![frmFolio]![cbxCombinedName]

When the frmfolio is opened and a name is selected from the combo box, I get thefollowing error message:

Compile Error
Expected End Sub

Reply | Quote

Here is example code for three combo boxes; it can easily be adapted for two combo boxes or for more than three combo boxes. The names of the combo boxes are cbxField1, cbxField2 and cbxField3, and they let the user select values for fields named Field1, Field2 and Field3, respectively. For illustration purposes, the first is numeric, the second text and the third a date field. Of course, you must substitute the appropriate names, and adjust for field types.

The following function must be called in the After Update event of each of the combo boxes.

Private Function DoSearch()
Dim rs As Object
Dim strFilter As String

‘ Assemble filter string

‘ Field1 is numeric
If Not IsNull(Me.cbxField1) Then
strFilter = strFilter & _
” And [Field1] = ” & Me.cbxField1
End If

‘ Field2 is text
If Not IsNull(Me.cbxField2) Then
strFilter = strFilter & _
” And [Field2] = ” & Chr(34) & Me.cbxField2 & Chr(34)
End If

‘ Field3 is a date
If Not IsNull(Me.cbxField3) Then
strFilter = strFilter & _
” And [Field3] = #” & Format(Me.cbxField3, “mm/dd/yy”) & “#”
End If

If strFilter = “” Then
‘ Nothing to search for
Exit Function
End If

‘ Get rid of first ” And ”
strFilter = Mid(strFilter, 6)

‘ This is the search code
Set rs = Me.Recordset.Clone
rs.FindFirst strFilter
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End Function

Reply | Quote

Thanks Hans, yes I would like the search to be either on both fields selected or just one and the other blank.

Reply | Quote