Run time Error too few parameters Expected 2

Question

I am trying to run a query from the Access Query designer that is working fine in Access but when I try to bring the statement across to VBA it is giving me this error message:

Run time error too few parameters. Expected 2.

I have printed the statement in the immediate window and run it in Access and it is running without asking for parameters. I have done a number of web searches the general consensus seems to be to declare it all in VBA, including the parameters -

Private Sub CmdAppend_Click()
    Dim db1 As Database
    Dim mystr As Recordset2
    Dim UserName As String
    Dim UpdateSQL As String
    Dim SelectIDSQL As String
    Dim checkstr As String

    If Validate_Data = True Then

        UserName = Environ$("Username")

        SelectIDSQL = "Select Distinct ChecklistResults.[StaffID]" _
            & " From ChecklistResults" _
            & " Where (((ChecklistResults.[ClientID])=[Forms]![TeamLeader]![ComClientNotFin])" _
            & " And ((ChecklistResults.[DateofChecklist])=[Forms]![TeamLeader]![ComDateSelect])" _
            & " AND ((ChecklistResults.[ManagerID]) Is Null));"

        Debug.Print SelectIDSQL

        Set db1 = CurrentDb
        Set mystr = db1.OpenRecordset(SelectIDSQL)
        checkstr = mystr!StaffID

        If checkstr <> UserName Then

I receive the above error message when I try to set mystr to the recordset. I think I can get the recordset by following the format below but is there a way of getting the above SQL statement/assignment to work?

Dim qdf1 As DAO.QueryDef

Set qdf1 = db1.QueryDefs("Get_StaffID")
qdf1.Parameters(0) = [Forms]![TeamLeader]![ComClientNotFin]
qdf1.Parameters(1) = [Forms]![TeamLeader]![ComDateSelect]
Set rst1 = qdf1.OpenRecordset(dbOpenDynaset)

Answer 1

As I look at this page, I see examples where the OpenRecordSet method takes two arguments. You have an error message that says something was expecting 2 parameters. Try changing this:

Set mystr = db1.OpenRecordset(SelectIDSQL)

to this:

Set mystr = db1.OpenRecordset(SelectIDSQL, dbOpenDynaset)

Answer 2

Thanks for the input, I used the following code to get the result I was looking for. It uses the query SelectClientID to return the ID of the person who completed the first stage of a checklist. it then checks the person who has done the second check and if they match it returns an error message. If two different people have completed it, it uses the SQL statement to update the previous record with the second checker's ID -

Private Sub CmdAppend_Click()
Dim rst1 As Recordset2
Dim db1 As Database
Dim mystr As Recordset2
Dim UserName As String
Dim UpdateSQL As String
Dim SelectIDSQL As String
Dim checkstr As String
Dim qdf1 As DAO.QueryDef

Set db1 = CurrentDb
Set qdf1 = db1.QueryDefs("SelectClientID")
qdf1.Parameters(0) = [Forms]![TeamLeader]![ComClientNotFin]
qdf1.Parameters(1) = [Forms]![TeamLeader]![ComDateSelect]
Set rst1 = qdf1.OpenRecordset(dbOpenDynaset)

If Validate_Data = True Then

    UserName = Environ$("Username")

   UpdateSQL = "UPDATE ChecklistResults" _
    & " SET ChecklistResults.[ManagerID] = '" & UserName & "'" _
    & " WHERE (((ChecklistResults.[ClientID])=[Forms]![TeamLeader]![ComClientNotFin])" _
    & " AND ((ChecklistResults.[DateofChecklist])=[Forms]![TeamLeader]![ComDateSelect])" _
    & " AND ((ChecklistResults.[ManagerID]) Is Null));"


    checkstr = rst1!StaffID
        If checkstr <> UserName Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL UpdateSQL
        DoCmd.SetWarnings True
        DoCmd.Close
        Else
        MsgBox ("This Checklist was created by you and cannot therefore Checked by you")
        End If
Else
Exit Sub
End If
End Sub