Toggle Filter on and off

[Paul H]

I have a combo box that is used to filter the records displayed in a form. Initially all records are displayed. When the user makes a selection from the list, only the matching records are displayed. I'd like to use the same control to remove the filter and display all records, but the list is contolled by a table. I don't want to add show all to the table.

What do I do?

[June7]

One way is to UNION a " Select All" value into the RowSource:
SELECT " Select All" As Criteria FROM tablename
UNION SELECT fieldname FROM tablename;

Then in the combobox AfterUpdate event VBA code sets Filter:

If Me.Combo0 = " Select All" Then
    Me.FilterOn = False
Else
    Me.Filter = "fieldname='" & Forms!form1!Combo0 & "'"
    Me.FilterOn = True
End If