我会收到一个编译错误:"参数不是可选"基本上,在此代码中,我正在尝试在表格样式自动分配周围进行。当调用代码时,它始于AssignNullProjects()
,当我获得错误时,GetNextAssignee
是 " & GetNextAssignee & "
我不确定如何修复它,这是我的完整代码:
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
调用此功能时,必须提供2个参数(字符串值):
Public Function GetNextAssignee(program As String, Language As String) As Long
但是,当构建UPDATE
语句时,您可以在不提供所需参数的情况下调用该功能:
strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee & ", assignedby = "
因此,访问抱怨说这些(字符串)参数不是可选的 - 您必须提供它们。当您提供函数2个字符串时,您的代码应编译...
strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee("foo", "bar") & ", assignedby = "
尽管代码将使用这些字符串编译,但该函数可能不会返回所需的结果。因此,代替 foo 和 bar 。