Append Query Error -Key violation

[Federico]

Hi all,
I need some help to figure out this problem.

I want to populate a table, let's say [PCR_Run_Details] with records from
[PCR_Run_DetailsClone] (I cloned the table to simplify this testing)

There are both used as subforms of frmPCR_Run.

I've added a checkbox field in [PCR_Run_DetailsClone] for the user to
friendly select that particular record. The idea is that a button command
triggers an AppendQuery to insert the selected records into
[PCR_Run_Details]. The problem is that I can't get the query to work since I
have a "Key violation" message.

[PCR_Run_DetailsClone]
--------------------------
IDRunDetail (Autonumber, PrimaryKey)
IDrun
Aliquot
OrigVialBC

Query:
INSERT INTO PCR_Run_Details ( IDRunDetail, IDRun, OrigVialBC, Aliquot )
SELECT PCR_Run_DetailsClone.IDRunDetail, PCR_Run_DetailsClone.IDRun,
PCR_Run_DetailsClone.OrigVialBC, PCR_Run_DetailsClone.Aliquot
FROM PCR_Run_DetailsClone;

Here I'm not even filtering by checkbox selection. When running the query,
says" you're about to add 2 records ...", which is fine, but then it reports
2 key validation error.

Any idea why this occurs? Any suggestion for copying records from one table
to another one?

I'll appreciate your help!

 

[Douglas J. Steele]

My guess is that you've already used the values of IDRunDetail in the second
table. Since IDRunDetail is an Autonumber field, remove it from the INSERT,
so that Access assigns its value:

INSERT INTO PCR_Run_Details (IDRun, OrigVialBC, Aliquot )
SELECT PCR_Run_DetailsClone.IDRun,
PCR_Run_DetailsClone.OrigVialBC, PCR_Run_DetailsClone.Aliquot
FROM PCR_Run_DetailsClone;

 

[Federico]

Thanks Doug!

I've tried removing that field but still didn't work.
Then I realized that PCR_Run_Details.IDRun was part of a Relationship (with
referential integrity) with the parent form PCR_Run. Removing the integrity
allows the query to populate the table, but it doesn't sound good to remove
the integrity for obvious reasons. Is that a limitation of the AppendQuery
approach?

Federico-

My guess is that you've already used the values of IDRunDetail in the second
table. Since IDRunDetail is an Autonumber field, remove it from the INSERT,
so that Access assigns its value:

INSERT INTO PCR_Run_Details (IDRun, OrigVialBC, Aliquot )
SELECT PCR_Run_DetailsClone.IDRun,
PCR_Run_DetailsClone.OrigVialBC, PCR_Run_DetailsClone.Aliquot
FROM PCR_Run_DetailsClone;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
I need some help to figure out this problem.

I want to populate a table, let's say [PCR_Run_Details] with records from
[PCR_Run_DetailsClone] (I cloned the table to simplify this testing)

There are both used as subforms of frmPCR_Run.

I've added a checkbox field in [PCR_Run_DetailsClone] for the user to
friendly select that particular record. The idea is that a button command
triggers an AppendQuery to insert the selected records into
[PCR_Run_Details]. The problem is that I can't get the query to work since
I
have a "Key violation" message.

[PCR_Run_DetailsClone]
--------------------------
IDRunDetail (Autonumber, PrimaryKey)
IDrun
Aliquot
OrigVialBC

Query:
INSERT INTO PCR_Run_Details ( IDRunDetail, IDRun, OrigVialBC, Aliquot )
SELECT PCR_Run_DetailsClone.IDRunDetail, PCR_Run_DetailsClone.IDRun,
PCR_Run_DetailsClone.OrigVialBC, PCR_Run_DetailsClone.Aliquot
FROM PCR_Run_DetailsClone;

Here I'm not even filtering by checkbox selection. When running the query,
says" you're about to add 2 records ...", which is fine, but then it
reports
2 key validation error.

Any idea why this occurs? Any suggestion for copying records from one
table
to another one?

I'll appreciate your help!

 

[Douglas J. Steele]

I'd say it's a limitation of using an Autonumber as the primary key. If you
used a natural key, you'd probably be okay (although you'd have to run a
query to ensure that the necessary records existed in the parent table).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug!

I've tried removing that field but still didn't work.
Then I realized that PCR_Run_Details.IDRun was part of a Relationship
(with
referential integrity) with the parent form PCR_Run. Removing the
integrity
allows the query to populate the table, but it doesn't sound good to
remove
the integrity for obvious reasons. Is that a limitation of the AppendQuery
approach?

Federico-

My guess is that you've already used the values of IDRunDetail in the
second
table. Since IDRunDetail is an Autonumber field, remove it from the
INSERT,
so that Access assigns its value:

INSERT INTO PCR_Run_Details (IDRun, OrigVialBC, Aliquot )
SELECT PCR_Run_DetailsClone.IDRun,
PCR_Run_DetailsClone.OrigVialBC, PCR_Run_DetailsClone.Aliquot
FROM PCR_Run_DetailsClone;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
I need some help to figure out this problem.

I want to populate a table, let's say [PCR_Run_Details] with records
from
[PCR_Run_DetailsClone] (I cloned the table to simplify this testing)

There are both used as subforms of frmPCR_Run.

I've added a checkbox field in [PCR_Run_DetailsClone] for the user to
friendly select that particular record. The idea is that a button
command
triggers an AppendQuery to insert the selected records into
[PCR_Run_Details]. The problem is that I can't get the query to work
since
I
have a "Key violation" message.

[PCR_Run_DetailsClone]
--------------------------
IDRunDetail (Autonumber, PrimaryKey)
IDrun
Aliquot
OrigVialBC

Query:
INSERT INTO PCR_Run_Details ( IDRunDetail, IDRun, OrigVialBC, Aliquot )
SELECT PCR_Run_DetailsClone.IDRunDetail, PCR_Run_DetailsClone.IDRun,
PCR_Run_DetailsClone.OrigVialBC, PCR_Run_DetailsClone.Aliquot
FROM PCR_Run_DetailsClone;

Here I'm not even filtering by checkbox selection. When running the
query,
says" you're about to add 2 records ...", which is fine, but then it
reports
2 key validation error.

Any idea why this occurs? Any suggestion for copying records from one
table
to another one?

I'll appreciate your help!