Too few parameters Expected 1, recordset issue

Solution 1:

A parameter like [Forms]![SurveyRegister_frm]![SurveyID] doesn't get evaluated automatically if you open a recordset in VBA.

Use this function:

Public Sub Eval_Params(QD As DAO.QueryDef)

On Error GoTo Eval_Params_Err

    Dim par As DAO.Parameter

    For Each par In QD.Parameters
        ' This is the key line: Eval "evaluates" the form field and gets the value
        par.Value = Eval(par.Name)
    Next par

Eval_Params_Exit:
    On Error Resume Next
    Exit Sub

Eval_Params_Err:
    MsgBox Err.Description, vbExclamation, "Runtime-Error " & Err.Number & " in Eval_Params"
    Resume Eval_Params_Exit

End Sub

with a QueryDef object like this:

Dim QD As QueryDef
Dim RS As Recordset

Set QD = DB.QueryDefs("UnitRec_Qry")
Call EVal_Params(QD)
Set RS = QD.OpenRecordset(dbOpenDynaset)

Alternatively, you can run it with SQL in the VBA code by moving the parameter outside of the SQL string:

Set rs = CurrentDb.OpenRecordset("SELECT UnitRecommend_tbl.URecID, UnitRecommend_tbl.Spara," _
& " UnitRecommend_tbl.Rec, UnitRecommend_tbl.SvyID" _
& " FROM UnitRecommend_tbl" _
& " WHERE ((UnitRecommend_tbl.SvyID) = " & [Forms]![SurveyRegister_frm]![SurveyID] & ")" & _
& " ORDER BY UnitRecommend_tbl.Spara;", dbOpenDynaset)