I'm getting an error saying that Access cannot find the referenced form CFRRR but there is definitely a form there. Not sure if I'm just not writing the code correctly.
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