Filter on subform not being reset using Filter = "" command

[Miles R]

Using Access 2007.
Have a subform with a filter set in the form - "British = TRUE"

In the VBA code I change the filter to other things.
e.g. Filter = "British = FALSE"
FilterOn = True

This works ok. However if I reset the filter using
Filter = ""
FilterOn = True
the filter value is not changed. I have verified this using MsgBox Filter before and after the Filter = "" statement.

Even more strange, if I use
FilterOn = False
Filter = "", then it is reset to blank.

Can't see the logic here or what is preventing Filter = "" being applied if FilterOn = True



Maybe there is a bug in this version of Access.

Can anyone offer enlightenment on this - I have trawled the internet for clues, but none were forthcoming.

[Minty]

To be honest I always reset a filter by simply setting it to "" And setting it to Me.FilterOn = False.

Perhaps the logic is that you can't change the filter value without it being off (FilterOn = False)?
The idea maybe being that by forcing a change of Filter state the records are then re-queried?

[Miles R]

Minty,

Yes you may be correct. I have done some more experiments and although I can change the Filter to something else if FilterOn = True,
I can't reset it to blank if FilterOn = True. Perhaps it does force a requery and so it does not make sense to have Filter = "" and FilterOn = True.
Surprised it does not throw up an error though trying to set Filter to "" when FilterOn = True.

Thanks.

[Micron]

Does your underlying query or table have that filter applied? When an object is created, it inherits certain properties from the object it's based on, filter being one of them. So perhaps in the absence of a valid filter string it defaults to whatever is in the underlying data source. It's my habit to set filter on property to False as is recommended, not set it to an empty string and turn it on.

[Miles R]

On the subform, it is simply using the query in the recordset, with a filter applied. There is a default one set in the form design.
I have noticed though that doing Filter = "" when FilterOn = True, does not revert the Filter to the default one, it leaves it to whatever it was set to previously.
I do have some SQL on a Popup form that I construct using the Filter from the Subform. This is why it is important that if the filter is cleared (using FilterOn = False), I also set Filter = "", so both the subform and the Popup are selecting the same data.

The issue has been resolved. Just really wanted to understand what was going on.
So, it is important to apply the two statements in VBA in the correct order.
ie. It has to be FilterOn = False followed by Filter = "". Filter = "" followed by FilterOn = False, does not work.

Thanks for the comments.

[apr pillai]

Before executing the FilterOn = True there should be a valid Filter condition set in the Filter Property like the "British=TRUE". FilterOn = False will remove the Filter and return the recordset to it's original state releasing the filter effect, but will not clear the filter condition from the Filter Property.

Later on if the FilterOn=True is executed again the data will be filtered with the earlier Filter condition.

Filter="" is not a valid Filter condition because it doesn't contain a valid Field Name or what data to look for to filter either and ignores the FilterOn=True when applied.

[Miles R]

Thanks for the reply.
Yes that seems to be correct.

[Minty]

Having seen that, I seem to remember somewhere that I saw someone use a filter of = "1=1" , which obviously is a valid filter but doesn't filter anything.
It means if it's inadvertently saved with the form and filtering is turned on the form will still work.

[Micron]

The only part I question is

Filter="" is not a valid Filter condition

because what "condition" means in this context is not clear. When you set a filter property you're creating a string that is like a WHERE clause without the "where" part. If you set the filter property value to an empty string there is no error so it's not invalid from that standpoint. Also, if you set it to "" then apply .FilterOn = True the effect is as if there was no filter, and again, there is no error. IMO, people typically set the filter to "" as a safeguard against later applying the wrong filter or subsequently and unintentionally applying any kind of filtering in code. In other words, if you "turned off" a valid filter and later turned filtering on but forgot to specify a new filter, the old one is applied, which may not be noticed right away. Setting it to "" beforehand would result in this new application doing nothing. I don't know if that would be a no brainer as far as realizing nothing changed or not. If you were paying attention and had record counters on the form, it should be obvious.

[apr pillai]

Since we enable or disable Filter through Code it is very unlikely that we write the FilterOn = TRUE statement alone.