remove filter

i used the "command button wizard" to build the following - it applies a filter based on the selection.  this button is on my form "ReadFrm"

i need to be able to remove the filter as well.  I  would like to be able to apply the filter with the first click and then remove the filter with the second click???????? or at least add a second button to remove the filter. (nothing in the "command button wizard" on removing the filter????

Private Sub Command97_Click()
On Error GoTo Err_Command97_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command97_Click
End Sub
Who is Participating?
Do do this with one button you would have to set up a Public variable in a module:
Public FilterApplied as Integer

In the On Open event of your form, clear the variable:
FilterApplied = false

Now in the On Click of your button:

If FilterApplied then
    Me.Filter = ""
    Me.FilterOn = True
    FilterApplied = False
    DoCmd.ApplyFilter , "FilterField= FilterValue"   '<----If FilterValue is text then you would use "FilterField = 'FilterValue'"
    FilterApplied = True
end if

This should clear your filter:

Me.Filter = ""
Me.FilterOn = True

ssblueAuthor Commented:
so do i create a new button and use your code to remove the filter?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.