Using VBA to change the criteria in a query?

[nodoby]

How do I use Access VB to change the criteria in an existing query?
I am querying a customer table to display only records for a specific
CustomerCode. In the criteria area I would normally type in the CustomerCode
number and run the query, or "Like [Enter Customer Code]" would ask me when
the query is run. Now I want to run the query from Access VB which is not a
problem, but I want the code to insert the criteria and then run the query.

Any suggestions.

 

[John Vinson]

How do I use Access VB to change the criteria in an existing query?
I am querying a customer table to display only records for a specific
CustomerCode. In the criteria area I would normally type in the CustomerCode
number and run the query, or "Like [Enter Customer Code]" would ask me when
the query is run. Now I want to run the query from Access VB which is not a
problem, but I want the code to insert the criteria and then run the query.

Any suggestions.

Use the Querydef's Execute method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.QueryDefs("qryCustomerCode")

<do something to get a search criterion into a variable
lngCustomerCode>
qd.Parameters(0) = lngCustomerCode
<(1), (2), (3) for additional parameters>
qd.Execute dbFailOnError

John W. Vinson[MVP]

 

[nodoby]

Hmmm!
A little over my head, but I'll have to read up on QueryDefs and their
parameters.

It looks like you can create queries and edit them from code. This going to
be some trial and error.

Thanks for your help, this gives me the opportunity to learn something.

How do I use Access VB to change the criteria in an existing query?
I am querying a customer table to display only records for a specific
CustomerCode. In the criteria area I would normally type in the
CustomerCode
number and run the query, or "Like [Enter Customer Code]" would ask me
when
the query is run. Now I want to run the query from Access VB which is not
a
problem, but I want the code to insert the criteria and then run the
query.

Any suggestions.

Use the Querydef's Execute method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.QueryDefs("qryCustomerCode")

<do something to get a search criterion into a variable
lngCustomerCode>
qd.Parameters(0) = lngCustomerCode
<(1), (2), (3) for additional parameters>
qd.Execute dbFailOnError

John W. Vinson[MVP]

 

[nodoby]

Thanks
I got it to work!
I used db.QueryDefs(0).SQL = strSQL

This is very powerful!

Thank-you again.

Hmmm!
A little over my head, but I'll have to read up on QueryDefs and their
parameters.

It looks like you can create queries and edit them from code. This going
to be some trial and error.

Thanks for your help, this gives me the opportunity to learn something.

How do I use Access VB to change the criteria in an existing query?
I am querying a customer table to display only records for a specific
CustomerCode. In the criteria area I would normally type in the
CustomerCode
number and run the query, or "Like [Enter Customer Code]" would ask me
when
the query is run. Now I want to run the query from Access VB which is not
a
problem, but I want the code to insert the criteria and then run the
query.

Any suggestions.

Use the Querydef's Execute method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.QueryDefs("qryCustomerCode")

<do something to get a search criterion into a variable
lngCustomerCode>
qd.Parameters(0) = lngCustomerCode
<(1), (2), (3) for additional parameters>
qd.Execute dbFailOnError

John W. Vinson[MVP]