asked on

Cannot clearform filter

It's probably something stupid that I have missed but I can't seem to set Me.Filter to an empty string in one of my forms. The line Me.Filter = strF always leaves the contents of Me.Filter untouched, even if strF is an empty string.

I am running the code in the click event of a show all button, which is supposed to remove any filter that the user has created using other functions on the form. The form can be opened in a filtered state which I don't want the user to be able to clear. The original filter that was active when the form was opened is stored in the textbox Me.txtFilter. In all the testing I have done so farMe.txtFilter is an empty string. I've tried setting Me.Filter directly from this (Me.Filter = Me.txtFilter) or via an intermediate string variable (strF) as in the code snippet. In both cases the result is the same - the data in Me.Filter remains unchanged.

Thanks

Dim strF As String
  strF = Me.txtFilter
  Me.Filter = strF
  If Me.Filter = "" Then
    Me.FilterOn = False
  Else
    Me.FilterOn = True
  End If

Open in new window

Jeffrey Coachman

"I am running the code in the click event of a show all button, which is supposed to remove any filter that the user has created using other functions on the form."

Then just try using this to clear the filter.
Sub cmdYourClearFilterButton_Click()
Me.Filter=""
Me.FilterOn=False
End sub

JeffCoachman

Jeffrey Coachman

Or brute-force:

DoCmd.ShowAllRecords

Blim2

ASKER

In certain circumstances I want the form to be opened with a filter that I set - the users are then permitted to add their own filters on top of this. I keep the form's original filter in a textbox called txtFilter. If I just set Me.Filter to "" I will loose the form's original filter.

Blim2

ASKER

I think that this has solved it:

Me.Filter = ""
Me.Filter = Me.txtFilter

This has the desired effect whether or not there is anything in Me.txtFilter. However, why doesn't
Me.Filter = SomeVariable set the Filter property to an empty string when SomeVariable is an empty string? I also think the two stage process above is going to lead to a flickering scrfeen as, when there's a where clause in Me.txtFilter, Access first displays all the records then the filtered subset. Are there any known bugs around Access form filters?

Jeffrey Coachman

OK,

I'm sure another expert will be along shortly to help you out.

;-)

JeffCoachman

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

<Are there any known bugs around Access form filters?>

One other thought... you haven't mentioned what version of Access you are using. I'm not sure about the "known bugs" with filtering in Access 2007, but some filter operations just do not work as expected:

https://www.experts-exchange.com/questions/24098570/2003-vs-2007-different-behavior-Opein-form.html

ASKER CERTIFIED SOLUTION

Blim2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial