key violation on append query

[Guest]

I am including an append query to copy the Unique Key from a form to another
table for reference in certain circumstances. I have an append query that
causes a key violation and I have no idea why.
The clientID is the Unique key to the main table and is just a number field
to the Tbl_ICContacts

INSERT INTO Tbl_ICContacts ( ClientID )
SELECT Main.ClientID
FROM Main
WHERE (((Main.ICNumber)=[forms].[frm_main].[ICNumber]));

 

[tina]

suggest you check the ClientID field in Tbl_ICContacts; even without a
primary key designation, the field may still have a unique index on it,
which will show up in the field properties as Indexed: Yes (No Duplicates).
if that's the setting, change it to Yes (Duplicates OK), and that should
take care of it.

hth

 

[John Spencer]

Are there other fields in Tbl_ICContacts that are related to other
tables? That can cause a problem.

For instance, if you have a related number field and have accidentally
set it to default to zero and have no zero record in the "parent" table
that will generate a key violation error.

 

[Guest]

thanks John & Tina. Problem solved.

Are there other fields in Tbl_ICContacts that are related to other
tables? That can cause a problem.

For instance, if you have a related number field and have accidentally
set it to default to zero and have no zero record in the "parent" table
that will generate a key violation error.

---
John Spencer
Access MVP 2001-2005, 2007

I am including an append query to copy the Unique Key from a form to another
table for reference in certain circumstances. I have an append query that
causes a key violation and I have no idea why.
The clientID is the Unique key to the main table and is just a number field
to the Tbl_ICContacts

INSERT INTO Tbl_ICContacts ( ClientID )
SELECT Main.ClientID
FROM Main
WHERE (((Main.ICNumber)=[forms].[frm_main].[ICNumber]));