append query key violations

[JimmyN]

I use a form to build a list of records into a temporary table, and when the user has completed the form I run an append query to add the temporary records into the main table. The idea is to be be able to delete all records from the temporary table if a user 'abandons' without affecting the main data. The structure of the temporary table is identical to the main table. The only indexed field is the Autonumber ID field in the main table. I do not specify the Autonumber field in the append query. The syntax I use is:
DoCmd.RunSQL "INSERT INTO [Main Booking Subdata] ( BookingID, MenuID, Product, Price, Qty, Total ) SELECT TempBooking.[BookingID], TempBooking.[MenuID], TempBooking.[Product],TempBooking.[Price], TempBooking.[Qty], TempBooking.[Total] FROM TempBooking;"

When the query runs it seems to try to add records from the temporary table into gaps in the Autonumber field in the main table and not onto the end of the table. The gaps are where records in the main table have been deleted. Say there is a gap of three numbers in the Autonumber ID field. The query will add three records into the gap and then throw up a key violation error when it then tries to add a duplicate ID number.

How do I ensure that the append query adds records to the END of the table?? Please help!

(afterthought - I suppose I could open the main table as a recordset and cycle through the temporary table adding records but that isn't very neat.)

 

[OnTheFly]

What is the name of the Auto Number field in the main table?

 

[JimmyN]

The Autonumber field is called ID

 

[JimmyN]

I have tried the Recordset method using .addnew and .update but it is still adding records into the main table into gaps in the sequence of autonumbers in the ID (primary key) field and not onto the end of the table.

 

[JimmyN]

I have 'got round' the problem by changing the generation of the autonumber in the main table from Incremental to Random. This avoids the clashes when the records are added into the gaps.

If anyone has any comments on the original problem they would be very much appreciated.

 

[OnTheFly]

Interesting problem. I have never seen Access do this before, in fact, many people have used code to fill in gaps because they did not want them. I use auto numbering quite aften and Append queries as well and even if I delete the entire table of 50 records when I add a new one it will number it at 51 (unless it is compacted first).

I am curious, are you using Access 2000 or a subsequent version. This could be a bug that should be reported to MS.