Link to home
Start Free TrialLog in
Avatar of jim1102
jim1102

asked on

I am getting a key violation error

I have a data base with about 180 records that have multiple yes no boxes for each record.  When I try to append information to a table based on the boxes it will not let me go above record 110 in appending the records.  ID's below 110 work find.

The code seems to be fine and there are no violation keys set.

What is happening and how can I fix this.
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Hi,
can you please provide a lot of more information? Without the necessary basic no one would be able to help you.
What database (NoSQL, Access, SQL Server??? which version!), what software / what code, how does the table structure look alike, what should your code do ...
Thanks.
Rainer
I'm also puzzled about the current state of the database.
You state you currently have 180 records in the database and you also state you can't append after record 110.  
How did you get this other 70 records into the database?  
Are you talking of inserting into gaps in a numbering or overwriting existing records rather than appending?
Avatar of jim1102
jim1102

ASKER

I have a append query with the following code.

SELECT ClientID, ClientName, IIf(IsNull([1040Ind]),"","04/15/15") AS DueDate, IIf(IsNull([1040Ind]),"","1040 Ind Return") AS TaskName
FROM tblClientList1
WHERE ((( [1040Ind])=Yes))
UNION ALL
SELECT ClientID, ClientName,  IIf(IsNull([OrganizerRequested]),"","01/15/15") AS DueDate, IIf(IsNull([OrganizerRequested]),"","'Organizer") AS TaskName
FROM tblClientList1
WHERE ((([OrganizerRequested])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([1041Trust]),"","' ") AS DueDate, IIf(IsNull([1041Trust]),"","1041 Trust Return") AS TaskName
FROM tblClientList1
WHERE ((([1041Trust])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([1099Req]),"","'01/31/15") AS DueDate, IIf(IsNull([1099Req]),"","1099") AS TaskName
FROM tblClientList1
WHERE ((([1099Req])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([W2Reg]),"","'01/31/15") AS DueDate, IIf(IsNull([W2Reg]),"","W2") AS TaskName
FROM tblClientList1
WHERE ((([W2reg])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([990NonProf]),"","'") AS DueDate, IIf(IsNull([990NonProf]),"","990") AS TaskName
FROM tblClientList1
WHERE ((([990NonProf])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([709Gift]),"","'04/15/15") AS DueDate, IIf(IsNull([709Gift]),"","790 Gift Tax") AS TaskName
FROM tblClientList1
WHERE ((([709Gift])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([PersonalPropTax]),"","'01/31/15") AS DueDate, IIf(IsNull([PersonalPropTax]),"","Personal Property Tax") AS TaskName
FROM tblClientList1
WHERE ((([PersonalPropTax])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([BusLicTax]),"","' ") AS DueDate, IIf(IsNull([BusLicTax]),"","Business License Tax") AS TaskName
FROM tblClientList1
WHERE ((([BusLicTax])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([1065Partship]),"","'04/15/15") AS DueDate, IIf(IsNull([1065Partship]),"","1065 Partnership") AS TaskName
FROM tblClientList1
WHERE ((([1065Partship])=Yes))
UNION ALL
SELECT ClientID, ClientName, IIf(IsNull([1120Corp]),"","'01/31/15") AS DueDate, IIf(IsNull([1120Corp]),""," 1120 Corp ") AS TaskName
FROM tblClientList1
WHERE ((([1120Corp])=Yes))
UNION ALL SELECT ClientID, ClientName, IIf(IsNull([1120Scorp]),"","' ") AS DueDate, IIf(IsNull([1120Scorp]),""," 1120 S Corp ") AS TaskName
FROM tblClientList1
WHERE ((([1120Scorp])=Yes

This populates the query with all the information correctly

I have the follow append code that is appending the union qurery results into a blank table that only has the structure .

INSERT INTO tblDDM ( ClientName, TaskName, DueDate )
SELECT [1040Ind].ClientID, [1040Ind].TaskName, [1040Ind].DueDate
FROM 1040Ind;

When the ClientID exceeds 110 those records are not appended.
Do you have a unique index on the destination table that is preventing duplicate rows?
>>I have the follow append code that is appending the union qurery results into a blank table that only has the structure .

Please give the structure of the table - you appear to have forgotten to do that.


ps.  Not relevant to the question, don't answer this.  Why do you have so many UNION ALL, your query looks at first glance to be very inefficient, you can (usually) have WHERE x OR y OR z.......
Usually a key violation error happens when a new record being appended has the same value for the key field as an existing record.  In a few cases I have seen an error in creating an AutoNumber value, where the table reuses a value from a previous record, resulting in this error.  You can test this in the interface -- start typing in a new record, and check the value of the key field, if it is an AutoNumber field.  When this happens, you need to recreate the table.  I have an Access Archon article on this topic:

http://www.helenfeddema.com/Files/accarch165.zip

I also think there are a lot of union queries here.  Most likely this SQL could be greatly simplified.
>>there are no violation keys set.

>>INSERT INTO tblDDM
Unless you tell what the fields in that table, tblDDM, are (definition and indexing) we won't be able to help.  

Everything points to you attempting to add a duplicate to a field defined as unique BUT you also seem to say with that first statement exert that there aren't any fields defined that way in the table.
Avatar of jim1102

ASKER

I did something not sure what and it si now working.  Thanks for all the help.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.