- Microsoft Access
- Visual Basic Classic
- Query Syntax
Problem passing Parameters using DAO QueryDef
I am trying to pass a start and end date to a parameter query using DAO querydef. Even though I assume I have passed the parameters in the following code, I am still being prompted to enter the dates. What am I missing?
'qryRpt_ByRelease
SELECT FROM tblProjectControl " _
& "tblProjectControl.TargetDT, " _
& "tblProjectControl.SomeField2, " _
& "tblProjectControl.SomeField3 " _
...
& "WHERE tblProjectControl.targetID " _
& ">=[StartDT] And <= [EndDT];"
Private Sub cmdBuildReleaseReport_Click()
'On Error GoTo Err_cmdBuildReleaseReport_Click
Dim StartDT As Date
Dim EndDT As Date
StartDT = cboReleaseMonth.Column(2)
EndDT = 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")
qdf.Parameters("StartDT") = StartDT
qdf.Parameters("EndDT") = EndDT
Set rs = qdf.OpenRecordset()
With rs
DoCmd.OpenQuery "qryRpt_ByRelease"
.Close
End With
End Sub
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]![
This function:
Public Function mQyrParmTest() As Boolean
Dim rst As DAO.Recordset, qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryPa
With qdf
.Parameters("[Forms]![frmT
' or Parameters(0) = ([Forms]![frmTest].[txtEmp
Set rst = .OpenRecordset(dbOpenDynas
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
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]![
This function:
Public Function mQyrParmTest() As Boolean
Dim rst As DAO.Recordset, qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryPa
With qdf
.Parameters("[Forms]![frmT
' or Parameters(0) = ([Forms]![frmTest].[txtEmp
Set rst = .OpenRecordset(dbOpenDynas
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.
.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
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
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])
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"
If your goal is to simply open a Recordset, why bother with QueryDefs at all? Just open the RS:
Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset("S
"BETWEEN "#" & txtStartDt & "# AND #" & txtEndDt & "#")
Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset("S
"BETWEEN "#" & txtStartDt & "# AND #" & txtEndDt & "#")
-
![]()
received a Solution
sql VBA UPDATE too few parameters -
![]()
wrote an Article
![]()
Synchronizing subforms - What record is displayed on top of a continuous subform after using scrollbar?
-
![]()
created a Video
![]()
Create a Query and Grouped Report and Modify Design using Access
-
Explore Cloud Class® ![]()
Programming C# 6: Fundamentals
Premium Content
You need an Expert Office subscription to comment.Start Free Trial