Autonumber Already Exists

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

All,

Anybody ever see an autonumber in a small table (<3000 records) create
an autonumber out of sequence? I can't add a new record without
getting an error message that the primary key autonumber already
exists (which it does). Help!
 
Yes. Autonumbers are primariy for the purpose of joining tables, they are not
necessarily sequential, and are NOT designed to be record numbers. If you
want actual record numbers, I think Allen Browne has a tip on that at his
site, www.allenbrowne.com
 
Sorry if I'm not being clear...I don't need a record number, nor do I
need it to be sequential. I simply need an autonumber, which happens
to be the primary key of this particular table, to act like an
autonumber is supposed to and NOT repeat itself.

I found the following KB article, which I'm digesting now:
http://support.microsoft.com/kb/884185

--Tom
 
The KB article below allowed me to "re-seed" the autonumber field.
All is well now.

Note: The SQL-specific re-seed query workaround referenced in the KB
article did not mention that it couldn't be used on linked tables. To
use it, I had to create the temporary query directly in the back end
database on the server.

Hope this helps someone else in the future,

Tom
 
I see that some years ago and I use:

Private sub Form_BeforeInsert(Cancer As Integer)
If DCount("MyField" , "MyTable") = 0 Then
me.MyField = 1
Else
me.MyField = DMax("MyField" , "MyTable") + 1
End If
End Sub
Click to expand...

Similar logic but simpler:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.MyField = NZ(DMax("[MyField]", "[MyTable]")) + 1
End Sub
 
Back
Top