Problem passing Parameters using DAO QueryDef

This should explain it:

Courtesy of EE's mbizup:

"References to form controls work fine in stored queries.  If you take that same query and open it through VBA, the same form reference that worked before will cause an error -- unless you evaluate that reference seperately.  This holds true for OpenRecordset statements, and also CurrentDB.Execute and Docmd.RunSQL."


ok ... here is the example.

Setup:
A form with RecordSource of tblEmp  (employees table - two fields EmpID, EmpName)
Textbox on the form bound to EmpID
A saved Query Def (qryParametersTest) in the database window with this SQL:

SELECT tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
WHERE (((tblEmp.EmpID)=[Forms]![frmTest].[txtEmpID]));

This function:

Public Function mQyrParmTest() As Boolean
   
    Dim rst As DAO.Recordset, qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("qryParametersTest")
    With qdf
        .Parameters("[Forms]![frmTest].[txtEmpID]") = ([Forms]![frmTest].[txtEmpID])
        ' or Parameters(0) = ([Forms]![frmTest].[txtEmpID])
        Set rst = .OpenRecordset(dbOpenDynaset)
        MsgBox rst![EmpName]    'this will display the Employee name related to the ID currently on the form
    End With
    Set rst = Nothing
    Set qdf = Nothing
End Function

------

mx

You may get an error is you don't explicitly refer to the .Value property of that Parameter:

.Parameters("Your Param Name").Value = SomeValue

This doesn't happen every time, but I've had it happen on more than one user machine. Not sure why, but better safe than sorry.

Hello chobe,

In order to pass in parameter values via DAO, I am pretty sure you must explicitly declare those parameters
in the query's SQL statement.

For example, instead of:

SELECT
FROM
WHERE ;

use:

PARAMETERS [StartDate] datetime, [EndDate] datetime;
SELECT
FROM
WHERE ;

Regards,

Patrick

You are opening the query twice:

   Set rs = qdf.OpenRecordset()
    With rs
        ' Skip the DoCmd.
        ' DoCmd.OpenQuery "qryRpt_ByRelease"
        '
        ' Do something (run a loop?) with your recordset.
        '
        .Close
    End With

/gustav

OK, tried the following 3 options using (hopefully correctly) the different suggestions offered - none of which I can get to work.  Options 1 and 2 still prompt for StartDT and EndDT even though ?qdf.Parameters([StartDT]) in intermediate window returns date value.  After removing criteria and Parameter definitions from stored procedure "qryRpt_ByRelease", I was finally able to address using a different approach.  I think this will work but if you see any problems please let me know.

    Option1
    Dim chuck As Variant
    Dim StartDT As Date
    Dim EndDT As Date
    txtStartDT = cboReleaseMonth.Column(2)
    txtEndDT = cboReleaseMonth.Column(3)
    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.QueryDefs("qryRpt_ByRelease")
    With qdf
        .Parameters("StartDT").Value = ([Forms]![Startup].[txtStartDT])
        .Parameters("EndDT").Value = ([Forms]![Startup].[txtEndDT])
        Set rs = .OpenRecordset(dbOpenDynaset)
    End With
    DoCmd.OpenQuery ("qryRpt_ByRelease")

'    Option2
    Dim StartDT As Date
    Dim EndDT As Date
    txtStartDT = cboReleaseMonth.Column(2)
    txtEndDT = cboReleaseMonth.Column(3)
    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.QueryDefs("qryRpt_ByRelease")
    With qdf
        .Parameters(0) = ([Forms]![Startup].[txtStartDT])
        .Parameters(1) = ([Forms]![Startup].[txtEndDT])
        Set rs = .OpenRecordset(dbOpenDynaset)
    End With
    DoCmd.OpenQuery ("qryRpt_ByRelease")

'    Option3_Click()
    Dim StartDT As Date
    Dim EndDT As Date
    txtStartDT = cboReleaseMonth.Column(2)
    txtEndDT = cboReleaseMonth.Column(3)
    StartDT = [Forms]![Startup].[txtStartDT].Value
    EndDT = [Forms]![Startup].[txtEndDT].Value
    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.CreateQueryDef("tmp_ByRelease", _
    "SELECT * FROM qryRpt_ByRelease WHERE [Install Date] " & _
    "BETWEEN "#" & [StartDT] & "# AND #" & [EndDT] & "#")
    DoCmd.OpenQuery "tmp_ByRelease"

Select allOpen in new window

You are still opening the query twice. Modify as shown:

    Set rs = qdf.OpenRecordset()
    With rs
        '
        ' Do something (run a loop?) with your recordset.
        '
        .Close
    End With

/gustav

Your suggestion is what worked best for me.  question...Sorry it took so long to respond  Thanks so much for your assistance