SamSam7 SamSam7 - 4 months ago 7
SQL Question

Error executing a saved Access query that depends on parameter values from a form

I'm trying to simply open a query and

SELECT
everything from it. The query requires
FROM
and
TO
dates. I have a
frmA
through which I pass the parameters to the query. I open the
frmA
and put in 2 dates. Funny thing is, when I do
docmd.openQuery "qryInsurance"
it opens it wihout a problem, however, when I try to
Select * from qryInsurance
, it tells me that it's expecting 2 parameters.

here is the code:

Public Function CountFollowup() As Boolean
On Error GoTo error_handler:
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strsql As String
Dim rcount As Integer
Set DB = CurrentDb
CountFollowup = False

DoCmd.OpenQuery "qryInsurance"

strsql = "SELECT * FROM [qryInsurance];"

'CurrentDb.Execute "Delete * FROM temp_InsData"
Set rs1 = CurrentDb.OpenRecordset(strsql, , dbOpenSnapshot) ' here is where it gives me an ERROR, expecting 2 parameters when it OPENS it fine before strsql
Set rs2 = CurrentDb.OpenRecordset("temp_InsData")

Debug.Print strsql


Exit Function
error_handler:
MsgBox Err.Number & " - " & Err.Description
End Function

Answer

It seems qryInsurance includes references to form controls, maybe like this ... Forms!frmA!From

Those references are resolved when you use DoCmd.OpenQuery, but not when you use the DAO.Database.OpenRecordset method. With OpenRecordset, they are interpreted as parameters for which you have not supplied values.

If you open the query's QueryDef and then feed each parameter Name to Eval(), it will give you the values of those form controls ... so you can then supply them as the values for the parameters.

That description may not be easy to follow, but the code is pretty easy.

Add these variable declarations before Set db = CurrentDb ...

Dim db As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim CountFollowup ' As what? Boolean?

Then later ...

Set db = CurrentDb
CountFollowup = False

'DoCmd.OpenQuery "qryInsurance"
'strsql = "SELECT * FROM [qryInsurance];"
'CurrentDb.Execute "Delete * FROM temp_InsData"

Set qdf = db.QueryDefs("qryInsurance")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
'Set rs1 = CurrentDb.OpenRecordset(strsql, , dbOpenSnapshot) ' here is where it gives me an ERROR, expecting 2 parameters when it OPENS it fine before strsql
Set rs1 = qdf.OpenRecordset(dbOpenSnapshot)

' and the rest ...