key violation error

H

Heather Vernon

I keep running into a key violation error while appending data. Here's my
SQL statement:

INSERT INTO tblCollectionDetail
SELECT tblSelections.AnimalID As CollectionID, tblChemicalItem.ChemicalID As
RawID
FROM tblSelections, tblChemicalItem
WHERE (((tblChemicalItem.ChemicalID)=59) AND ((Exists (SELECT * FROM
tblCollectionDetail INNER JOIN tblSelections ON
tblCollectionDetail.CollectionID = tblSelections.AnimalId WHERE
tblCollectionDetail.RawID = 59))=False) AND
((tblSelections.FormID)="frmChemEntry_Subform1"));
 
K

Ken Snell [MVP]

Key violation means that the query is trying to create a record where one or
more of the values violates an index restriction -- primary key's value is
being duplicated, another index that doesn't allow duplicates is being given
a duplicate value, etc. It also may result if you try to insert a record
where the foreign key is getting a value that does not exist in the "parent"
table.

We don't know what the data are, so this is the best we can suggest at the
moment.
 
H

Heather Vernon

The primary key of tblCollectionDetail is CollectionDetailID, which is an
autonumber field; I'm not augmenting it.
The foreign keys of tblCollection and tblChemicalItem do exist in the
"parent" tables; I double-checked that this morning.
 
K

Ken Snell [MVP]

Ah, but your query is telling ACCESS to write a value into the autonumber
field because you didn't specify the "target fields" that are to get the
data. Try this generic example (change names as needed):

INSERT INTO tblCollectionDetail ( FieldNameForCollectionID,
FieldNameForRawID )
SELECT tblSelections.AnimalID As CollectionID, tblChemicalItem.ChemicalID As
RawID
FROM tblSelections, tblChemicalItem
WHERE (((tblChemicalItem.ChemicalID)=59) AND ((Exists (SELECT * FROM
tblCollectionDetail INNER JOIN tblSelections ON
tblCollectionDetail.CollectionID = tblSelections.AnimalId WHERE
tblCollectionDetail.RawID = 59))=False) AND
((tblSelections.FormID)="frmChemEntry_Subform1"));

Your query was telling ACCESS to begin filling the fields from first to last
because you didn't specify the target fields, and I am guessing that the
autonumber field is the first field in the table.
 
H

Heather Vernon

Thanks for your help so far Ken. Here is my new query:
INSERT INTO tblCollectionDetail(CollectionID, RawID) SELECT
tblSelections.AnimalID As CollectionID, tblChemicalItem.ChemicalID As RawID
FROM tblSelections, tblChemicalItem WHERE NOT EXISTS (SELECT * FROM
tblCollectionDetail INNER JOIN tblSelections ON
tblCollectionDetail.CollectionID = tblSelections.AnimalId WHERE
tblCollectionDetail.RawID = 59) AND tblSelections.FormID =
"frmChemEntry_Subform1" AND tblChemicalItem.ChemicalID = 59;

But I'm still getting key violations for all 15 records I'm trying to append.
 
K

Ken Snell [MVP]

If you run just the SELECT query portion of the query, how many records do
you get? Is it the correct number? Are they the records that you expect?

SELECT
tblSelections.AnimalID As CollectionID, tblChemicalItem.ChemicalID As RawID
FROM tblSelections, tblChemicalItem WHERE NOT EXISTS (SELECT * FROM
tblCollectionDetail INNER JOIN tblSelections ON
tblCollectionDetail.CollectionID = tblSelections.AnimalId WHERE
tblCollectionDetail.RawID = 59) AND tblSelections.FormID =
"frmChemEntry_Subform1" AND tblChemicalItem.ChemicalID = 59;

I ask because you're using a Cartesian query as the join for the two tables,
and that can create multiple records, more than you expected.
 
H

Heather Vernon

I did intend to create a Cartesian query, because sometimes I'll want to add
multiple ChemicalID's per CollectionID to my table (controlled by the third
WHERE statement). The select statement creates 15 records with 2 fields:
CollectionID & RawID. The amount and values are what I wanted.
 
K

Ken Snell [MVP]

Then I would check the indices in the tblCollectionDetail table -- is there
an index there that would prevent any of the new data being added (such as
an index composed of more than one field)?

Is it possible for a Null value to be generated and trying to go into a
field that is not allowed to be empty (field is Required or field is part of
an index)?

I realize that this error can be elusive to track down, but invariably it
does have a viable source/cause. Sometimes, I resort to printing out the
table's current data and the data I'm trying to append, and going through it
one record at a time. (It'll be *so* obvious when you finally identify it <
g >.)
--

Ken Snell
<MS ACCESS MVP>
 
H

Heather Vernon

Ahah. I had a third index (ChemicalID) that had a default value of 0, and 0
does not exists as an ID in that table. Awfully obvious now :). Thanks again
for your help.
 
Top