Autonumber Already Exists

[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!

 

[Golfinray]

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

 

[Tom]

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

 

[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

 

[vasco]

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!

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

 

[John W. Vinson]

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

Similar logic but simpler:

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