CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Lilly
Lilly

Reputation: 233

Parameters Error MS Access SQL

I'm getting a Parameters error here: db.Execute strSQL, dbFailOnError saying "too few parameters, expected 1". I'm not sure exactly why. I'm referencing both tables. I'm also not 100% this is written correctly Workername = " & DLookup("username", "attendance", GetNextAssignee("program", "Language", "username")) I want [Workername] field to update to the workername that is linked to the GetNextAssignee("program", "Language", "username") which I'm not getting and it could be connected to the this error.

Public Function AssignNullProjects() As Long

    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT CFRRRID, [program], [language] FROM CFRRR WHERE assignedto Is Null"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.BOF And Not rs.EOF Then
        While Not rs.EOF

            strSQL = "UPDATE CFRRR,attendance SET assignedto = " & GetNextAssignee("program", "Language", "username") & ", assignedby = " & Forms!Supervisor!NavigationSubform!assignedby.Value & ", Dateassigned = #" & Now & "#, actiondate = #" & Now & "#, Workername = " & _
                              DLookup("username", "attendance", GetNextAssignee("program", "Language", "username")) & ", WorkerID = " & DLookup("UserID", "attendance", GetNextAssignee("program", "Language", "username")) & " WHERE CFRRRID = " & rs!CFRRRID
            Debug.Print strSQL
            db.Execute strSQL, dbFailOnError
            rs.MoveNext
        Wend
    End If

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

End Function

Here is what the Debug.Print strSQL shows:

UPDATE CFRRR,attendance
SET
    assignedto = 7,
    assignedby = 33,
    Dateassigned = #5/16/2015 11:16:31 AM#,
    actiondate = #5/16/2015 11:16:31 AM#,
    Workername = Lillian,
    WorkerID = 6
WHERE CFRRRID = 6

Upvotes: 2

Views: 71

Answers (1)

HansUp
HansUp

Reputation: 97101

It seems Workername is a text field and you want to store the string Lillian there. Add quotes so the db engine will understand Lillian is literal text instead of the name of a parameter.

UPDATE CFRRR
SET
    assignedto = 7,
    assignedby = 33,
    Dateassigned = Now(),
    actiondate = Now(),
    Workername = 'Lillian',
    WorkerID = 6
WHERE CFRRRID = 6

Since the db engine supports the Now() function, you can ask it to store the value of Now() in your Dateassigned and actiondate fields. That is simpler than taking the value of Now() in VBA and then adding # characters around that value to concatenate into the statement text.

I'm skeptical that UPDATE CFRRR,attendance was the right choice. That would be a CROSS JOIN between the two tables and Access may therefore decide the query is not updateable. I suggest you UPDATE just the CFRRR table.

Upvotes: 1

Related Questions