VBA OpenRecordset Producing Too few parameters. Expected 2. Error

Question

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

>=[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:

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.

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.

Answer 1

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.

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