Vba – Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)

ms-accessvba

I have the following 'set recordset' line that I cannot get working. The parameters seem correct according to all available help I can find on the subject.

The error displays :

"Run-time error '3061'. Too few parameters. Expected 1."

Here is the line of code:

Set rs = dbs.OpenRecordset("SELECT Centre_X, Centre_Y FROM [qry_all_details] 
WHERE ID = " & siteID & ";", dbOpenSnapshot)

Where rs is the recordset (Dim rs As Recordset) and dbs = CurrentDb()

Any help would be appreciated.

I have tried removing the WHERE cause with no effect, and also using single quotes between double quotes, but no joy.

Many thanks.

Best Answer

"Run-time error '3061'. Too few parameters. Expected 1."

I believe this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.

Related Solutions

VBA OpenRecordset Producing Too few parameters. Expected 2. Error

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

Best Answer

Related Solutions

VBA OpenRecordset Producing Too few parameters. Expected 2. Error

Related Topic