Duplicate detection

[Guest]

To help you understand my problem and assist me, I will give an actual
illustration. I have a table that consists of CustomerPhone, Name, Address
etc. The customer Phone is a PK. I have created a form with unbound text
fields. Once a user enters the customer phone and clicks on search it will
bring up existing record from the table. If the record doesn't exist they
will have an option to add that phone number and other details to the
database. But if the record exist then it will pull up the record. Once the
record is displayed the end user can make any changes they want to the record
and click on update. Since this form has unbound text controls, On click
event I run an update query to update the record. Now if a user wants to
change the phone number (the primary key) and if they enter a phone number
that already exists in the database, the system should not update the record
and give error message indicating the record already exists. Below is an
example

Customer Phone
ABC 1234
XYZ 1111

If a user pulls up XYZ record and changes its phone number from 1111 to 1234
the system should give an error. Any help you can provide would be greatly
appreciated.

PS. The key thing to remember is the form consists of unbound controls.

 

[Douglas J Steele]

If Phone Number is the primary key, they will not be able to change it to a
duplicate value: primary keys have unique indexes on them. I take it you
want to intercept before you get to the point of trying to make the change
in the database.

In the Phone Number text box's AfterUpdate event, do a query of the database
to see whether that value already exists. You can use the DLookup or DCount
function if you like, or you can open a recordset.

However, is Phone Number really a good choice for a primary key? Are you
positive you'll never have two customers with the same phone number?