Setting AutoNumber to Non-Default Value

[Jeffreyu]

I would like to use the AutoNumber data type within a field. However I would
like the sequential autonumbering to begin from 6000 rather than the default
value of 1(for the first record). Thanks for your help in advance.
Jeff

 

[Larry Daugherty]

If you actually care what the value of the field happens to be then
you should not be using the Autonumber datatype. It exists for the
sole purpose of generating unique values for a surrogate Primary Key.
Use it for any other purpose and you will find parts of your life
wasted in trying to get it to behave as you wish. It is not
guaranteed to be sequential and, in most cases, eventually is not.

HTH

 

[John W. Vinson]

I would like to use the AutoNumber data type within a field. However I would
like the sequential autonumbering to begin from 6000 rather than the default
value of 1(for the first record). Thanks for your help in advance.
Jeff

Well... Don't.

Autonumbers are designed for one purpose, and one purpose only: to provide a
meaningless unique key. They WILL develop gaps; not only will deleting a
record leave a gap, so will hitting <Esc><Esc> at any point after starting a
new record. Loading data into the table with an Append query can generate a
gap, often a big gap. Replicating your database will make all autonumbers
random!

If you care about the value in an ID field, use a Long Integer and maintain it
yourself with code, rather than using an autonumber. For instance, in a Form
you can use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]"), 5999) + 1
Me.Dirty = False
End Sub

to increment the largest existing ID and immediately write the record to disk.

John W. Vinson [MVP]