VBA OpenRecordset生产参数太少.预期2.错误 [英] VBA OpenRecordset Producing Too few parameters. Expected 2. Error

问题描述

我有一个名为qryAlloc_Source的查询，该查询在一个条件下具有两个参数:

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

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

A有一个单独的查询，该查询最终引用qryAlloc_Source(之间有几个查询)，当我在UI中双击该查询时，该查询运行良好，但是如果尝试在VBA中打开它，则会收到错误消息.我的代码是:

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:

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

我遇到运行时错误3061，参数太少.预期2.我已经读到我可能需要使用form参数在VBA中构建SQL，但是鉴于链中有一些查询，这将是相当复杂的SQL.

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.

有关解决方法的任何建议吗?我考虑过使用VBA从查询中创建一个表，然后只引用该表，但是我不想做额外的步骤.

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.

推荐答案

当您尝试打开记录集时收到错误的原因是您的表单未打开，而当您尝试访问[forms]![frmReportingMain]时它为null您尝试在该空引用上获取一个属性，然后事情就崩溃了. OpenRecordset函数无法弹出对话框来提示用户输入，就像UI遇到此错误一样.

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 OpenRecordset function has no way of poping up a dialog box to prompt for user inputs like the UI does if it gets this error.

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

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.

在这种情况下，您可以绑定表格qryAlloc_debts并在保存的查询中包含 no where子句，然后使用表格Filter制作where子句.在这种情况下，您可以准确地使用where子句编写它的方式.

In that case you can bind forms qryAlloc_debts and have no where clause on the saved query, then use the forms Filter to make your where clause. In that instance you can use your where clause exactly how you have it written.

然后，如果您仍要打开记录集，则可以这样做

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

这篇关于VBA OpenRecordset生产参数太少.预期2.错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！