Dave is probably asleep right now
I use this for when I want to reset the Autonumber field after testing. However the experts say there is no real need as it should not matter what the value is, just that it is linked correctly.?
Code:
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
NOTE the DELETE command !!!
Other ways are here https://docs.microsoft.com/en-us/office/troubleshoot/access/reset-autonumber-value