Any (easy) way to do this?

[benallen002]

I'm trying to figure out a way that would allow users to create a custom WHERE clause for a form that is bound to a table that has alot of fields. The table contains alot of records as well, but the user usually only needs to work with one subset of records at a time. I could put ALL of the fields on a "filter by" form and run it before the real form, but most of the time the user will only need to filter on one or two fields, and it seems like a waste of both load time and code running time to check all, say, 30 controls for values. The other problem is that the form I want to filter on contains not only textbox controls, but also combobox controls, which of course are linked to ID fields that the user doesn't know.

Anyways, I've gotten so far as to mark all controls whose ControlSource I want to be able to filtered with a tag, and I've got the code that gathers all those controls up into an array, I just can't figure out what to do next.

Any thoughts?

 

[Andrew Fergus]

Hi

When using standard Access forms, there is the option of using the built-in sorting and filtering functions. If you right click one of the fields there should be 4 filter options and a couple of sort options. Would that be easier?

Andrew

 

[benallen002]

yeah, but that is already after the form has loaded and in my case pulled a few thousand records from the underlying table that aren't needed. I'm looking for a way to let the user create an ad-hoc query, basically, that will serve as the recordset of the form, and not the table itself.

 

[Andrew Fergus]

Even with a few thousand records, have you tried using the standard form filters? Access can handle much higher volumes of records...

If not then an intermediate form should do the trick but I am curious as to why you would apply a single filter across multiple fields. Do the fields contain similar data values?

Andrew

 

[SydneyGeek]

I'm guessing that you only want to open the form with the filter applied, rather than opening first and then filtering. Is that correct? Also, you can read "easy" two ways in this context: "easy to implement", or "easy to use".

Andrew's answer covers "easy to implement". If you want "easy to use" you will need to (1) Create a search form for the users to make their selections, (2) Based on those selections, and using code in the search form, build the filter on the fly using VBA, and (3) Use that filter when you launch the data form.

If that's what you are after, let me know and I'll see if I can dig up an example.

Denis

 

[benallen002]

yes SydneyGeek, I'm trying to create the search form for them to use. And I actually found a good enough example hidden in a book I already have. Thanks for the responses...apologies for not closing out the thread.