在SQL MS访问串错误(String error in SQL MS Access)

我不知道如果我的标题是100％准确的，但我认为这是在我的代码的问题。我得到这个代码的工作，但是当我更改了我的代码的其他部分，并将其停止工作。下面是完整的SQL：

UPDATE CFRRR    
  SET assignedto = " & GetNextAssignee("program", "Language", "username") & ", 
    assignedby = '" 
    & Forms!Supervisor!NavigationSubform!assignedby.Value 
    & "', Dateassigned = #" 
    & Now & "#, actiondate = #" 
    & Now & "#, Workername = '" 
    & DLookup("username", "attendance", "userID = " 
    & GetNextAssignee("program", "Language", "username")) 
    & "', WorkerID = " & DLookup("userID", "attendance", "userID = " 
    & GetNextAssignee("program", "Language", "username")) 
    & " WHERE CFRRRID = " 
    & rs!CFRRRID

我正的错误是在这里：

Workername = '" 
& DLookup("username", "attendance", "userID = " 
& GetNextAssignee("program", "Language", "username"))

这是我得到的输出：

UPDATE CFRRR SET assignedto = 6, 
assignedby = '33', 
Dateassigned = #5/17/2015 7:46:40 PM#, 
actiondate = #5/17/2015 7:46:40 PM#, 
Workername = 'Valentino', 
WorkerID = 7 WHERE CFRRRID = 40

这是我应该得到的输出：

UPDATE CFRRR SET assignedto = 6, 
assignedby = '33', 
Dateassigned = #5/17/2015 7:46:40 PM#, 
actiondate = #5/17/2015 7:46:40 PM#, 
Workername = 'John', 
WorkerID = 6 WHERE CFRRRID = 40

和这里：

WorkerID = "
& DLookup("userID", "attendance", "userID = "
& GetNextAssignee("program", "Language", "username"))

下面是引用GetNextAssignee代码：

Public Function GetNextAssignee(program As String, 
                              language As String, 
                              username As String) As Long
        Dim db As dao.Database
        Dim rs As dao.Recordset
        Dim strSQL As String
        Set db = CurrentDb
        strSQL = "SELECT TOP 1 
                   userID 
                 FROM attendance as a, 
                 CFRRR WHERE a.Status = 'Available' 
                 AND a.Programs LIKE CFRRR.program  
                 AND a.Language = CFRRR.language 
                 ORDER BY TS ASC, userID, CFRRRID"
        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        If Not rs.BOF And Not rs.EOF Then
      strSQL = "UPDATE attendance 
                SET TS= " & DMax("[TS]", "attendance") + 1 
                & " WHERE [userID]=" & rs!userID 
                & " AND [Status]=""Available"""
            db.Execute strSQL, dbFailOnError
            GetNextAssignee = rs!userID
        Else
            GetNextAssignee = 0
        End If

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

    End Function

如下更改代码

Dim i as long

和你的while循环中AssignNullProjects改变这样的查询

If Not rs.BOF And Not rs.EOF Then
        While Not rs.EOF

i = GetNextAssignee("program", "Language", "username")

 strSQL = "UPDATE CFRRR SET assignedto = " & i & ",
 assignedby = '" & Forms!Supervisor!NavigationSubform!assignedby.Value 
& "', Dateassigned = #" & Now & "#, actiondate = #" 
& Now & "#, Workername = '" 
& _DLookup("username", "attendance", "userID = " & i)
 & "', WorkerID = " & i & " WHERE CFRRRID = " & rs!CFRRRID
Debug.Print strSQL
 db.Execute strSQL, dbFailOnError
  rs.MoveNext
        Wend
    End If