Set query criteria when opening the query's form

[cmray58]

I have a query that I'm displaying (in datasheet view) in a form. I want to set the criteria of the query based on input from the user. Basically, the user will select their program and the form will open to the query filtered to only entries for that program.

I found the below article, but their query isn't in a form. Any help to implement this?

https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s02.html

 

[theDBguy]

Hi. It shouldn’t matter if the query is in a form or not. As long as the parameters are available to the query, it should work. Give it a try and let us know how it goes.

 

[cmray58]

Hi. It shouldn’t matter if the query is in a form or not. As long as the parameters are available to the query, it should work. Give it a try and let us know how it goes.

Started working on this, but am now realizing my case is a little more complicated. I have several fields in play. To give some context, I want to make a single form for all departments to use. When the user opens the first form, it asks them which department they're with. When they select the department and click OK, the form with the query opens, filtered to clients in their department.

The complexity is that clients could be in multiple departments. There's a field for each department on the client's record, indicating whether the client belongs to the department.

So, when the department selects their department from the dropdown, the criteria going into the query isn't their department name, but actually a "YES" criteria on the client's field for that department.

 

[June7]

I don't use dynamic parameterized queries.

I would use VBA to construct the filter criteria and pass the criteria to form (or report) when opened. http://allenbrowne.com/ser-62.html

 

[theDBguy]

There's a field for each department on the client's record, indicating whether the client belongs to the department.

Sounds like you may have a non-normalized table structure. If it's not too late, you might consider normalizing your table structure to reduce the complexity in designing your forms.

 

[June7]

Normalizing will likely involve a new table and may be more advantageous in long run. This might make dynamic parameter queries simpler but data entry design actually more complex - form/subform arrangement. What you have can be dealt with, will probably have to learn some new techniques to do so.