Auto Number has lost it's position

[kevnaff]

Hi All.

I have copied a table in my database with the structure of it staying the same. I have then appended data from another table in to this new table.

Now when trying to add a new record I receive a message that this would create duplicate values in the index key.

If I was to create a new record right now, the primary key auto number would be 17005, where as the appended data has primary key values that go up to 17536.

Is there any way to set the auto number start point so that it would start at 17537?

I could manually enter a new record 500 times and then exit back out of the form. But I'm guessing there must be a simpler way.

Thanks

 

[Gasman]

A quick Google would show you how?

Google Search

Admittedly, I have only reset it back to 1, but you should be able to select your starting number.

Google Search

I found what I used, you can amend to suit.

Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub

Do look at this code carefully, as it suited my needs. Likely you will not want all the lines of code?

 

[isladogs]

Try compacting the database. The ID for the next record should then be 1 more than the last saved record

 

[ebs17]

Is there any way to set the auto number start point so that it would start at 17537?

ALTER TABLE YourTable ALTER COLUMN YourField COUNTER(17537,1)

 

[Cotswold]

In this situation I would be concerned that there may be corruption in the table in question. Look for record-slip, where the contents of one record are mixed with another. Also, what some people call hieroglyphics in a few or many records. Which could account for the error.
Rather than relying in the system number, I'd use a field with a new number in the sequence set by your code when adding a new record.