Cannot add new records to table with auto number primary key

[Lewiy]

Have just started experiencing some very strange behaviour in Access 2016. This is happening even on a brand new database with one table.

E.g. I have set up a table with an ID field as primary key and data type of autonumber along with a couple of other short text fields. I enter some data just fine, say three rows. Then I run some SQL via VBA with the intention of creating a new data item with the same data as the row with ID 1:

Dim sqlNewVersion As String
sqlNewVersion = "INSERT INTO [dataItems] SELECT * from [dataItems] WHERE [ID] = 1"    
DoCmd.RunSQL sqlNewVersion

When executing the code, I get an error saying that the row was not inserted because of a primary key duplication.

When I then return to the table and try to enter a new data row manually, I see that the autonumber is setting to '2' which is a duplication of the second data row and therefore won't allow me to enter any new rows of data. I've never seen this before. Any ideas?

 

[Joe4]

Yes, you cannot add a new record in that way, because you cannot add the Auto number field from the old record from the old record since it already exists, and it must be unique.
So, just add all the other fields, except for the AutoNumber field, and it should work.

For example, let's say the Autonumber field was [ID] and the other fields were [Field1], [Field2], and [Field3]. Then this code should work:

Dim sqlNewVersion As String
sqlNewVersion = "INSERT INTO [dataItems] SELECT [Field1], [Field2], [Field3] from [dataItems] WHERE [ID] = 1"    
DoCmd.RunSQL sqlNewVersion

 

[Lewiy]

Ahhh, thanks! I knew there'd be a reason for it. Taking a slightly different approach and doing it through an append query now, but it's working!