Avoiding duplicate entries

[Bham45]

Not sure if I should have posted this under tables or forms. But if different people will be entering data into a "new donor" form, how do I keep them from adding the same person a second time?

It's possible people could have the same name, and it's possible that two people at the same address could be donors. So I assume I would want a "no duplicates" on the combination of name and address, right? But I have no idea how to actually do that.

Thanks for your help.

 

[vbaInet]

Also two people could have the same name and live in the same address so if you have a DOB then that would make a better triple combination. Otherwise you can use the Name and Postcode (or zip).

Perform a count using the DCount() function to determine whether that person exists:

 

[burrina]

Sounds like a case for a composite key to me.

 

[Bham45]

vbaInet, we wouldn't be able to get DOBs as some people are VERY private and are reluctant to give us even basic information. But can I do some kind of check using a calculated field "AddressAs" (combines Title, FirstName, LastName for "Mr. & Mrs. John Doe") and the ZIP code? (All from the Donors table.)

At this point I don't know where/how to apply DCount, but will look into it... Just wondering if this is something that would be included in the "Add New Donor" form, or would it be applied elsewhere?

 

[vbaInet]

What other pieces of information do you have?

Look into it and get back to us if you need further assistance.

 

[Bham45]

I couldn't figure out the DCount function, but I did read about and then created an Index in the Donors table that wouldn't allow duplicates on First Name, Last Name, and Zip code. I'm hoping we don't have a father and son with same name living in the same house as donors. I'll probably add a note somewhere to add a middle initial to the first name if that ever happens. So John Smith and John E. Smith. (I have elected not to have fields for Sr./Jr./III.)

 

[vbaInet]

You can add as many fields as you want. I'll start you off with First and Last names and see if you can add the zip code part. The following assumes that the First and Last Name field names don't contain spaces and that your form is bound:

Dim intCount As Integer

intCount = DCount("*", "TableName", "[[COLOR="Blue"]FirstName[/COLOR]] = '" & Me.[COLOR="blue"]FirstName [/COLOR]& "' AND [[COLOR="blue"]LastName[/COLOR]] = '" & Me.[COLOR="blue"]LastName [/COLOR]& "'")

If intCount > 0 Then
    Cancel = True
    Msgbox "This person already exists"
End If

The ones in square brackets are the field names in your table and the other is the one on your form and take note of the single quotes used to enclose text (the same as how you would enter criteria under a Text field in a query.

Put the code in the Before Update of the form.