Prevent duplicate record from being written to a table

SteveL13
SteveL13 used Ask the Experts™
on
I have a datasheet form that has 5 fields.  If the user double clicks the record selector the record is written to a separate table.  But if the user d/c again and that record has already been written to the table I don't want to allow it if it is an exact duplicate (all 5 fields).  How can I prevent this from being allowed?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Create on the table a unique index that holds all five fields.
ste5anSenior Developer

Commented:
hmm, create an unique index over one of the (natural) candidate keys, instead of using the surrogate primary key over as only unique index.

Author

Commented:
Gustav, I think I did what you suggested.  I went to the design of the table, indexes, and added all 5 fields and made them Unique = Yes.  It worked.  Did I do exactly what you suggested?

Also, is there a way to let the user know the record couldn't be added because it already exists?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, that's it.
But to play nicely, you'll have catch the error when trying to insert the record and, if so, pop a normal messagebox to inform the user politely.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
actually, you will need to add a composite index which has a single name and has all 5 fields assigned to it. I might look like:
composite indexthose other unique indexes you created on that table will prevent it from having two records with the same value in any one of those fields, so get rid of those individual "unique" indices.  If you still need that column indexed, then simply change Unique to No.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial