OpenRecordset Method Issue With Too few Parameters

Question

This seemingly simple problem has me stopped dead in my tracks for three days now.

My code:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qryAutoOrder", dbOpenDynaset)

qryAutoOrder is a select query which runs just fine by itself and has no parameters (unless criteria in the query builder count).

When the code runs it hangs on the set rs = line and throws this error.

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

There is more to the code where I would like it to run a loop for each record in the query results so that I can append data to another existing databases tables but it is currently commented out.

Answer 1

OpenRecordset does not resolve the form reference ([Forms]![completeRepair]![txtRepairID]) in the query. In that situation, it is interpreted as a parameter for which you have not supplied a value.

So give it the parameter value via Eval(prm.Name) ...

Dim rs As DAO.Recordset
Dim db As DAO.database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef

'Set rs = CurrentDb.OpenRecordset("qryAutoOrder", dbOpenDynaset)
Set db = CurrentDb
Set qdf = db.QueryDefs("qryAutoOrder")
For Each prm In qdf.Parameters
    prm.value = Eval(prm.Name)
Next
Set rs = qdf.OpenRecordset(dbOpenDynaset)

You don't actually need a For loop there; that's just the way I set these up by habit. But you could just give it the single parameter value instead ...

qdf.Parameters(0).Value = [Forms]![completeRepair]![txtRepairID]