Built-in filter feature conflict with query criteria based on form field

[Steven Kogan]

I have a form where you can choose to filter results on an unbound field with a Client ID. The form has a query for it's record source that filters on that field if it is filled in. So the where clause has "WHERE qryDetailList0100Union.ORDClientFK=IIf(IsNull([Forms]![frmDetails]![FilterClient]),[ORDClientFK],[Forms]![frmDetails]![FilterClient])". In the afterupdate event of the filter field I do a me.Requery. That works okay on it's own.

The issue is that if the user uses the built-in filter, such as by being in the bound ClientID field and clicking the Filter icon, and chooses a particular client, then the filter based on the FilterClient field stops working, even when I use the Toggle Filter button to turn off the built-in filtering.

So after using the built-in filter feature, the query criteria based on FilterClient is ignored even when doing me.requery in VBA or clicking Refresh All.

The problem occurs both in Access 2010 and 2013. It also occurs on non-Union queries.

Is this a known issue? Has anyone else experienced this?

I managed to get the query's where clause to kick in by putting me.recordsource = me.recordsource instead of me.requery in the afterupdate event.

[jforbes]

In my experience, if you are providing a Filtering mechanism for your users, you need to provide the full range of user needs in your Filtering, and then hide or turn Access' UI filtering. Often the Form Property .AllowFilters is all you need.

The two just do not get along and I've never seen anyone successfully get both to work on the same Form.

[NeoPa]

It's not impossible, but certainly more awkward.

The inbuilt filtering is reflected in the .Filter & .FilterOn properties.