Settings/Code to prevent duplicate record

[Golo]

Hi everyone! I am a newbie and I have a silly question here. Hope you could help me to solve it!

I have a table with 3 fields: Customer ID (Primary Key and Autonumber), Customer Name and Customer DOB. Now I want to prevent my users from entering the same customer. For example, there are 2 customers named John with different DOBs. If there has already been a record of the first John, I'd like to let my users enter the second John as long as they enter a different DOB and I'd like a message to pop up saying that "This customer might have already been in your database" if they enter the same DOB of the first John.

So in this case, how do I do that?

Thanks so much for your time!

 

[arnelgp]

on design view of your Table, add composite index on [customer name] and [dob] (without duplicate)

 

[Golo]

Thanks so much @arnelgp

 

[arnelgp]

if you are using Form, you can Validate the two fields on the Form's BeforeUpdate Event:

private sub form_beforeUpdate(cancel as integer)
if dcount("1", "yourTable", "[customer name] = '" & me!txtCustomerName & "' and [customer dob] = " & Format$(me!txtDOB, "\#mm\/dd\/yyyy\#")) <> 0 then
msgbox "This customer might have already been in your database"
cancel = true
end if
end sub

 

[taball0829]

I would suggest you need more data (Probably from a different table.) to make (Allow the user to make) this decision. I have been doing quite a bit of genealogy recently, and can tell you there are people with the same name and dob. Display customer addresses for the name you have entered. Name, dob and address are practically exclusive.

I do not think it is possible to prevent a user from entering the data again with your scenario. The only way I know of is to make the primary key the combination of name + dob. In this case the user would then need to enter a Customer ID to allow you to have two different customers with the same name and dob have different addresses and accounts. Maybe find the largest Customer ID and increment it for the user when they certify that it is actually a different person.

 

[taball0829]

Pat,

The original post states they are using name as an entity, not first, middle, last. From Golo: "I have a table with 3 fields: Customer ID (Primary Key and Autonumber), Customer Name and Customer DOB."

I am not working on making a genealogy database, it was an example about the ambiguity of using name and dob as exclusive criteria.

 

[Isaac]

Agree with Pat - just create a unique index that prevents the dupes from happening.

The next consideration is how to handle the GUI situation, where you have 2 basic choices that I can think of:

1. Go ahead and evaluate the uniqueness of the record during Form operation, too. The advantage of this is that you can present your user with a graceful error message rather than letting the Table error message provide a hard (and perhaps inelegant) stop. The disadvantage of this is that now you are duplicating the table constraint again in code, which will make your form work slower and provide a worse user experience.
2. Do not evaluate the actual uniqueness of the record during Form operation.......however, figure out a way to "trap" the error that will come back from the Table constraint if the constraint is violated - thus a) providing the user an informative message, and b) avoiding the form slowness that you'll incur if you try to do this 'again' at the form level

I think I'd try for the unique index + #2