Set auto#

[szag via AccessMonster.com]

I have a historical table with a JOB#'s field in it. the numbers for this
field were entered somewhat randomly and go up to about the number 7700. I
would like to set this JOB# field to start at 8000 for all new jobs going
forward then just go up by one for each newly assigned JOB#.

 

[Jeff Boyce]

If the field is defined as an Access Autonumber, congratulations! You've
(re-)discovered one of the reasons why the Access Autonumber is unfit for
human consumption.

The Access Autonumber data type works great for what it is designed to do,
that is, to provide a unique row identifier.

If you expect it to be sequential without gaps, or plan to be setting the
value, you need to use a different data type -- those are NOT what
Autonumber is designed to do.

Try searching on "custom autonumber" for approaches to creating your own
sequential numbering procedure.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Clifford Bass via AccessMonster.com]

Hi,

Take a look at <http://support.microsoft.com/kb/812718>.

Clifford Bass

 

[BruceM via AccessMonster.com]

Here is one way of creating a custom "autonumber" that increments by 1 with
no gaps:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

Note the multi-user example, if there could be more than one user at a time.

To start at 8000 the simplest would be to create a record with 8000 as the
job number, then lock the text box. Or you could create a dummy record (or
renumber an old record) with the number 7999.