VBA OpenRecordset 生成参数太少。预期 2. 错误

VBA OpenRecordset Producing Too few parameters. Expected 2. Error

提问by rryanp

I have a query called qryAlloc_Source that has two paramaters under one criteria:

我有一个名为 qryAlloc_Source 的查询，它在一个条件下有两个参数：

>=[forms]![frmReportingMain]![txtAllocStart] And <=[forms]![frmReportingMain]![txtAllocEnd])

A have a separate query that ultimately references qryAlloc_Source (there are a couple queries in between), and that query runs fine when I double click it in the UI, but if I try to open it in VBA, I get an error. My code is:

A 有一个单独的查询，最终引用 qryAlloc_Source（中间有几个查询），当我在 UI 中双击它时，该查询运行良好，但是如果我尝试在 VBA 中打开它，则会出现错误。我的代码是：

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("qryAlloc_Debits")

I am getting run-time error 3061, Too few parameters. Expected 2. I've read that I may need to build out the SQL in VBA using the form parameters, but it would be pretty complex SQL given that there are a few queries in the chain.

我收到运行时错误 3061，参数太少。预期 2。我读过我可能需要使用表单参数在 VBA 中构建 SQL，但鉴于链中存在一些查询，这将是非常复杂的 SQL。

Any suggestions as to a workaround? I considered using VBA to create a table from the query and then just referencing that table--I hate to make extra steps though.

关于解决方法的任何建议？我考虑过使用 VBA 从查询中创建一个表，然后只引用该表——不过我不想做额外的步骤。

采纳答案by Brad

The reason you get the error when you just try to open the recordset is that your form is not open and when you try to access [forms]![frmReportingMain]it's null then you try to get a property on that null reference and things blow up. The OpenRecordsetfunction has no way of poping up a dialog box to prompt for user inputs like the UI does if it gets this error.

当您尝试打开记录集时出现错误的原因是您的表单未打开并且当您尝试访问[forms]![frmReportingMain]它时它为空然后您尝试在该空引用上获取属性并且事情会爆炸。OpenRecordset如果出现此错误，该函数无法像 UI 那样弹出对话框来提示用户输入。

You can change your query to use parameters that are not bound to a form

您可以更改查询以使用未绑定到表单的参数

yourTableAllocStart >= pAllocStart
and yourTableAllocEnd <= pAllocEnd

Then you can use this function to get the recordset of that query.

然后您可以使用此函数来获取该查询的记录集。

Function GetQryAllocDebits(pAllocStart As String, pAllocEnd As String) As DAO.Recordset

    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Set db = CurrentDb
    Set qdef = db.QueryDefs("qryAlloc_Debits")
    qdef.Parameters.Refresh
    qdef.Parameters("pAllocStart").Value = pAllocStart
    qdef.Parameters("pAllocEnd").Value = pAllocEnd
    Set GetQryAllocDebits = qdef.OpenRecordset

End Function

The disadvantage to this is that when you call this now on a form that is bound to it it doesn't dynamically 'fill in the blanks' for you.

这样做的缺点是，当您现在在绑定到它的表单上调用它时，它不会动态地为您“填空”。

In that case you can bind forms qryAlloc_debtsand have no where clauseon the saved query, then use the forms Filterto make your where clause. In that instance you can use your where clause exactly how you have it written.

在这种情况下，您可以绑定表单qryAlloc_debts并且在保存的查询上没有 where 子句，然后使用表单Filter来创建您的 where 子句。在那种情况下，您可以完全按照您编写的方式使用您的 where 子句。

Then if you want to still open a recordset you can do it like this

然后如果你还想打开一个记录集，你可以这样做

Function GetQryAllocDebits(pAllocStart As String, pAllocEnd As String) As DAO.Recordset

    Dim qdef As DAO.QueryDef
    Set qdef = New DAO.QueryDef
    qdef.SQL = "Select * from qryAlloc_Debits where AllocStart >= pAllocStart and pAllocEnd <= pAllocEnd"
    qdef.Parameters.Refresh
    qdef.Parameters("pAllocStart").Value = pAllocStart
    qdef.Parameters("pAllocEnd").Value = pAllocEnd
    Set GetQryAllocDebits = qdef.OpenRecordset

End Function

回答by VBlades

While a [Forms]!... reference does default to a form reference when a QueryDef is run from the GUI, it is actually just another Parameter in the query in VBA. The upshot is you don't have to recode your query/create a new one at all. Also, as @Brad mentioned, whether a parameter is in the final query of a chain of queries or not, you are able to refer to the parameter as if it is in the collection of the final query. That being the case, you should be able to use code similar to this:

虽然 [Forms]!... 引用在从 GUI 运行 QueryDef 时默认为表单引用，但它实际上只是 VBA 中查询中的另一个参数。结果是您根本不必重新编码查询/创建新查询。此外，正如@Brad 所提到的，无论参数是否在查询链的最终查询中，您都可以引用该参数，就像它在最终查询的集合中一样。在这种情况下，您应该能够使用与此类似的代码：

Sub GetQryAllocDebits(dteAllocStart As Date, dteAllocEnd as Date)

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryAlloc_Debit")

    If CurrentProject.AllForms("frmReportingMain").IsLoaded Then
        qdf.Parameters("[forms]![frmReportingMain]![txtAllocStart]") = [forms]![frmReportingMain]![txtAllocStart]
        qdf.Parameters("[forms]![frmReportingMain]![txtAllocEnd]") = [forms]![frmReportingMain]![txtAllocEnd]
    Else
        qdf.Parameters("[forms]![frmReportingMain]![txtAllocStart]") = CStr(dteAllocStart)
        qdf.Parameters("[forms]![frmReportingMain]![txtAllocEnd]") = CStr(dteAllocEnd)
    End If

    Set rst = qdf.OpenRecordset

    Do Until rst.EOF
        '...do stuff here.
    Loop

    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Function

If the referenced form is open, the code is smart enough to use the referenced controls on the form. If not, it will use the dates supplied to the subroutine as parameters. A gotcha here is that the parameters did not like when I set them as date types (#xx/xx/xx#), even if the field were dates. It only seemed to work properly if I set the params as strings. It didn't seem to be an issue when pulling the values straight out of the controls on the forms, though.

如果引用的窗体已打开，则代码足够智能，可以在窗体上使用引用的控件。如果不是，它将使用提供给子例程的日期作为参数。这里的一个问题是，当我将它们设置为日期类型 (#xx/xx/xx#) 时，参数不喜欢，即使该字段是日期。如果我将参数设置为字符串，它似乎只能正常工作。不过，当直接从表单上的控件中提取值时，这似乎不是问题。

回答by Darren Bartrup-Cook

I know it's been a while since this was posted, but I'd like to throw in my tuppence worth as I'm always searching this problem:

我知道这篇文章发布已经有一段时间了，但我想投入我的 tuppence 价值，因为我一直在寻找这个问题：

A stored query can be resolved:

可以解析存储的查询：

Set db = CurrentDb
Set qdf = db.QueryDefs(sQueryName)
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

For SQL:

对于 SQL：

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", "SELECT * FROM MyTable " & _
    "WHERE ID = " & Me.lstID & _
    " AND dWeekCommencing = " & CDbl(Me.frm_SomeForm.Controls("txtWkCommencing")) & _
    " AND DB_Status = 'Used'")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

This assumes that all parameter values are accessible - i.e. forms are open and controls have values.

这假设所有参数值都是可访问的 - 即表单是打开的并且控件具有值。

回答by user6337445

'I have two parameters in my recordset and I was getting the "Too few parameters. Expected 2" 'error when using an OpenRecordset in MS Access vba, and this is how I got around it and IT WORKS! see the below sub routine:

'我的记录集中有两个参数，在 MS Access vba 中使用 OpenRecordset 时，我收到了“参数太少。预期为 2”错误，这就是我绕过它的方法，它可以正常工作！见下面的子程序：

'Private Sub DisplayID_Click()

'私有子 DisplayID_Click()

'1. I created variables for my two parameter fields xEventID and xExID as seen below:

'1。我为我的两个参数字段 xEventID 和 xExID 创建了变量，如下所示：

Dim db As Database
Dim rst As Recordset
Dim xEventID As Integer 
Dim xExId As Integer  

'2. Sets the variables to the parameter fields as seen below:

'2。将变量设置为参数字段，如下所示：

Set db = CurrentDb
xEventID = Forms!frmExhibitorEntry!txtEventID
xExId = Forms!frmExhibitorEntry!subExhibitors!ExID

'3. Set the rst to OpenRecordSet and assign the Set the variables to the WHERE clause. Be sure to include all quotations, ampersand, and spaces exactly the way it is displayed. Otherwise the code will break!exactly as it is seen below:

'3。将 rst 设置为 OpenRecordSet 并将设置变量分配给 WHERE 子句。请务必按照显示方式包含所有引号、与号和空格。否则代码会崩溃！正如下面看到的那样：

Set rst = db.OpenRecordset("SELECT tblInfo_Exhibitor.EventID,tblInfo_Display.ExID, tblMstr_DisplayItems.Display " _
& "FROM tblInfo_Exhibitor INNER JOIN (tblMstr_DisplayItems INNER JOIN tblInfo_Display ON tblMstr_DisplayItems.DisplayID = tblInfo_Display.DisplayID) ON tblInfo_Exhibitor.ExID = tblInfo_Display.ExID " _
& "WHERE (((tblInfo_Exhibitor.EventID) =" & xEventID & " ) and ((tblInfo_Exhibitor.ExID) =" & xExId & " ));")

rst.Close
Set rst = Nothing
db.Close

'End Sub

'结束子