Preventing duplicate entries

[Sam Summers]

Hi all,
I am currently using this code in the before Update event of the Surname Textbox on a form. The first Textbox is FirstName.
The second one is Surname.

****************************************************

If (Not IsNull(DLookup("[FirstName]", _
"Employee", "[FirstName] ='" _
& Me!FirstName & "'"))) And (Not IsNull(DLookup("[Surname]", _
"Employee", "[Surname] ='" _
& Me!Surname & "'"))) Then
MsgBox "Someone already exists with the same name! Please check for duplicates", vbCritical, "IPDMS"
Me.Undo
End If

****************************************************

However once the user has entered the surname and tries to save the record the MsgBox is coming up.
Basically what is happening is the first name is coming up as the duplicate entry only.
I need to prevent the whole name (first name and surname together) from being duplicated.

Thanks if you can help me.

 

[Matt Greatorex]

I seem to remember someone recently having a problem with using AND in an If statement. They got around it by using the following kind of statement. Still checks both clauses, but removes the need for the AND.

Hope it helps.

If (Not IsNull(DLookup("[FirstName]", "Employee", "[FirstName] ='" & Me!FirstName & "'"))) Then
If (Not IsNull(DLookup("[Surname]", "Employee", "[Surname] ='" & Me!Surname & "'"))) Then
MsgBox "Someone already exists with the same name! Please check for duplicates", vbCritical, "IPDMS"
Me.Undo
End If
End If

 

[Sam Summers]

Thanks for that but unfortunately that still didn't work.
I'm baffled !!
Seems like it should be pretty straightforward.
Just need to check the firstname combined with the last name with entries already in the table?
I will find the answer eventually.

 

[Matt Greatorex]

Looking athe code again, it looks like the two checks are separate. i.e.
1) 'is there someone called John' (true or false) and
2) 'is there someone whose last name is Smith' (true or false),
rather than 'is there someone called John Smith'.

the check should be more like:

'where the first name = John and (the second name = Smith where the first name = John)'

That way you'd only be getting a match for all people where both names are the same.