Prohibiting Duplicate Data

[unclefink]

I'm having some issues with stopping duplicate entries in a table and have tried a copule different ways with no avail.

In one table in particular, I have a table holding employees as followed;

EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
DepartmentFK

What I am trying to do is stop the ability to duplicating records over a series of fields (FirstName, MInitial, LastName, Company)rather than just one specific field.

I attempted to index the table as followed;

EmployeeID: PK/Unique
FirstName: Unique
MInitial: Unique
LastName: Unique
Company: Unique

The problem with this is it wont allow me to enter more than one person with the same first, middle, or last name or company.

I tried "DCount" Code in a form with no luck, that code reads as followed:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblEmployees", "[firstname]=" & Chr(34) & Me!firstname & Chr(34) & _
" And [MInitial]=" & Chr(34) & Me!MInitial & Chr(34) & _
" And [LastName]=" & Chr(34) & Me!LastName & "#") > 0 Then
Msgbox "Person already exists."
Cancel = True
End If
End Sub

Note: this code was initially used on a different database I was working on that accounted for an individuals First Name, Last Name, and Date of Birth which worked great. I am just trying to apply that similar concept to this new database with first name, middleinitial, last name, and company; which will be a foreign key.

Not all too familiar with vba, im running out of thoughts. Can anyone plese help me with this or suggest an alternative method?

Thank you in advance for help. It's greatly appreciated.

:banghead:

 

[Sketchin]

I have this code in a textbox :

=IIf(DCount("*","[tblContacts Extended]","[ID]<>" & Nz([ID],0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'")>0,"Possible Duplicate","")

 

[unclefink]

I have this code in a textbox :

=IIf(DCount("*","[tblContacts Extended]","[ID]<>" & Nz([ID],0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'")>0,"Possible Duplicate","")

At this point i'll give anything a try, can you tell me what this code means and what information I need to change to accomodate my table?

 

[Sketchin]

Change "tblContacts Extended" to "tblEmployees", change "Contact Name" to "FirstName" and "ID" to "EmployeeID"

All that this code will do is warn you that you have a duplicate entry, it won't prohibit duplicate entries.

Could you make all 4 of those fields a primary key? Not sure if thats a bad idea or not..

 

[unclefink]

Just to clarify, you said you have this code in a text field. Is the text box saving to a table? or just referencing the table?

In the text box, where are you putting this code?

 

[Sketchin]

Its in the control source of an unbound textbox.

All it does is show "Possible Duplicate" if there is a duplicate entry.

 

[unclefink]

Well thats cool.

Just thought of something that may resolve my issue.

If i create a new field in the tblemployee table that combines data from the firstname,MInitial, LastName, and Company ID and then "index" that specific field, it should work. Question is; how do make inforomation enterd into one field default to another field. Which i will hide on the data entry form.

 

[Sketchin]

I was actually going to suggest that, till I looked into it and found you can't index calculated fields. You would have a calculated field that looks like this:
[First Name] & "" & [Last Name] & "" & etc...

 

[unclefink]

I've got some calculated queries using that combination and work great. Is there a way to do this similarily in a text box within a form?

 

[gemma-the-husky]

the real life problem is that people can and do have the same names. maybe a father and son with the same name live at the same address.

you can use other stuff like DOB to try and make the data unique, but sometimes you don;t have that data to hand.

at some point, the person setting up the employee record will have to take responsibility for ensuring that a new record is actually needed. all you can do is flag that the data is already there, and confirm that they are sure it is a new record, and not a duplicate.

you may even get an employee leaving and then rejoining - in which case you probably do need an identical duplicate record for the new employment.

 

[Manfred]

Hello Unclefink. I am new to the forum and access db. Can't help you with your query, however you can help me. In your original post you mentioned code you used on a different database for surname, firstname and date of birth. Can you post that code as it is what I am searching for please?