Filtering records using combo boxes on a form

[Guest]

I am trying set up a form that can be used to filter records, showing these
filtered records in a subform on the same form. I would have a combo box on
the main form, one for each field in the records, listing the entries for
that field. I want to use these combo boxes to filter the records, with the
list of entries in the remaining combo boxes filtered to reflect the filtered
records on the subform. Also, not all of the combo boxes need to be used in
the filter, with the combo boxes not used not affecting the filtered results.
I would like the whole thing to be dynamic, with the filtered records in the
subform changing with every selection in the combo boxes.

This is very similar to the Auto Filter feature in excel, except with the
controls seperated (on the main form in this case).

I have tried using a query, with the criteria referencing a text box (e.g. "
Forms![Search]![Title] "), however this is not working. I have also tried
using a macro on the subform.

Anybody got some ideas, is this even possible? I am relatively new to
Access, some pointers in the right direction would be appreciated.

 

[Allen Browne]

Okay, you want to place a bunch of combos across the top of your form, and a
command button to apply the filter from the non-blank values.

The Click event of the command button builds up a string to use as the
form's filter. The example below illustrates how to do that for a number
field, a text field, and a date field. It is structured so that you can
easily add as many as you need, by tacking " AND " onto the end of each one
and then removing the trailing AND at the end.

Private Sub cmdFiler_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

If Not IsNull(Me.cboNum) Then 'Numeric field example.
strWhere = strWhere & "([MyNumberField] = " & Me.cboNum & ") AND "
End If

If Not IsNull(Me.cboText)) Then 'Text field example.
strWhere = strWhere & "([MyTextField] = """ & Me.cboText & """)
AND "
End If

If Not IsNull(Me.cboDate) Then 'Date field example.
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.cboDate, conDateFormat) & ") AND "
End If

'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "No criteria"
End If
End Sub