Passing parameters to a query

[RobatSara]

Greetings from Holland,

I am relatively new to access and I am banging my head against the wall with this problem I do not understand.

I have saved a query which I use in a sub form to initially extract ALL data from a table. On the main form I have a list box which I want to use to filter the query with (So called WHERE clause).

What I want to do is to pass the value in the list box as a filter value / criteria to extract A PORTION of the data in the table and then run it. I should then see the result set from that (filtered) query in the sub form.

I have tried altering the SQL instruction via VBA. The SQL instruction of the query does get altered but when run the requery method of the sub form nothing happens. Only when the main form is actually reloaded I see the result of the VBA code.

The main problem I would like to fix once and for all is that I cannot understand why it is not possible to pass selection criteria or parameters (where clause) from user forms controls to a stored query and than run it.

Can anybody explain why this can not be done?

Thanks!

 

[Cbrine]

You can do this.
Build your user form with your controls. The form cannot be associated to the query for this to work. Build a button that will run the query. In your query criteria, while the form is open, right click and select build. Using the open forms option double click until you get to your fields. Then just double click on the field name and press OK. Your should now bo able to use form controls as parameters for your queries. Hope this helps.

 

[RobatSara]

Hello again,

I was on vacation for a while so I got to try your solution yesterday and it work!

Thanks for your help!