How do I program Select Query Criteria into VBA?

[Guest]

Hey all, I have a project that I am working on, and it would be
extraordinarily convienient to program criteria of a select query on the fly
in VBA. So while my sub is running and I need a criteria based on a region I
the code will tell that query a specific criteria of region. Is this possible?

 

[Duane Hookom]

What do you plan on doing with the query? You can modify the sql property of
a saved query with DAO code like:

CurrentDb.QueryDefs("qselYourQuery").SQL = "SELECT... FROM.. WHERE....."

 

[John Vinson]

Hey all, I have a project that I am working on, and it would be
extraordinarily convienient to program criteria of a select query on the fly
in VBA. So while my sub is running and I need a criteria based on a region I
the code will tell that query a specific criteria of region. Is this possible?

Sure. Several ways to do this.

Could you explain where the code should get the criterion?

Either concatenate the region into the SQL string of the query, or use
a Parameter Query with code like:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim prm As Parameter
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("YourQueryName")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name) ' or set parameters manually
Next prm
Set rs = qd.OpenRecordset


John W. Vinson[MVP]

 

[Guest]

Unfortuneatly my access doesnt seem to recognize most of those functions =(

Isnt there a simpler way to just to a docmd.open query
and then add a line that will edit the criteria of a certain field?
like a setvalue ="Blah"
I don't now how to select that object though

 

[John Vinson]

Unfortuneatly my access doesnt seem to recognize most of those functions =(

In the VBA editor select Tools... References. Scroll down to the line
saying

Microsoft DAO x.xx Object Library

and check the highest version.

Isnt there a simpler way to just to a docmd.open query
and then add a line that will edit the criteria of a certain field?
like a setvalue ="Blah"
I don't now how to select that object though

Nope.

John W. Vinson[MVP]