We help IT Professionals succeed at work.

Prevent duplicate record from being written to a table

SteveL13
SteveL13 asked
on
101 Views
Last Modified: 2018-08-24
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

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
ste5anSenior Developer
CERTIFIED EXPERT

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?
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.