Passing VBA parameters to query

Posted on 2004-11-24
Last Modified: 2010-05-18
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
Question by:alanbergin
    LVL 2

    Accepted Solution

    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


    LVL 58

    Expert Comment

    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 Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    27 Experts available now in Live!

    Get 1:1 Help Now