• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

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

0
Blim2
Asked:
Blim2
  • 3
  • 3
  • 2
2 Solutions
 
Jeffrey CoachmanCommented:
"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
0
 
Jeffrey CoachmanCommented:
Or brute-force:

    DoCmd.ShowAllRecords
0
 
Blim2Author Commented:
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.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Blim2Author Commented:
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?
0
 
Jeffrey CoachmanCommented:
OK,

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

;-)

JeffCoachman
0
 
mbizupCommented:
Just curious...
Do you have any error handling code in this event that you have not posted here?

I'm wondering for a couple of reasons:
- An untouched textbox's value will be Null, not an empty string
- A Null will cause this line to fail with an "invalid use of Null" error if strF is declared as a string: " strF = Me.txtFilter"
- Nulls and Empty Strings are not the same, so comparing them will never result in a match.

Try this code (and read the comments for an expllanation):

Dim strF As String
  strF = Nz(Me.txtFilter, "") '<-- This will return an Empty String if txtFilter is NULL
  Me.Filter = strF
  If Me.Filter = "" Then
    Me.FilterOn = False
  Else
    Me.FilterOn = True
  End If

Open in new window

0
 
mbizupCommented:
<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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24098570.html
0
 
Blim2Author Commented:
I've tried the Nz trick as well.
This works:

  strF = Nz(Me.txtFilter)
  Me.Filter = ""
  Me.Filter = strF
  If Me.Filter = "" Then
    Me.FilterOn = False
  Else
    Me.FilterOn = True
  End If

This doesn't:

  strF = Nz(Me.txtFilter)
'  Me.Filter = ""
  Me.Filter = strF
  If Me.Filter = "" Then
    Me.FilterOn = False
  Else
    Me.FilterOn = True
  End If

The only difference is that in the first code snippet I have commented out the line that explicitly sets the form's filter property to an empty string before setting it to the value of the textbox.  I am using Access 2003 with defult file format 2000.

Incidentally, what are the pros and cons of not setting the FilterOn property to false if the Filter property is empty?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now