Appending data to a table with Autonumber Primary Key

[TwoOdd]

I am trying to use an append query to append data to a table that has an Autonumber field as it's primary key.

I am not referencing the autonumber field in the query so that access will automatically apply the number to the field.

I get an error message when trying to run the append query saying that it did not enter the records due to validation rule violations.

I made a copy of the table and eliminated the autonumber field and appended the data to the copied table without any problems, thus narrowing the problem down to the autonumber field. I have also compacted and repaired the database with no luck.

The original db was created in Access 2000 and I am using Access 2003. Can someone please explain what is going on?

If you need more info to answer the question, please ask.

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[RoyVidar]

I think this may be this bug BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003.

It seems reseeding the autonumber after compact might do.

Roy-Vidar

 

[TwoOdd]

It was worth a shot, but that did not fix the problem. It still gives me the validation rule violation for every single record that I try to append to the table.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[lespaul]

what is the sql for the insert statement you are trying to run?


Leslie

 

[TwoOdd]

PARAMETERS [What Revision are you importing?] Short;
INSERT INTO tblTakeOff ( LineCostCode, Revision, CO_ID, COdescript, DateQSReceived, Ref, CostCode, SubFab, Fab, Utility, HPM, BulkGas, AdditionalAdjustment, AAQuantity, ItemCode, Comments, SystemSpec, ShortSpecification, Specification, classification, [Size], Unit )
SELECT qryCreateTOExport.LineCostCode, [What Revision are you importing?] AS Revision, [Forms]![frmBOQ]![Combo76] AS COID, [Forms]![frmBOQ]![frmChoosedescript]![COdescript] AS Descript, Now() AS Expr1, qryCreateTOExport.Ref, qryCreateTOExport.CostCode, qryCreateTOExport.SubFab, qryCreateTOExport.Fab, qryCreateTOExport.Utility, qryCreateTOExport.HPM, qryCreateTOExport.BulkGas, qryCreateTOExport.AdditionalAdjustment, qryCreateTOExport.AAQuantity, qryCreateTOExport.ItemCode, qryCreateTOExport.Comments, qryCreateTOExport.SystemSpec, tblScheduleOfRates.Spec, tblScheduleOfRates.Specification, tblScheduleOfRates.Classification, tblScheduleOfRates.Size, tblScheduleOfRates.Unit
FROM (qryCreateTOExport INNER JOIN tblBOQ ON qryCreateTOExport.CostCode = tblBOQ.CostCode) INNER JOIN tblScheduleOfRates ON qryCreateTOExport.ItemCode = tblScheduleOfRates.ItemCode;

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[lespaul]

and are any of these fields the autoincrementing field? I don't think you need all the "As" for each field in the select.

Are you sure that all the information is the correct type? For instance, you have COID coming from a text box, is that a numeric field? Is DateQSReceived a Date field? Time field? Date Time stamp? Does the NOW() function return the correct information? Have you tried removing all but one of the fields and then add them back to see which field is causing you to get the error?

Leslie

 

[TwoOdd]

No, none of those fields is/are the autonumber field. Yes, I have double checked all the datatypes. As stated in the first post, I was able to append this data to an identical table (minus the autonumber field) without any problems. I only get the error when the autonumber field is in the recieving table.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[PHV]

Leslie, the OP said this:
I made a copy of the table and eliminated the autonumber field and appended the data to the copied table without any problems

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244