asked on

Key violation for appending a record

I have a macro that is trying to execute an append query to a table with an autonumbered ID field (which is my key field) but it keeps failing. I have all sorts of other append queries to the same table with no issues, so I am completely baffled.

The way I have this structured is that I choose records from my table (tbl_prepare_financials) and then attempt to duplicate those records. I am NOT appending the key field as it is my assumption that the autonumbering will occur when the records are appended. Is this wrong thinking? I even tried making the select activity ONE query, and then the appned query is based on the select query, but still keep getting 7 key violations. If I "Un-key" the autonumber field, it works but then creates duplicate IDs.

Any suggestions?

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

" Is this wrong thinking? "
No. Do you have any other Unique Indexes that you have created on one or more fields - or a multi-field index?

Any required fields ?

mx

jwandmrsquared

ASKER

The SQL for the second-query:
INSERT INTO tbl_Finance_prepare_budgets_forecasts ( [Finance Contract ID], [Finance Tool Type], [Date of Item], Current_Active, [Effective Month], [Effective Year], Cost, [Forecast Type], Notes, [GA Code], TimeDateChanged, PersonwhoChanged, CashWeek, CashStatus, Check_date, PROD_ID, Total_type, Subtype, pmt_id )
SELECT qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Finance Contract ID], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Finance Tool Type], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Date of Item], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Current_Active, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Effective Month], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Effective Year], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Cost, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Forecast Type], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Notes, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[GA Code], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr3, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr4, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.CashWeek, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.CashStatus, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Check_date, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.PROD_ID, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Total_type, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr1, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr2
FROM qry_upd_cash_frcst_upd_sum_line_prtyr_step0;

I have verified field types on all of the above; what is NOT being appended is the ID field (the indexed/no duplicates one) as I thought the system would fill those in for me since it is an autonumber field.

jwandmrsquared

ASKER

OH, and I checked all other fields...none are required and all that are indexed are "duplicates OK"

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"what is NOT being appended is the ID field (the indexed/no duplicates one) as I thought the system would fill those in for me since it is an autonumber field."
Yes ... the system will do that. So, you are saying that field is NOT in the query above? What is Finance Contract ID ?

mx

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

Compact and Repair can't hurt, may work, but it's a long shot.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Exactly. Can't Hurt.

mx

Lambert Heenan

Compact and repair is the usual route to fix problems with autonumber seeds.

jwandmrsquared

ASKER

DGMG comment identified the problem perfectly, and compact/repair fixed it. Once again, I am kept from many moments of frustration thanks to the guys on this site!!!

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Long Live Compact & Repair, DeCompile and DAO !

mx

dqmq

Let's hope you find the culprit.