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.

Tiphane

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

Antepast

I need to reread this to fully digest it, but I did want to note that my form is open, and a date range is entered. I actually initiate the code with a button on that same form. That's what's really confusing me--I can open the query manually but not with code. Thanks a lot for the input though--I'll review it again now. – Tiphane

So my main problem is that these parameters aren't part of the query I need (qryAlloc_Debits)...they are part of a source data query that filters the raw data by date. The query I need is a few queries down a chain that does a lot of grouping and consolidating. So unfortunately I can't apply the parameters directly to this query. – Tiphane

well the query def option still applies. The parameters will work their way up the chain. You can even populate this recordset like this on form load and set it to the recordset of the form. – Antepast

Or, once you get your big set of queries working, consolidate it back into one query. – Antepast

Ah, I get it now--I was missing that I could use the qdef parameters even though those parameters weren't specifically in that query. Thanks! – Tiphane

Answer 2

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:

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.

Remy

Thanks @VBlades--this really helped a lot! I gave the right answer to Brad based just on timing, but seeing both answers really helped clarify it. I appreciate it. – Tiphane

Answer 3

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:

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:

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.

Vinnievinnitsa

Answer 4

'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:

'Private Sub DisplayID_Click()

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

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:

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:

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

Strapped