Remove Filter

[achiola]

I am working on a form that I set up with several filter capabilities. When I click on the Remove Filter button that I set up, Access removes all the filters. How can I rewrite the following code to make it remove one filter at a time?

Public Sub RemoveFilter()

Set frm = Screen.ActiveForm

With frm
.FilterOn = False
.Filter = ""
.Requery
End With

Set frm = Nothing
End Sub

 

[RickSpr]

It's not clear what you mean by "remove one filter at a time". An Access form only has one Filter property, and thus only one filter can be associated with it at a given time. Access can not remove a filter, it can only remove the filter.

Are you perhaps giving your users buttons or other means to apply successively more restrictive filter criteria? If so, you need to track the sequence of Filter property changes as they occur, and let your RemoveFilter procedure reapply the preceding filter each time it is called. When you get back to the first filter setting, then you can do the code shown above to remove the last filter.

I recommend you create a stack using an array to hold the values of the Filter property settings. You can either make it a dynamic array and use ReDim Preserve to resize it as needed, or you can make it a fixed array with plenty of elements and hope the user doesn't go wild applying more and more layers until the stack overflows.

Each time your user adds another layer of filtering, push the new value of the Filter property onto the stack. When the user clicks the Remove Filter button, pop the top entry off and use the new top entry to set the Filter property. If the stack becomes empty when you pop the entry off, set Filter to "" and FilterOn to False, as above.

BTW, I don't believe you need the .Requery method call. In my experience, changing the Filter property automatically requeries the form. Rick Sprague