对象必需错误MS Access SQL

Question

我在VBA编码中被这个SQL语句卡住了。我正在尝试使用DLookUp完成更新查询，但我可能没有正确编写代码。基本上，我希望WorkerID和Workername来自DLookUp，来自用于获取此处数据的UserIDGetNextAssignee("program", "Language", "username")。我在这里得到一个对象所需的错误：

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

以下是上下文的完整代码：

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(attendance.username, "attendance", "username = UserID") & ", WorkerID = " & DLookup(attendance.userID, "attendance", "WorkerID = UserID") & " WHERE CFRRRID = " & rs!CFRRRID
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
rs.MoveNext
Wend
End If

谢谢！

Answer 1

您的DLookup表达式不正确。DLookup的目标字段也是一个字符串表达式。所以应该是：

... & DLookup("username", "attendance", "username = UserID") & ...

SQL中多次出现此错误。

除此之外，您还应该检查条件表达式：

..."username = UserID"...

这在我看来很可疑。但如果不了解表和数据结构，我就无法判断它是否正确。