Checking for Duplicate Employee Names (2 Fields)

[wilderfan]

Still trying to get my head around vba code. Perhaps one of you has some sample code that will help.

I have a form to enter new employee names.

Before saving the new entry, I would like ACCESS to check the existing table records to see if the new name already exists.

If so, then the user should be asked whether he/she is sure about creating a new record for this name. (He/she should be able to choose "Yes" because there could be people with the same first and last names).

Here is some additional info which may be useful:

tablename is Employee

fields include:

EmpId (PK)
FirstName
LastName
JoinDate


formname is frmAddNewEmployee
textbox for First Name is ubdFirstName
textbox for Last Name is ubdLastName

[NOTE: Entries in the unbound textboxes are later transferred into the "real" textboxes after the edit checks].

Can anyone suggest some code that will alert users of the potential duplicate entry, yet still allow them the option of adding the new record anyway?

Thanks.

 

[dkinley]

You can slap this on the BeforeUpdate() event ...

Dim iEmpId  As Long
 
iEmpId = Nz(DLookup("[EmpId]", "tblEmployee", _
    "[FirstName] = '" & Me.ubdFirstName & "' And " &_
    "[LastName]= '" & Me.ubdLastName & "'"), 0)
If iEmpId <> 0 Then
    MsgBox "This employee already exists. " & vbCrLf & _
        vbCrLf & "Please enter a different employee.", _
        vbCritical, "Duplicate Employee"
    Cancel = True
End If

Hope that helps,
-dK

 

[wilderfan]

Thanks, dK.

If I'm reading the code properly, I think I still need to "tweak" it a bit to allow the user to save the new name/record if the new employee truly is a different person. eg. say there are 2 John Smiths.

 

[dkinley]

Correct ... you can make use of your join field or any other identifier that you may collect.

Just an launch pad to get you down the path ....
Good luck!

-dK