Surrogate Key/Prevent Dups

[Michael]

Hi Folks - Question ... I've decided to use an Autonumber primary key for a
customer database. We do not use customerIDs. Since I am using Autonumber,
not composite keys, how can I prevent duplicate customers if I have their
info in lastname, firstname, address, city, state, zip and phone number
fields. Thanks.

 

[John W. Vinson]

Hi Folks - Question ... I've decided to use an Autonumber primary key for a
customer database. We do not use customerIDs. Since I am using Autonumber,
not composite keys, how can I prevent duplicate customers if I have their
info in lastname, firstname, address, city, state, zip and phone number
fields. Thanks.

With some difficulty.

Are Fred Brown, 123 Main St., Podunk Idaho and Fred Brown, 123 Main St.,
Podunk Idaho the same person? Nope, father and son.

Are Jim Smith, 321 Oak Ave. #2, Hibbard, MT
and James Smith, 321 Oak, Hibbard MT
the same person? Maybe. Access won't recognize them as such though!

This really does require a USB interface - Using Someone's Brain. Even that
can fail, of course! What I'd recommend is checking some appropriate
combination of fields (lastname, firstname, phone say) in the data entry
form's BeforeUpdate event, looking them up in the form's recordsource, and
*warn* the user of a potential duplicate, giving them the choice to revert to
the existing record or add the new one.

John W. Vinson [MVP]