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]=" _
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"
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?