Setting AutoNumber to Non-Default Value

[Guest]

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

 

[Joseph Meehan]

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

If you want to try anyway (no guarantee of success) put in a dummy
record with 6000 manually input. See if your next number is 6001. BTW
don't be surprised if it misses a few numbers from time to time or if it may
decide to go back and start using those unused numbers like 2,589.

 

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