By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,300 Members | 1,867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

Validation rule question - avoiding duplicates of multiple fields

P: 9
Hi guys,
So I'm editing an existing database (Access 2003), and I need a bit of help in making a validation rule.

My table has many fields, 3 of which are indexed (location, number & revision)

Each of these 3 fields can have duplicates (Same location can exist for multiple items, same number can exist for multiple items, and same revision can exist for multiple items).

However, all 3 fields cannot be the same in multiple records (Ex.. There cannot be multiple items from location X, with Number 1, and revision A).

Is there a way to create a validation rule for the 3 fields in question to enforce this? If not, is there a way to code something like this into VBA?

Thanks!!

Ian
Jun 18 '08 #1
Share this Question
Share on Google+
8 Replies


100+
P: 167
Hi Ian,

Looks like the Primary key issue to me.

If you don't want these fields to have the same values twice you should make a Primary key composed of these three fields.
..or maybe add them to the existing PK if you have one???

H.
Jun 18 '08 #2

FishVal
Expert 2.5K+
P: 2,653
You may create multifield index to enforce unique combinations only.

Regards,
Fish
Jun 18 '08 #3

100+
P: 167
Hi!

I just searched help for "index" (multiple-field index), tried it out and got the result you expect. Here is explanation from help:

1. Open the table in Design view
2. Click Indexes on the toolbar.
3. In the first blank row in the Index Name column, type a name for the index ). You can name the index after one of the index fields, or use another name.

4. In the Field Name column, click the arrow and select the first field for the index.
5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

This solution is better than what I suggested in the first place. I learned something new too :-)

Regards,
H.
Jun 18 '08 #4

P: 9
Guys,
Thanks for the help, but it still isn't working for me.

In table design view, I click the indexes button on the table design toolbar, and I added location, number and revision as indexes. Near the bottom of the box, I can click either "primary" "unique" and "ignore nulls". The problem is I can only make them unique individually, instead of unique combinations of the three.

I've tried selecting all of them at once in the indexes box, but if more than one index is selected, the "primary" "unique" and "ignore nulls" options disappear.

Any thoughts??

And with regards to the triple primary key idea, this almost work, except sometimes revision is left null, and primary keys cannot be null :(
Jun 18 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Access "Indexes..." dialog is not an intuitive eye candy. ;)

....
5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.
....
Regards,
Fish
Jun 18 '08 #6

P: 9
Thanks guys!
The reason it wasn't working for me is that the table I'm using is already full of duplicates, and I need to get of them before I make my indexes.

Cheers!
Jun 18 '08 #7

P: 1
@hjozinovic

Hi H

I had a similar problem and fixed it using this great solution. However, when I now try and submit an update to a table I get the error "changes were not successful because they would create a duplicate", which I would expect.

How can I turn off the error message so it doesn't appear?

Iann
Feb 25 '19 #8

twinnyfo
Expert Mod 2.5K+
P: 3,284
Iann,

You can't turn off the warning. You need to change the table so that it allows duplicates.
Feb 25 '19 #9

Post your reply

Sign in to post your reply or Sign up for a free account.