• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2212
  • Last Modified:

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.

private sub cmdRunReport_Click()
   Dim startDate, endDate as Date
   '...Calculate values for startDate and endDate...
   DoCmd.OpenQuery myQuery   ' Query needs parameters...
end sub
1 Solution
Try This:

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


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... ;)

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now