执行依赖于表单中的参数值的已保存 Access 查询时出错

Question

我试图简单地打开一个查询并从中SELECT所有内容。查询需要FROM和TO日期。我有一个frmA，通过它我将参数传递给查询。我打开frmA并输入 2 个日期。有趣的是，当我这样做时docmd.openQuery "qryInsurance"它会打开它而不会出现问题，但是，当我尝试Select * from qryInsurance时，它告诉我它需要 2 个参数。

这是代码：

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 1

似乎 qryInsurance 包含对表单控件的引用，也许是这样的...... Forms!frmA!From

使用 DoCmd.OpenQuery 时解析这些引用，但使用 DAO.Database.OpenRecordset 方法时不会解析。 使用 OpenRecordset 时，它们被解释为尚未提供值的参数。

如果您打开查询的QueryDef，然后将每个参数Name提供给Eval()，它将为您提供这些表单控件的值......因此，您可以提供它们作为参数的值。

该描述可能不容易遵循，但代码非常简单。

在Set db = CurrentDb...

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

然后后来...

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 ...