Filtering on records from WHERE condition

Wyoming
StarLounger
Posts: 76
Joined: 04 Jan 2013, 12:07

Filtering on records from WHERE condition

Post by Wyoming »

Hello,

I have a form that shows a list of records in it.
In the DoCmd.Openform that opens this form I have included a Where condition so just a subset of the total recordsource is shown.
Then, inside the form there is a textbox that allows to filter the data so that searching for records is easier.
The problem comes here, when filtering the records it executes the filter on the whole recordsource, not only on those records that where matching the Where condition.

An alternative would be to include that Where condition in the recordsource itself, which is a query, but the condition is based on the value of a variable, that's why I am including it in the DoCmd.OpenForm instead.

So, is there any way to run the filter only on those records included in the where condition, this is, the records that were initially shown in the form?

Thanks!

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Filtering on records from WHERE condition

Post by Rudi »

Just a guess...
If the text box runs an event to filter the form records based on its value, then simply include the criteria of the where clause in the event of the text box to.
This is all presumption...
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

Wyoming
StarLounger
Posts: 76
Joined: 04 Jan 2013, 12:07

Re: Filtering on records from WHERE condition

Post by Wyoming »

Thanks a lot, Rudi.

Yes, doing it the way you suggest it works :)
Anyway I'm still curious about this question, I'd like to know if there is a way to approach this issue without using this "workaround".

In any case the issue is solved, thanks for your help!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filtering on records from WHERE condition

Post by HansV »

Instead of setting a WhereCondition in DoCmd.OpenForm, you could set the RecordSource property of the form to a dynamically generated SQL statement in the On Open event of the form. In order to do this, you can pass the value of the variable in the OpenArgs argument of DoCmd.OpenForm. It will become the OpenArgs property of the form that you open.
Best wishes,
Hans

Wyoming
StarLounger
Posts: 76
Joined: 04 Jan 2013, 12:07

Re: Filtering on records from WHERE condition

Post by Wyoming »

This sounds great. It looks like this is the most straightforward way of doing it.
Thanks once more.