Microsoft Access Duplicate Values on Multiple Fields
Preventing Duplicate Values Across Multiple Fields with Two Indices
Q: How can I prevent the user from entering a record with
duplicate values across MULTIPLE fields. For example, if I have
CustomerID and PersonID in a table, the user could duplicate CustomerID
or PersonID many times, but the combination of the TWO fields has to
always be unique.
A: If you have a multi-field
index and you set the fields individually to NO DUPLICATES then you
won't be able to have a duplicate value in EITHER field.
In order to make it so that the COMBINATION of indexes is unique, then
you can do that by setting both fields to be the PRIMARY KEY (with the
little key button) but changing their index type to YES, DUPLICATE
VALUES OK. Access will make sure that the combination of both records is
unique or it will give you an error message.
If you want to be able to control what happens when the user violates
the rules, then you need to do that with some VBA code. I would put it
in the BeforeUpdate event of a form (before the record is actually
saved) so that you can display a warning message and cancel the event.
Here is a sample database file that shows you the exact code:
SAMPLE
FILE - ZIP file, 13k, Access XP (2002) format
|
