Using VBA to change the criteria in a query?

  • Thread starter Thread starter nodoby
  • Start date Start date
N

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.
 
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.
Click to expand...

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]
 
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.


John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
Thanks
I got it to work!
I used db.QueryDefs(0).SQL = strSQL

This is very powerful!

Thank-you again.

nodoby said:
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.


John Vinson said:
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.
Click to expand...

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]
Click to expand...
Click to expand...
 
Back
Top