Prevent duplicate record from being written to a table

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Create on the table a unique index that holds all five fields.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
hmm, create an unique index over one of the (natural) candidate keys, instead of using the surrogate primary key over as only unique index.
SteveL13Author 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?
Gustav BrockCIOCommented:
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 LLCCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.