Database/Table/Field Validation?

[LinksAwakener]

Ooookay. So, I'm kind of at a loss here.

I have narrowed down my issue to some validation rule or something, but I cannot see any rule in place at all. Here's what happens.

Procedurally, I'm trying to duplicate a record on a table. Every time I try, I get a 'duplicate record' error, even though the ID (key field) is unique. This table has many relationships but I don't think that's the cause of the issue. I got fed up, and decided to just manually duplicate the record to see if I get the same error. I tried entering in one field at a time, and it came down to one particular field, let's call it SequenceNumber. Every other field can be duplicated just fine (ID aside) but this field is the one that Access complains about.

So, you'd think I would have found the answer. But no, there doesn't appear to be any validation rules on this field. Or the table. Unless they're invisible? :banghead:

 

[theDBguy]

Hi. Go to the table's design view and select the "SequenceNumber" field. Then, in the Field Properties window at the bottom, what do you see in the "Indexed" property?

 

[LinksAwakener]

Hi. Go to the table's design view and select the "SequenceNumber" field. Then, in the Field Properties window at the bottom, what do you see in the "Indexed" property?

Indexed is marked 'No'

 

[theDBguy]

Was the error/complaint specifically about a "duplicate" record? Try creating a new record and only enter a duplicate value in this one field, leave all the rest empty. Does it still error out because of the duplicate value? Or something else this time?

 

[LinksAwakener]

Was the error/complaint specifically about a "duplicate" record? Try creating a new record and only enter a duplicate value in this one field, leave all the rest empty. Does it still error out because of the duplicate value? Or something else this time?

I think so. Here's a screenshot of the exact message.

 

[theDBguy]

Hi. The error message gives us at least three locations to check: (1) the Index, (2) Primary Key, or (3) Relationship. Do you see anything else in the Indexes? While on design view of the table, click on the "Indexes" button on the Design Ribbon. Look in the "Field Name" column to see the fields involved in any index.

 

[LinksAwakener]

Hi. The error message gives us at least three location to check: (1) the Index, (2) Primary Key, or (3) Relationship. Do you see anything else in the Indexes? While on design view of the table, click on the "Indexes" button on the Design Ribbon. Look in the "Field Name" column to see the fields involved in any index.

EUREKA! This is what I was looking for, THANKS!!

Since this is the first time I've seen this screen, could I ask a few questions? (Please keep in mind, I didn't create this Db and don't purport to be any type of expert on Access).

This layout doesn't look particularly intuitive to me. I have 5 things listed under the column Index Name and 7 fields listed under Field Name. Can I assume if a Field Name is next to a blank Index Name, that it's associated with the Index Name that appears above that blank cell?

 

[theDBguy]

Yes, that's correct. It's called a multi-field index.

 

[Cronk]

And to give an example, this table can have a record for a Yellow Ball or a Yellow Cube but not two records for Red Prisms because there is a unique index on the multi field ObjectShapeColor

 

[theDBguy]

Yup! You got it. You can also have a Yellow and Red Balls or Yellow and Red Prisms. Just not two Red Prisms or two Yellow Balls.