Validation rule question - avoiding duplicates of multiple fields

[preeny8]

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

[hjozinovic]

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.

[FishVal]

You may create multifield index to enforce unique combinations only.

Regards,
Fish

[hjozinovic]

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.

[preeny8]

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 :(

[FishVal]

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

[preeny8]

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!

[IannWard]

Error Message

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.

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

[twinnyfo]

Iann,

You can't turn off the warning. You need to change the table so that it allows duplicates.