Message Box to Display if Duplicate Values are entered in Field

[bethabernathy]

Hi - I have a database with a table with one field "CustomerID" where the properties have been set at Indexed to: Yes (No Duplicates). While this does work, if you go to add a duplicate record on a form it simply does not save the record. Is there a way to set it so a message box pops up if a duplicate value is entered to indicate that to the user?

Thanks, Beth beth@integratedresourcemgmt.com

 

[phinoppix]

Can you state more conditions like are you're text fields bound or not? It will be more helpful if you post the code where you think there is an anomally.

Just a try, though. Is there an On Error statement within that procedure where you were suppose to expect to occur an error? Normally, in my apps, an Error -2147217887 (80040e21) should occur. To resolve the duplicate problem, either trap the error or, search the record if it's already existing before updating your table.

 

[bethabernathy]

The "customerID" field on the form is just a regular text field. All I want to do is to find some code to give a user a message if they have added a duplicate value. On exit of the form, if a duplicate value is entered the data is just not saved. I have seen this many times with access.

Thanks, Beth beth@integratedresourcemgmt.com

 

[Scheur]

You can also query the database first before you save the input to the database. Search for a record with the given CustemerID and use Recordcount to find out if the record exists. If recordcount is 1, you can raise an error. Otherwise you can launch an insert query to save the input.

 

[kbuc6]

If you use ADO and do an insert, you will receive an error stating that it cannot insert a duplicate value.