Dulpicate Record

[Thorson]

I currently have a table (tblBreeding) set up with 4 fields: "EarTag" "Date"
"Bull" "UnitLocation"

The same animal may be bred to the different or same bull more than once,
however the table is not allowing me to enter in the same animal "EarTag"
more than once; when I try to a box pops up:

"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."

The field "EarTag" indexed property is set to "Yes (Duplicates OK)". The
table, tblBreeding, is also set up in a relationship with another table
(tblEarTag). The join properties are set up so the "EarTag" record must
first be in tblEarTag before tblBreeding, Enforce Referential Integrity and
Cascade Update Related Fields are both checked.

I don't know what I have set up wrong, any suggestions?

 

[Allen Browne]

So you have 2 tables:
- tblEarTag has one record for each bull, with the EarTag field marked as
primary key (or at least Indexed with no duplicates.)

- tblBreeding records each breeding event, so it has an EarTag foreign key
that relates to tblEarTag.EarTag.

Open tblBreeding in design view, and set the Indexed property of the EarTag
field to No. Because you defined a relationship with referential integrity
enforced, Access has created a hidden index on this field anyway, so you
don't need another one.

Now, in what context does this error occur? Is this a query that uses both
tables (or a form based on a 2-table query)? If so, the query may have the
EarTag property from the wrong table, so it's trying to add a record to the
wrong table. Fix the query, so that you are only adding/editing fields from
tblBreeding (even if you display fields from the other table.)

There is also a bug in Access where it wrongly applies the Default Value to
the field in the primary table in such a query. Removing the default value
from all fields in tblEarTag would solve that.

BTW, Date is a reserved word, so consider changing the field name to
BreedingDate or something. Otherwise it can cause you grief: in some
contexts, Access misunderstands it as the system date. Here's a list of the
words to avoid:
http://allenbrowne.com/AppIssueBadWord.html

 

[Thorson]

yes, your assumptions are correct, tblBreeding is the table I am having
problems with not tblEarTag, So I changed the index property for the EarTag
field in tblBreeding "no"

The error is occuring when I am simply entering a new record into
tblBreeding. It will not allow me to enter an eartag twice in the table,
even though I have the Index property field set to "no". The only query
involved is a lookup query for the bull name, even if I leave this field
blank the table does not allow me to enter 2 different records with the same
EarTag.

There are only 3 fields in tblBreeding, none of the fields have a default
value.

Thank you for the heads up on the "date" name. I changed it.

The table is still not letting me enter multiple records with the same
number in the EarTag field...

 

[Allen Browne]

Something's wrong: it should work the way you expect (allowing multiple
breedings per bull.)

Make a back up if you wish, and then open tblBreeding in design view. In the
lower pane of table design, change the Default Control property (on the
Lookup tab) from combo to text box. Save the change. Does it let you enter a
record directly into the table now?

If it doesn't, still in table design, click the Indexes box on the ribbon.
Something is indexed with no duplicates.

 

[Thorson]

I tried those ideas, but they didn't work.

I looked at my relationships between tblEarTag and tblBreeding and removed
the Unchecked "Enforce Referential Integrity" this solved my problem.
However now EarTags can be entered in tblBreeding that are not present in
tblEarTag, which is not what I want. Any suggestions?

Thank you

--
Thorson

Something's wrong: it should work the way you expect (allowing multiple
breedings per bull.)

Make a back up if you wish, and then open tblBreeding in design view. In the
lower pane of table design, change the Default Control property (on the
Lookup tab) from combo to text box. Save the change. Does it let you enter a
record directly into the table now?

If it doesn't, still in table design, click the Indexes box on the ribbon.
Something is indexed with no duplicates.

 

[Allen Browne]

Your relationships were backwards, or one-to-one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried those ideas, but they didn't work.

I looked at my relationships between tblEarTag and tblBreeding and removed
the Unchecked "Enforce Referential Integrity" this solved my problem.
However now EarTags can be entered in tblBreeding that are not present in
tblEarTag, which is not what I want. Any suggestions?

Thank you

 

[Thorson]

Thank you! That was an easy fix.
--
Thorson

Your relationships were backwards, or one-to-one.