Database/Table/Field Validation? (1 Viewer)

LinksAwakener

Registered User.
Local time
Today, 03:31
Joined
Feb 6, 2019
Messages
12
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

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 03:31
Joined
Feb 6, 2019
Messages
12
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

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 03:31
Joined
Feb 6, 2019
Messages
12
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.
 

Attachments

  • Screen Shot 2019-02-13 at 6.18.38 PM.png
    Screen Shot 2019-02-13 at 6.18.38 PM.png
    12.8 KB · Views: 48

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
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.
 
Last edited:

LinksAwakener

Registered User.
Local time
Today, 03:31
Joined
Feb 6, 2019
Messages
12
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

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
Yes, that's correct. It's called a multi-field index.
 

Cronk

Registered User.
Local time
Today, 21:31
Joined
Jul 4, 2013
Messages
2,770
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



 

Attachments

  • Capture.PNG
    Capture.PNG
    14 KB · Views: 47

theDBguy

I’m here to help
Staff member
Local time
Today, 03:31
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom