Change the query criteria and run it from a form using VBA

[Ahmed Nada]

Hi,
I have small data base with many tables, one of them a table for equipment wit related details, as below fields.

1-EqipmentID
2-SerialNO
3-Model
4-Coustmer
6-City

I created one normal method query of ACCESS “QueryEQ” on that table , but every time I want to change any criteria I have to edit manual direct to query design .

What I need to do now is to create a form with one combo box showing all “model” and after select any mode I have to press command bottom to change the criteria of the model of the existing query “QueryEQ” and run it and show the result in a subform in same form.

Any idea how to that using VBA

 

[pr2-eugin]

You might need to create the SQL string and then use QueryDef to change the pre compiled query.. Or easier to just assign that to the Subform recordsource..

 

[JackKaptijn]

There is another solution, using a parameter in the query.
In the design of the query create a parameter (sorry, I have got a dutch version): see Screen1

In VBA you can refer to the parameter:

Set qdfExample = CurrentDb.QueryDefs("QueryEQ")
qdfExample.Parameters("parModel") = Me!ModelComboBox
Set rstBron = qdfExample.OpenRecordset