Duplicate Names

[Sam Summers]

I have a firstName text field on a form and a Surname Text field. on the Event AfterUpdate i have this code:

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

I am trying to Check the table "Employee" to prevent duplicate personnel from being entered.

My code at the moment is preventing any duplicate surname, its not combining it with the FirstName.

i.e. any person with the surname "Summers" cannot be entered twice even if they have different FirstName.

Thank you in advance.

 

[MStef]

Hello Summers!

Put INDEX on the field SURNAME, Unique = Yes.
Open tbl in design view, click on INDEXES icon,
and put index.

 

[RuralGuy]

I would create a query that concantenates the [FirstName] & " " & [SurName] as [FullName] and do my concantenated DLookUp() against the query.

 

[Sam Summers]

Thanks for that.

Just had an attempt at it but no luck. Tried to find something on here that would match but nothing seemed to be the same or worked.

As i said, all i want to do is ensure that no duplicate employees are entered like two Michael forests, but at the moment it is preventing users from entering any forest. ??

 

[RuralGuy]

Sam,
Can you post your db stripped of any sensitive data but with enough sample data to demonstrate the problem? It would be easier to fix and easier to show you what I mean.

 

[valheru]

Hi

Just a quick question, I assume that you want to check that a name doesn't exist before updating, so why are you placing the code in the after update event?

 

[RuralGuy]

Boy I sure missed that! Good catch Valheru. The validation code should be in the BeforeUpdate event and rather than using UnDo just set Cancel = True to hold the user in the control.

 

[Banana]

You also would make it much easier for yourself and your users if you used a combobox bound to the concentating query with the names and having a Not In List event fire to allow user to add name.

A BIG benefit of combobox is they have Auto Expand so users will only need to start typing in few letters and they'll get the name. It serves as a safeguard against bad data.

 

[Sam Summers]

Hi Guys,

Thanks for taking the time. Here is a stripped down copy of the DB.
If you try and just enter two names and surnames the same, you will see what i mean.
Bear in mind that the actual database holds over a 1000 personnel.

If you can help me i will be most appreciative.

Thanks