Filtering and Removing Filter based on a combo box

[Michael]

I have a combo box which I am trying to use as a records filter. It
works for finding records that match the combo box but my problem is
how to remove the filter and show all records once it has already been
filtered. Here is what I have so far:

Private Sub Form_AfterUpdate()
If Not IsNull(Me![FilterByTicketStatus]) Then
Me.Filter = "[TicketStatus]='" & Me![FilterByTicketStatus] &
"'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

When the "FilterByTicketStatus" is cleared, it shows no records and
the filter button still shows active.

How do I fix this so it shows all the records and removes the filter
when the combo box is cleared?

 

[stevez]

Have you tried:
DoCmd.ShowAllRecords
in your If then else structure?

 

[Michael]

Now a followup question. I'm wondering if I can have more than one
combo box along the top of our forms which would allow the user to
filter the form based on a combination of different filters. Is it
possible and how would I code that?

Filter Combo Boxes desired:
Ticket Status
Ticket Sub-Status
Follow Up By
Ticket Number
Client

 

[stevez]

Funny you should ask since that is what brought me to this forum in the first place, I was looking for methods to achieve the same.
The only thing that I found people doing was creating nested IF/THEN statements that built longer WHERE strings for the query. Each of the combo boxes would trigger on after_update and execute a sub that would examine all of the combo box values and build the query.
I didn't like the structure of the code (too messy for my beginner talent) and settled instead for a different approach by sorting instead.
BTW, I haven't finished my recent app so I may wind up creating the multiple filter as well. If so, I'll come back and posta copy of what I did.

 

[stevez]

better method

Micheal, I used the multiple WHERE method and created something that I found offensive until I found a much cleaner method.
I hope that I can describe it properly, if not then just inspect the attached example.
It involves work in a couple of places (rather than just code) but is much simpler.
Create a query for your form.
Create criteria for the fileds that you want to filter.
Make the criteria point to a function that examines the combobox contents.
Make the After_Update event of the combobox requery the form (or list as in my case). The trick is in the syntax of the query criteria, be sure to use "Like".
Be sure to browse the example.
Sorry, should have mentioned that this method that I found was someone else's work and I lost the link to their forum and their name, so, many thanks to the "forum world", not me.