Running Queries from VB Code

[Guest]

I've got a query that has 2 parameter fields. I think parameter field is the correct term for it. Basically, the query prompts the user for the month and year of the data to be summed.

I've got a report that uses the resultant query as its datasource.

I know it's possible to run the code through VB by using the

DoCmd.OpenQuery "queryname"

but I would like to use Input boxes in VB to obtain the two parameter fields - Month and Year - and then pass them to the query instead of having the user do it again.

Is this possibe?

Thanks

 

[Hi Jeff]

Yes, you can easily do what you want to do. Following is
the general syntax that you can use. The sample assumes
that you want to use a saved query, which is what I
gathered from your post. I have not included the dim
statements (other than the two), variable assignments,
error handler, etc, just the part dealing with the qdef.


Dim qdef As DAO.QueryDef
Dim dbs As DAO.Database
'Add the rest of your variable Dim's
'Add Code for Error Handler and User Input assignment to
'variables used below
Set qdef = dbs.QueryDefs("YourSavedQueryName")
With qdef
.Parameters("YourParameterName1") = InputVariable1
.Parameters("YourParameterName2") = InputVariable2
.Execute
End With
ExitLine:
Set qdef = Nothing
Set db = Nothing
Exit Sub
'Add ErrorHandler (msg error, resume next or resume
'ExitLine, etc.

Of course you will need to have a reference to DAO to use
this and you will need to substitute your actual query
name, parameter names, and variable names. Post back if
you have any questions or if you have problems.

HTH, Ted Allen

-----Original Message-----
I've got a query that has 2 parameter fields. I think

parameter field is the correct term for it. Basically,
the query prompts the user for the month and year of the
data to be summed.

I've got a report that uses the resultant query as its datasource.

I know it's possible to run the code through VB by using the

DoCmd.OpenQuery "queryname"

but I would like to use Input boxes in VB to obtain the

two parameter fields - Month and Year - and then pass
them to the query instead of having the user do it again.

 

[Dale Fye]

Jeff,

The preferred method is to use a form to capture the month
and year values, then run your query in the Click event of
a command button.

With that method, you can replace the inputbox type
parameter queries with the form, can test to make sure the
entries are valid (months between 1 and 12, years between
certain range) before running the query.

HTH
Dale

-----Original Message-----
I've got a query that has 2 parameter fields. I think

parameter field is the correct term for it. Basically,
the query prompts the user for the month and year of the
data to be summed.

I've got a report that uses the resultant query as its datasource.

I know it's possible to run the code through VB by using the

DoCmd.OpenQuery "queryname"

but I would like to use Input boxes in VB to obtain the

two parameter fields - Month and Year - and then pass them
to the query instead of having the user do it again.