New employees are assigned a unique employee ID number (lngEmpId as long) in a tblNewHires table, and a unique system ID (strSysId as string) in a tblNewSystemUser table via a frmNewHires. The last step in the hire process is to add the tblNewEmployees records to the tblEmployees and add the tblNewSystemUser records to the tblSysUesrs table. I need to check the tblEmployees table and the tblSysUsers to be sure the unique numbers being assigned have not already been assigned to someone else. I do this:
‘check employee Id number for duplicates in the main employees table
Private Sub EmpId_AfterUpdate()
On Error GoTo Err_EmpId_AfterUpdate
Dim blnEmpId As Boolean
Dim lngNewEmpId As Long
lngNewEmpId = Me.EmpId.Value
blnEmpId = DLookup([EmpId], “tblEmployee”, “[EmpId]=” _
& lngNewEmpId)
If blnEmpId = True Then
MsgBox “This Employee Id number is already assigned. Please assign an unused employee id number for this employee.”, vbOKOnly, “Invalid Entry”
Me.EmpId.SetFocus
End If
Exit_EmpId_AfterUpdate:
Exit Sub
Err_EmpId_AfterUpdate:
Resume Exit_EmpId_AfterUpdate
End Sub
This seems to work fine for the employee ID number. (although there is probably a way to do this with a little less code.)
However, when I use the same code to test the SysId field, I get an error that says “You cancelled the previous operation”.
Does the datatype of the (strSysId as string) have anything to do with this or is there some other problem?
