Cannot add new records to table with auto number primary key

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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:
VBA Code:
Dim sqlNewVersion As String
sqlNewVersion = "INSERT INTO [dataItems] SELECT [Field1], [Field2], [Field3] from [dataItems] WHERE [ID] = 1"    
DoCmd.RunSQL sqlNewVersion
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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!
 

Forum statistics

Threads
1,082,348
Messages
5,364,858
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top