By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

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

Expert 100+
P: 107
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.
May 18 '17 #1

✓ answered by NeoPa

It's not impossible, but certainly more awkward.

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

Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,107
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.
May 19 '17 #2

Expert Mod 15k+
P: 31,491
It's not impossible, but certainly more awkward.

The inbuilt filtering is reflected in the .Filter & .FilterOn properties.
May 19 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.