Run Access parameter query from code?

Hello Experts
I want to run a function that executes a paramter query and returns the answer.
I think I'm close but I'm stuck on my poor understaning of querydefs
Here is my code. Help! Please?

Public Function PreviousRptDt(AsOfDt As Date)
Dim db As Database
Dim qdf1 As QueryDef
Dim StrSQL As String
Dim rs1 As DAO.Recordset

Set db = CurrentDb()

StrSQL = "PARAMETERS [Forms]![frmMain]![cboAsOfDate] DateTime; "
StrSQL = StrSQL & "SELECT Max(dbo_tblSPL.ReportDate) AS MaxOfReportDate "
StrSQL = StrSQL & "FROM dbo_tblSPL "
StrSQL = StrSQL & "WHERE (((dbo_tblSPL.ReportDate)<[Forms]![frmMain]![cboAsOfDate]));"

Set qdf1 = db.QueryDefs(StrSQL)
DoCmd.RunSQL StrSQL

Set rs1 = CurrentDb.OpenRecordset(StrSQL)
rs1.MoveFirst
PreviousRptDt = rs1!MaxofReportDate
rs1.Close
rhk6446Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
StrSQL = "SELECT Max(dbo_tblSPL.ReportDate) AS MaxOfReportDate "
StrSQL = StrSQL & "FROM dbo_tblSPL "
StrSQL = StrSQL & "WHERE dbo_tblSPL.ReportDate < #" & [Forms]![frmMain]![cboAsOfDate] & "#"

Set rs1 = CurrentDb.OpenRecordset(StrSQL)
0
 
StanyslawCommented:
Use QueryDef, set parameters, then execute Query (for insert/update) or Use recordset for select

Set qdf1 = db.QueryDefs(StrSQL)

qdf1.Parameters("[Forms]![frmMain]![cboAsOfDate]")=value
set rs1=qdf1.OpenRecordSet()

and work with rs1
0
 
rhk6446Author Commented:
Hi Stanyslaw,
I think you a re very close to the solution, but my code returns the error:
"Item not found in this collection" on line
Set qdf1 = db.QueryDefs(StrSQL)

any ideas?
0
 
StanyslawCommented:
Ops,
the example is for parametrized saved query - instead of strSQL there should be query name.

If query should be created from strSQL -
Set qdf1 = CreateQueryDef ("", StrSQL)

Empty first parameter causes creating for temporary query.
0
 
rhk6446Author Commented:
Public Function PreviousRptDt(AsofDt As Date) As Date
Dim db As Database
Dim qdf1 As QueryDef
Dim StrSQL As String
Dim rs1 As DAO.Recordset

Set db = CurrentDb()

StrSQL = "PARAMETERS AsOfDt DateTime; "
StrSQL = StrSQL & "SELECT Max(dbo_tblSPL.ReportDate) AS MaxOfReportDate "
StrSQL = StrSQL & "FROM dbo_tblSPL "
StrSQL = StrSQL & "WHERE (((dbo_tblSPL.ReportDate)<AsOfDt));"

Set qdf1 = db.CreateQueryDef("", StrSQL)
qdf1.Parameters("AsOfDt") = AsofDt
Set rs1 = qdf1.OpenRecordset()

If rs1.RecordCount > 0 Then
    PreviousRptDt = rs1!MaxofReportDate
End If
rs1.Close
0
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.

All Courses

From novice to tech pro — start learning today.