Query using form fields as criteria (1 Viewer)

[Drand]

Hi

I hope I explain this properly!

I have a query that includes three fields, "Group", "SubGroup" and "StockItem" where the items in subgroup are related to groups and stockitems are related to subgroups.

I have a form where the user selects each of these from separate comboboxes.

At present, to run the query I have referenced each of these to the form combobox fields in the query criteria.

That is fine if all I want to see is the data for the stockitem field, as all three criteria are applied, and works really well.

What I would like to achieve is essentially a drill down of the data in stages.

If the user selects the group combo then the data shows all items for the entire group. If they then select a subgroup under that group then only the data for the subgroup is displayed and finally if they select the stockitem then only that data is displayed.

As I cannot edit the criteria in a query programmatically, I am not sure how to achieve this.

Would really appreciate any assistance on this.

Thanks

 

[Uncle Gizmo]

I have a "Search Form" that I thought might be of interest to you.

It's on my website here:-

Nifty Search Form - Nifty Access

Nifty Search Form This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the "Go To" person in your...

www.niftyaccess.com

 

[plog]

I wouldn't make the query what you view. Instead I would either make a form or a report to view the data. That form/report would be based on your current query but with all the criteria removed. Then you would open the form/report with the appropriate VBA:

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

One of the arguments of those is filter criteria. You would use your existing form and its input to build a filter string and then open the form/report to just the data you want.

 

[MajP]

Lately I have been incorporating this concept in a lot of forms. Easy and super flexible.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk