Too few parameters Expected 1, recordset issue

Question

I'm having a problem getting a recordset to run from a query I created in an MS Access 2010 database. here is t he code I want to run:

Private Sub Command192_Click()
Dim recs As String
Dim param As Integer
Dim rs As Recordset

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

With rs
.MoveLast
.MoveFirst

While Not .EOF

recs = recs & vbNewLine & !Spara & " - " & !Rec
.MoveNext

Wend

End With


MsgBox (recs)
End Sub

What this should output is a message box with a number of records from the query in a list. I do this so I can gather this and a number of other records into a text file for copying and pasting into a separate system. At the moment, I'm running this code so I can place it all into a string variable.

My problem is that I'm getting the dreaded "Too Few parameters expected 1" error.

The query works, I've saved it into the database and tested it and I get the expected results.

I tried running the recordset with SQL:

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)

I get the same error

I ran it again but removed the "WHERE" statement and the code ran just fine, but gave me every record in the table. Not what I wanted.

So, the fields are OK because the data runs. When I debug the text the parameter in the SQL does show up as the right parameter, in this case, the number 4 which is an integer.

So I'm at a loss here, I've searched through the other posts here and I have tried these possible solutions (unless I missed something).

I also tried using dbopensnapshot as well, still no joy. Wondering if I'm using the right code here now.

Any help would be great.

Cheers

Answer 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)