Solved Code to check for duplicate entries

[rayape]

I have two text boxes. 1. To enter the First Name and 2. To enter the Last Name. After I enter the Last Name and click tab or place the cursor elsewhere, I would like Access to check for duplicate entries. If there is already an existing record, I would like Access not save the name, instead notify me. If there is no record, however, I would like Access to save the name.

I am using the following code. Sometimes, although there are no existing records, the code tells me there is an existing record. I can't figure out what is going on. Need some help, please.
Thanks.

Private Sub LastName_AfterUpdate()
Dim NewFN As String
Dim NewLN As String
Dim stLinkCriteriaFN As String
Dim stLinkCriteriaLN As String

NewFN = Me.FirstName.Value
NewLN = Me.LastName.Value
stLinkCriteriaFN = "[FirstName] = " & "'" & NewFN & "'"
stLinkCriteriaLN = "[LastName] = " & "'" & NewLN & "'"

If IsNull(NewFN) Or IsNull(NewLN) Then
MsgBox "Please enter First and Last Name to continue", vbInformation, "Enter Name"
End If

If Me.LastName = DLookup("[LastName]", "tbl_Participant", stLinkCriteriaLN) And Me.FirstName = DLookup("[FirstName]", "tbl_Participant", stLinkCriteriaFN) Then
MsgBox "This participant already exists in the database.", vbOKOnly, "Check the Name."
Me.Undo
End If
End Sub

 

[isladogs]

For a start you should add the line Exit Sub after your first MsgBox line.
Secondly remove .Value. Its not needed

 

[plog]

If Me.LastName = DLookup("[LastName]", "tbl_Participant", stLinkCriteriaLN) And Me.FirstName = DLookup("[FirstName]", "tbl_Participant", stLinkCriteriaFN) Then

You need to do both comparisons at the same time. You're doing one then the other.

tbl_Participant
FirstName, LastName
John, Smith
Steve, Jones

Suppose you try to enter John Jones. First comparisons sees there's a Jones, so that's true. Second comparison sees there's a John so that's true. Since they are both true, it hits your duplicate code. You need to do both comparisons at once.

I would throw away the DLookups and use one DCount(https://www.techonthenet.com/access/functions/domain/dcount.php). It's similar to a DLookup but is guaranteed to return a number. 0 means unique name, >0 means a match is in there. For its criteria, combine both the DLookup criteria you are using now.

 

[rayape]

@plog Thanks for the advise. I am only a MS Access user and have very rudimentary knowledge in programming. I would appreciate if you don't mind helping me with the code. Thanks very much.

 

[plog]

If DCount("[LastName]", "tbl_Participant", stLinkCriteriaLN & " AND " & stLinkCriteriaFN)>0 Then

 

[rayape]

Worked like magic! You are amazing, plog. Thanks for the help.

@isladogs I made the changes you mentioned too. Thanks to you as well.