autonumbering is reusing numbers

[Lynn atkinson]

I have an employee details table which is set to give a
new entry a new and unique number automatically
(autonumber). This has been working fine until recently
when it has started creating numbers which have already
been used and therefore are not accepted. As this is an
autonumber field I cannot type an unused number manually
and so the table has to be shut down without saving the
changes. I do not know what has happened or what I have
done to cause this. I have tried the compact and repair
command but it is still not working.
Does anyone have any ideas?

 

[Allen Browne]

After three requests for this in the last 24 hours, there is an updated
version of this function in the new article:
Resetting AutoNumbers
at:
http://allenbrowne.com/ser-40.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Download Service Pack 7 for Jet 4 from:
http://support.microsoft.com/default.aspx?scid=kb;en-us;282010

You can also reset the Seed for all the AutoNumbers in your database with
the code below. It requires a reference to the ADOX library (Tools |
References).

Function FixAutoNum()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim varMaxID As Variant
Dim strTable As String

Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
strTable = tbl.Name
If tbl.Type = "TABLE" Then
If Left(strTable, 4) <> "Msys" And Left(strTable, 1) <> "~" Then
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
If col.Properties("Seed") < 0 Then
varMaxID = DMax("[" & col.Name & "]", "[" &
tbl.Name & "]")
If Not IsNull(varMaxID) Then
Debug.Print tbl.Name & Space(40 -
Len(tbl.Name)) & col.Name, col.Properties("Seed"),
col.Properties("Seed") = CLng(varMaxID) + 1&
Debug.Print " => " & col.Properties("seed")
End If
End If
Exit For
End If
Next
End If
End If
Next

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I have an employee details table which is set to give a
new entry a new and unique number automatically
(autonumber). This has been working fine until recently
when it has started creating numbers which have already
been used and therefore are not accepted. As this is an
autonumber field I cannot type an unused number manually
and so the table has to be shut down without saving the
changes. I do not know what has happened or what I have
done to cause this. I have tried the compact and repair
command but it is still not working.
Does anyone have any ideas?