key violation on append query

I

Ian

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]));
 
T

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
 
J

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

Ian

thanks John & Tina. Problem solved.

John Spencer said:
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]));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top