Auto Number Field Change

[Guest]

How can I change the starting number for the auto number field? I am using
Access 2002

 

[Van T. Dinh]

You can use an APPEND Query to insert a dummy Record with the AutoNumber
value one less than the number you want to start.

OTOH, the only purpose of an AutoNumber Field is to provide uniqueness to
each Record in the Table. The AutoNumber sequence will develop gaps, become
negative or even random. Generally, users should not even see the values of
the AutoNumber Field. Hence values allocated should not matter at all.

If you use the AutoNumber sequence with some human meaning, this can create
problems later.

 

[=?iso-8859-1?Q?J=F6rg_Ackermann?=]

Hi,

pcteacher schreibselte:

How can I change the starting number for the auto number field? I am
using Access 2002

If ID is the Autonumber-Field and TableA is your table:

In the imadiate-Window type ([RETURN] after each line)

currentdb.execute "Insert into TableA(ID) Values(4587);"
currentdb.execute "Delete * from TableA;"

Then work normaly with your Table.
The first new autonumber will be 4588.

Joerg

 

[imbd4a]

If that is indeed possible, how then should I go about developing a
numerical Work Request Numbering system that would not eventually develop
into such madness?

Brian

 

[Van T. Dinh]

This depend on whether the database is a single-user database or a
multi-user database. Either way, the data entry must be limited to Form and
not datasheetView of the Table or Query since there are no events you can
use to run your code to create the "next number"

For single-use database, you can simply use the DMax() function to find the
giher number used and add 1 to get the next number.

For multi-user database, you will need to ensure that 2 more users don't
grab the same number (at the same time). This involves using a 1-Record
storing the next number to be allocated. Just before the user saves the new
Record, your code locks this Table (using Recordset based on this Table) so
that no other users can access it, grabs the number to be allocated for this
user's new Record, updates the Record in the 1-Record Table to the next
number, releases the Recordset (hence unlocks the Table) and then saves the
Record with the number that has been allocated.

Search Google for "Custom Number Sequence". The code to do this has been
posted a number of times in these Access newsgroups.

This won't prevent gaps when users delete existing "Work Request" Records,
though.