Compile error MS Access SQL

Question

I'm getting a compile error: "Argument not optional" Basically in this code, I'm trying to do a around the table style auto-assigning. When the code is called, it starts at AssignNullProjects() and when I get the error, GetNextAssignee is highlight in the part " & GetNextAssignee & "

I'm not really sure how to fix it, here's my full code below:

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 SET assignedto = " & GetNextAssignee & ", assignedby = " & [Forms]![CFRRR]![assignedby] & ", Dateassigned = #" & Now & "#, actiondate = #" & Now & "#, Workername = " & _
                              [Forms]![CFRRR]![assignedto] & ", WorkerID = " & [Forms]![CFRRR]![assignedto] & " WHERE CFRRRID = " & rs!CFRRRID

            db.Execute strSQL, dbFailOnError
            rs.MoveNext
        Wend
    End If

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

End Function

Public Function GetNextAssignee(program As String, Language As String) As Long
'   Returns UserID as a Long Integer with the lowest [TS] value,
'   and updates same [TS] by incremented with 1.

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

    Set db = CurrentDb
    strSQL = "SELECT TOP 1 WorkerID FROM attendance WHERE [Programs] LIKE '*" & program & "*' AND [Language] = '" & Language & "' AND [Status] = '" & Available & "' ORDER BY TS ASC"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.BOF And Not rs.EOF Then
        'Found next assignee, update date/time stamp
'        strSQL = "UPDATE tblUser SET TS = " & DMax("[TS]", tblUser) + 1 & " WHERE [WorkerID]= " & rs!workerid
        strSQL = "UPDATE attendance SET TS = " & DMax("[TS]", "attendance") + 1 & " WHERE [WorkerID]= " & rs!workerid
        db.Execute strSQL, dbFailOnError
        GetNextAssignee = rs!workerid
    Else
        'Field TS has NO VALUE FOR ALL RECORDS!
        'Code calling this function should check for a return of 0 indicating an error.
        GetNextAssignee = 0
    End If

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

End Function

GetNextAssignee clearly requires two parameters (program and Language), as shown in the declaration; your calling code provides neither of them with & GetAssignee &). What else would you expect to happen? "In order to be delivered by the postal service, your letter requires two things: a delivery address, and a stamp with sufficient postage. You've provided neither of them. We cannot complete your request for delivery of this letter." — Ken White
– Ken White, Commented May 9, 2015 at 4:46

HansUp · Accepted Answer · 2015-05-09 05:27:11Z

2

When you call this function, you must supply 2 arguments (string values):

Public Function GetNextAssignee(program As String, Language As String) As Long

But when building your UPDATE statement, you call that function without supplying the required arguments:

strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee & ", assignedby = "

So Access is complaining that those (string) arguments are not optional --- you must supply them. Your code should compile when you give the function 2 strings like this ...

strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee("foo", "bar") & ", assignedby = "

Although the code would compile with those strings, the function would probably not return the result you need. So substitute realistic values in place of foo and bar.

edited May 9, 2015 at 5:27

answered May 9, 2015 at 4:46

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lilly Over a year ago

Do I type it just as I have it in the function? " & GetNextAssignee(program As String, Language As String) As Long & " but I'm getting an error saying list separator or ) and As is highlighted.

Lilly Over a year ago

Great that did the trick, now I'm getting an error on the bottom half, saying too few parameters: expected 2.

strSQL = "SELECT TOP 1 WorkerID FROM attendance WHERE [Programs] LIKE '*" & program & "*' AND [Language] = '" & Language & "' AND [Status] = " & ("Available") & " ORDER BY TS ASC"     Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

I can ask another question though.

HansUp Over a year ago

Yes, that is a different problem, so a new question for it is the way to go. When you write that question, please paste in the output from Debug.Print strSQL when you encounter the new error. It's better to show us the actual failing statement than only the code which creates the statement.

Lilly Over a year ago

Question asked with the Debug.Print entered: stackoverflow.com/questions/30142827/…

Collectives™ on Stack Overflow

Compile error MS Access SQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related