Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2019, 06:04 PM   #1
LinksAwakener
Newly Registered User
 
Join Date: Feb 2019
Posts: 12
Thanks: 2
Thanked 0 Times in 0 Posts
LinksAwakener is on a distinguished road
Database/Table/Field Validation?

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?

LinksAwakener is offline   Reply With Quote
Old 02-13-2019, 06:10 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Database/Table/Field Validation?

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 02-13-2019, 06:11 PM   #3
LinksAwakener
Newly Registered User
 
Join Date: Feb 2019
Posts: 12
Thanks: 2
Thanked 0 Times in 0 Posts
LinksAwakener is on a distinguished road
Re: Database/Table/Field Validation?

Quote:
Originally Posted by theDBguy View Post
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'

LinksAwakener is offline   Reply With Quote
Old 02-13-2019, 06:17 PM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Database/Table/Field Validation?

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 02-13-2019, 06:20 PM   #5
LinksAwakener
Newly Registered User
 
Join Date: Feb 2019
Posts: 12
Thanks: 2
Thanked 0 Times in 0 Posts
LinksAwakener is on a distinguished road
Re: Database/Table/Field Validation?

Quote:
Originally Posted by theDBguy View Post
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.
Attached Images
File Type: png Screen Shot 2019-02-13 at 6.18.38 PM.png (12.8 KB, 10 views)
LinksAwakener is offline   Reply With Quote
Old 02-13-2019, 06:25 PM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Database/Table/Field Validation?

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 02-13-2019 at 06:41 PM.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
LinksAwakener (02-13-2019)
Old 02-13-2019, 06:37 PM   #7
LinksAwakener
Newly Registered User
 
Join Date: Feb 2019
Posts: 12
Thanks: 2
Thanked 0 Times in 0 Posts
LinksAwakener is on a distinguished road
Re: Database/Table/Field Validation?

Quote:
Originally Posted by theDBguy View Post
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?

LinksAwakener is offline   Reply With Quote
Old 02-13-2019, 06:42 PM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Database/Table/Field Validation?

Yes, that's correct. It's called a multi-field index.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 02-14-2019, 12:09 PM   #9
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,993
Thanks: 3
Thanked 433 Times in 426 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Database/Table/Field Validation?

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



Attached Images
File Type: png Capture.PNG (14.0 KB, 1 views)
Cronk is offline   Reply With Quote
Old 02-14-2019, 12:24 PM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Database/Table/Field Validation?

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Field Validation Based on Field in Another Table diofree Tables 7 12-01-2015 12:12 PM
Validation rule to restrict input of one field based on other field in same table jobrien4 General 2 09-12-2011 09:10 AM
Validation Rule on Field in a Table mariaw Tables 9 04-12-2007 08:14 AM
Validation in Table Field MiAs Tables 2 08-12-2005 03:31 PM
table field validation gogaak Tables 1 04-20-2005 08:24 AM




All times are GMT -8. The time now is 05:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World