How do i create a "unique" field

[unclefink]

I am trying to figure some things out and thought of something that may solve my problem.

I have a table as followed;

EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK

What I want to do is create a new field that automatically populates based on what is entered into the above fields.

Essentially creating a single field with unique data generated by multiple fields.

For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test

The new field can be generated as followed: JohnPDoeFederalgovTest

What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.

Any help would be greatly appreciated as well as other reccomendations so as not to allow duplicated data.

Respectfully,

 

[John Big Booty]

You already have an AutoNumber Primary Key field, you don't need another "Unique Field". This would essentially be a calculated field, and as such storing it would not be in the interests of good Data Normalisation practices.

You can use the DLookUp() function in the Form's Before Update event to check and warn of potential duplicates.

 

[unclefink]

John,

I think you were involved in my initial posting that this was referenced in. I am relatively inexperienced with writing vba which i believe is causing a lot of my own confusion.

I had this same issue in another database I built that referenced First Name Last Name and date of birth. I tried to use that code for this application after adding the necessary changes to no luck. I suspect the difference in the last database to this new one is the fact I was searching for "raw data" however in this case i am searching for a foreign key as part of the "dlookup".

Here is the code I am using in the last database; however i'm having no luck making necessary changes to apply it to the one i'm working on now.

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

Can you tell me what I need to change to accomodate this new lookup.

I want it to check tblemployee
FirstName
MInitial
LastName
Company- which will be a FK reference.

Also the "MInitial" will not always have data in it; therefore Null values are acceptable.

Thank you in advance for any help you might give.

respectfully,

 

[gemma-the-husky]

unclefink

if you want to use the employee as a FK in another table - then i would add a autonumber key to the employee table, and use that for the FK.

the matter of controlling the validness of the employees in the employee is a different issue, and best managed by unqiue keys in the employee table.

 

[qaziashfaq]

thanks your sharing information.