Passing VBA parameters to query

I have a query that I have been executing in VBA via the line "DoCmd.OpenQuery myQuery" in the code for a form. I've now modified the query so that it takes in two parameters but I'm stumped as to how to pass those parameters to the query. The parameters are not form controls, they are new values calculated within the form's code.

e.g.:
private sub cmdRunReport_Click()
   Dim startDate, endDate as Date
   '...Calculate values for startDate and endDate...
   DoCmd.OpenQuery myQuery   ' Query needs parameters...
end sub
alanberginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JFercanCommented:
Try This:

Dim db As DATABASE
Dim qry AS QueryDef
Dim rs As Recordset

'Use the current database
Set db = CurrentDb()

'Select your query
Set qry = db.QueryDefs("qryName")

'Specify the value of the paramter (prompt that apears in the input box when you run the
'query from the designer e.g. "Enter Value:"
qry.Parameters("Parameter Prompt") = value

//Open the query and assign the results to the recordset.
Set rs = qd.OpenRecordset

HTH

JFercan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
I'm afraid that you can't do that. You can specify parameters to a query that will run within VB like this:

    With qselMyParamQuery
        .Parameters(0).Value = #1 jan 2005#
        .Parameters("Min Value") = 123
        .Parameters![Last Name] = "Jones"
        With .OpenRecordset
            Debug.Print .RecordCount
        End With
    End With

The problem is that DoCmd.OpenQuery has a different scope. It is opened by Access, not VB. This is how a windows is created to display the records... VB doesn't know how to do that.

If you want a query opened as an Access Window, you will need to provide your parameter data either through a form (Access object) or through a function (VB solution).

Change your code to this:

<in a global module>
Global gdatStartDate As Date, gdatEndDate As Date
Function ParamStartDate() As Date : ParamStartDate = gdatStartDate : End Function
Function ParamEndDate() As Date : ParamEndDate = gdatEndDate : End Function

<in the form>
private sub cmdRunReport_Click()
    ' calculate gdatStartDate and gdatEndDate
   DoCmd.OpenQuery myQuery   ' Query uses ParamStartDate() and ParamEndDate() as parameters
end sub

If you do not want to add a global module for that:

<in the form>
Public StartDate As Date, EndDate As Date

private sub cmdRunReport_Click()
    ' calculate Me.StartDate and Me.EndDate
   DoCmd.OpenQuery myQuery   ' Query uses Forms!frmThisForm.StartDate and .EndDate as parameters
end sub

In other words, a public variable in the form's module is used exaclty like a control on the form. This replaces the typical solution of using a hidden control on the form to achieve the same result...

Hope this helps... ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.