Duplicate values

[Thorson]

I currently have a table set up with 6 fields. The identification of the
records is the ear tag field. This field can have duplicate ear tags
therefore in design view I have the indexed field set to Yes (Duplicates
ok). None of the other fields have restrictions on duplicates. Currently
none of the fields are set as the primary key. I may add a autonumber field
as the primary key after I get this problem fixed.

Even though I have it set this way I still get an error when I try to enter
2 records for the same ear tag. The error states "The changes you requested
to the table were not successful because they would create duplicate values
in the index, primary key, or relationship. Change the data in the field or
fields that contain duplicate data, remove the index, or redefine the index
to permit duplicate entries and try again."

Is there anything else I should check that might be preventing the duplicate
ear tag record? I can't figure out what is causing this.

Thanks,

 

[Jerry Whittle]

Open up the table in design view. Got to View, Indexes. Make sure that Access
didn't sneak in an index on you.

Next go to the Relationship Window and see if there are any relationships
defined using that table with Referential Integrity enabled.

Next are you using lookup tables inside this table or is this table used as
a lookup for another?

The last thing to try, after making a complete backup of the database, is a
Compact and Repair. The database may be corrupted.

 

[Thorson]

The problem was Referential Integrity. It was enabled with a master listing
of the Ear Tag numbers. When I unchecked referential integrity I could enter
in duplicate ear tags.

From my memory I have other tables set up with referential integrity but
still allow duplicate records, maybe I am remembering wrong...

I do want referential integrity set up, I don't want users accidentally
entering in ear tags that aren't even in the master listing of the database.
Is there anyway around this situation?

 

[Jerry Whittle]

Which side was the infinity symbol on? Possibly you had the join backwards
from what should have been the child table to the parent table.

 

[Thorson]

Thank you so much! When I tried re-forming the relationship I realized the
symbols were different than before, this time it was a 1 to infinity
relationship last time it was 1 to 1. I didn't realize that would even
affect it, but obviously that makes sense.

Thank you for your help!