Using a combo box as a filter

[Maver1ck666]

I am having some difficulties setting a combo box as a filter on a main form.

I have a single form with the record source set as a query. Within the
detail section, it shows all the records currently logged on the database
which can be scrolled down (currently about 150 records showing).

What I want to do is to add a number of combo boxed as filteres iin the form
header which will

a) provide a list based on what has been logged

&

b) when a value is selected, show all records that match.

I currently have a number of unbound text boxes with an event procedure on
after update set as:

Private Sub Filter1_AfterUpdate()

'sets the filter1
DoCmd.ApplyFilter , "" & Me.Filter1.Tag & " = " & Me.Filter1 & ""

End Sub

No this method works fine but I obviously dont get a selection list to
choose from.

I then created a combo box, used the wizard to select the list of entries
based on the field in the query then adapted the sql code so that it groups
the exact matches together. I can't however, seem to get the code behind the
combo box to apply the selected criteria in the detail section.

Any ideas please?

 

[NKTower]

Private Sub cbo_Filter_1_OnClick() ' or update as you prefer
Update_Filter
End Sub
Private Sub cbo_Filter_2_OnClick()
Update_Filter
End Sub
....
Private Sub cbo_Filter_n_OnClick()
Update_Filter
End If

Private Sub Update_Filter
Dim str_Filter As String
Dim CRLF As String
Dim int_N_Clauses As Integer

CRLF = Chr$(13) & Chr$10) ' used to assist in examining filter in
immediate window if you need it

str_Filter = "(" & CRLF
int_N_Clauses = 0

If Not IsNull(cbo_Filter_1) Then
int_N_Clauses = int_N_Clauses + 1
If int_N_Clauses > 1 Then
str_Filter = str_Filter & " AND "
End If
str_Filter = " ( [Filter_1_data_column] = """ & cbo_Filter_1 & """
) " & CRLF
End If

If Not IsNull(cbo_Filter_2) Then
int_N_Clauses = int_N_Clauses + 1
If int_N_Clauses > 1 Then
str_Filter = str_Filter & " AND "
End If
str_Filter = " ( [Filter_2_data_column] = """ & cbo_Filter_2 & """
) " & CRLF

.... etc. for other combo boxes as needed

str_Filter = str_Filter & ")"
Debug.Print str_Filter
If int_N_Clauses > 0 Then
Me.Filter = str_Filter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub

note: Remove the double quotes if data column being filtered isn't text.
For example -

str_Filter = " ( [Filter_2_numeric_column] = " & cbo_Filter_2 & ")" & CRLF