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.
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]
