INSERT INTO does not append new records for existing IDs 1

[pjtarheel]

Hey,

I'm using a table to track all changes made to another table (through a form). The corrections table is identical to the main table (except that the GroupID is the primary key in the main table and I have an autonumber primary key for the corrections table, because I want to be able to have multiple entries for the same GroupID in this one).

I've got the following code in my form to insert the current record into the corrections table before updating the main table (thanks to RoyVidar for helping me get part of this sorted out!).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As Database

Set db = CurrentDb
db.Execute "INSERT INTO [tbl_GROUP-INFO-CORRECTIONS] " _
& " SELECT * FROM [tbl_GROUP-INFO] WHERE " _
& " [tbl_GROUP-INFO].[GroupID] = '" & Me![GroupID] & "';"
Set db = Nothing
End Sub

This is working fine for a single correction to a GroupID. However, if I change a record more than once, it is not inserting the additional record(s) into the corrections table. Strangely, the autonumber in the corrections table advances by one (you can see the skipped numbers when a new record actually is inserted). But the actual record is not being appended - it will only hold one record per GroupID. If I delete the record in the corrections table, then make a change in the main table, then that record is inserted just fine. It just won't hold more than one. There are no key violations here that I can see.

Any ideas what's up??????????

Thanks!!!!

 

[DoubleD]

This sure sounds like a key violation issue. Have you tried creating a query that does your insert, and letting it prompt you so you can see whether Access thinks there is a key violation?

I am what I am based on the decisions I have made.

DoubleD

 

[pjtarheel]

Thanks! You were right - I tried appending the data with a query and got the key violation error. So I looked more closely at my table, and I the GroupID was still indexed with no duplicates (Duh!). I had copied my table, removed the primary key designation, but did not think to look at the indexing settings. Now I feel kind of stupid.

Thanks again! Who knows how long it would have taken me to figure that out.

Paul