Access Attempting to Assign Duplicate AutoNumbers

[EnginerdUNH]

Hi,

In my main table, I have a ID field which is an AutoNumber. Recently, my database started trying to assign new records the same ID AutoNumber as the ID for an existing record. I noticed this when I was trying to save some new records using the form that has this table as its record source and/or a couple of different append queries that append to this table from data on other forms.

Has anyone seen this problem before and possibly know how to fix it? Thanks in advance!

[Gasman]

No, but I would find the highest number (assuming you are using the default incrementing ID) and ressed the table above that.

Code: Select all

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 above was resetting a table AND deleting the records. I used this to start the table afresh after development.
So I would comment/remove the strSQL lines. The important part is the (1,1) which means start at 1 and increment by 1.

[EnginerdUNH]

Hi Gasman,

Thank you for providing the code. I have a few questions based on the code that you provided before I try anything:
1) How did you get the database to run this code? Did you temporarily put a command button or something on your main dashboard or a dummy form that you could click it and run?
2) I'm assuming that pstrTable is the string where you pass in the name of the table you're trying to re-index and that pstrID is the name of the field (column) you're trying to re-index?
3) How often did/do you re-run this? just once? Every so many days? Every time someone goes to save a new record?

Thank you again for your response.

[Gasman]

1. Just called it from the immediate window.
2. Yes, correct. However I was always resetting the table to 1 after deleting all the records after testing. I would pass in the starting number as well in your case, but really no need to parameterise it unless you expect to use on multiple tables as I did. Just hardcode it.