Settings/Code to prevent duplicate record (1 Viewer)

[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.

 

[Pat Hartman]

tabal,
A unique index prevents duplicates from being saved the same way the PK does. The PK is simply another unique index. For ease and efficiency in joins, most tables will have an autonumber/identity column as the PK. However business rules often require unique indexes to prevent duplicates.

Using first, middle, last, names and DOB are probably sufficient to prevent duplicates in most applications but something that deals with gemology might need more attributes to ensure uniqueness.

 

[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

 

[Pat Hartman]

Mushing all the parts of a name into a single column just leads to future problems. Start from the beginning to split the name. If you want to have form of address (Mr., Mrs., etc) add a field for that and also Suffix if you want things like (Jr., III, Esq, etc)

If you want to give the user a "friendly" error message instead of what you will bet from Access, in the Form's BeforeUpdate event, use a DCount() to see if you get any records with matching values in the combination of fields you decide will be unique.

Define this constant in a general code module, NOT in the class module of a form or report. That will allow you to use it everywhere even when the form is not open.

Const QUOTE As String = """"

In the BeforeUpdate event of the FORM:

If dCount("*", "tblPeople", "First = " & QUOTE & Me.First & QUOTE & " AND Last = " & QUOTE & Me.Last & QUOTE & " AND DOB = #" & Format(DOB, "mm/dd/yyyy") & "#") > 0 Then
    Msgbox "This person alreadh exists.", vbCancelOnly
    Cancel = True
    Exit Sub
End If

I define the QUOTE constant in all my databases to give me an easy way to enclose text in double quotes. It is far easier to read than expressions with varying numbers of " all strung together.

REMEMBER - last names sometimes contain single quotes that means that they cannot be delimited with single quotes or you wll get an error so ALWAYS use this method when you are working with strings that might include single quotes.

And, since I don't know what country you are working in, I formatted the DOB as mm/dd/yyyy so you won't run afoul of Access misinterpreting dates in dd/mm/yyyy format if that is your local format.