Limit Form Results with Multiple Combo Boxes

[Jason]

Good Monday Morning All! Hope you all had a great
weekend. I have a form bound to a query. I would like to
place 3 combo boxes (cboCity, cboDate, cboTime) at the top
of the form. When the form is first loaded it displays all
records. As the user makes their choices from the combo
boxes, I would like to have the form reflect their choices
as each selection is made. Any suggestions or links to
resources would be appreciated.

 

[Allen Browne]

What you are asking to do is to apply a filter to the form, based on any
combination of the non-null values in the 3 combos. Use the AfterUpdate
event procedure to build the filter string, and apply it to the form.

This kind of thing:

Private Sub cboCity_AfterUpdate
Dim strFilter As String
Dim lngLen As Long

If Me.Dirty Then 'Save any changes.
Me.Dirty = false
End If

If Not IsNull(Me.cboCity) Then
strFilter = strFilter & "([MyCityField] = """ & _
Me.cboCity & """) AND "
End If

If Not IsNull(Me.cboDate) Then
strFilter = strFilter & "([MyDateField] = " & _
Format(Me.cboDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

If Not IsNull(Me.cboTime) Then
strFilter = strFilter & "([MyTimeField] = " & _
Format(Me.cboDate, "\#hh\:nn\:ss\#") & ") AND "
End If

lngLen = Len(strFilter) - 5 'Without trailing " AND ".
If lngLen < 1 Then
Me.FilterOn = False
Else
Me.Filter = Left(strFilter, lngLen)
Me.FilterOn = True
End If
End Sub

Private Sub cboDate_AfterUpdate
Call cboCity_AfterUpdate
End Sub

Private Sub cboTime_AfterUpdate
Call cboCity_AfterUpdate
End Sub