Autonumber Question

[Owen]

I have a table with an autonumber that I need to start
with the number 20801.

Access help says to change the New Values property.
I don't see that property listed anywhere.

How can I do this?

TIA

Owen

 

[Allen Browne]

See:
Set AutoNumbers to start from ...
at:
http://members.iinet.net.au/~allenbrowne/ser-26.html

 

[Ray]

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.
Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.

In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.


Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.
Delete the temporary table.


Delete the record added by the append query.


If you had to disable property settings in step 3, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.
Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Jeff Boyce]

Owen

A scan through this newsgroup (tablesdbdesign) will reveal a common response
to a post like yours ... "why do you care?" This isn't intended as a
rhetorical or facetious question. Autonumbers are an Access way of
providing a unique row identifier. They generally aren't suitable for human
consumption.

Why do you care what value the Autonumber "starts with"?

 

[Stephen Rasey]

One answer why you care about setting the start value of an auto number.

You may need a sequential and unique key entered on many different instances
of the same logical database. You want to start each instance with an
index like 1000000, 2000000, 3000000. This will allow you to merge or
Union the tables from the different instances at a later date.

It is just like buying books of receipts at Office depot. Each Book has
sequentially numbered receipts, but there may be no rhyme or reason as to
the starting number of each book.

Stephen Rasey
Houston
http://WiserWays.com
http://excelsig.org

 

[Ken Snell [MVP]]

Except that there is no guarantee that an autonumber field will not depart
from an incrementing sequence... they can go negative, or random. And of
course autonumber fields have an upper limit: 2147483647. And a lower
limit: -2147483648

In those cases, one DB could end up using the same "number" as one already
used in another DB; but, because the DBs are separated, you'd not know until
trying to combine the data.

Still is a good design concept to never use an autonumber field if it's to
convey "meaning" to the user. In those situations, use your own field where
you identify and set the value based on some business rule that won't cause
overlaps.

 

[Stephen Rasey]

I think your counter examples strain credulity.

If you need an sequential "autonumbering" system that will run on separate
instances of one logical database (like disconnected laptops), you would
have to do a lot of work building your own "invoice number" that was better
that that already supplied by the Access autonumber. Any numbering system
you create would suffer the same limitations. If 4 billion numbers of a
Long type aren't enough, then you have to switch to a 12+ character text key
as your index.

The question was Why you would want to provide a seed for the autonumber.
I provided a reason. Within certain caveates, the incremented Autonumber
satisfies the need with minimum effort.

Stephen Rasey
Houston

 

[Stephen Rasey]

Is there ever a need for a sequential key? Accountants have used them for
centuries in receipt books.

If you need a sequential key, what system would you create that was
stronger/better than an incrementing autonumber?

Stephen Rasey

 

[Douglas J. Steele]

The problem with using Autonumbers as a sequential key is that you can (and
will) lose numbers in the sequence. If you start to insert a row and then
change your mind, the autonumber value that would have been used is lost.
Auditors tend to get upset about missing numbers...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Jeff Boyce]

Stephen

The thread raises good points. Another example would be law enforcement
officers "ticket books". Not uncommonly, adjacent jurisdictions can have
tickets numbered in the same sequence. They are able to distinguish between
the tickets issued by two differing jurisdictions because they record both
ticket number AND jurisdiction.

I'd offer an alternate approach to starting an Autonumber in differing
sequences to document the source -- just record the source. The combination
of source and number is unique.

From a relational design standpoint, storing more than one fact in a field
is undesirable. Your numbering system stores both a sequence number AND a
source ("1000...'s" are in Houston, "2000...'s" are in Dallas, ...).

Thanks for expanding on the thoughts.

Jeff Boyce
<Access MVP>

 

[Stephen Rasey]

Yes, you could create a compound key (DBInstance, IDAuto). Then you would
be STUCK with a compound key in your design. Not a disaster, but an
annoyance in your programming. I have little doubt that that is the
"correct" way to go. Yet I do not want to go with a compound PK when a
single field PK "will do".

Let's not forget the situation where you have an existing database design
that you now want to work on four instances. You want to redesign the
database for a compound key? Or is it best to design a compound PK into
every database at the beginning? Or do grimace and set the start value of
the existing autonumber PK?

I do not want to get onto a crusade about this. The question was asked,
"Why you would want to" set the start value of the Autonumber? I think
this is a legitimate situation.

I am a big proponent of replication for Access Databases. I, too, try to
avoid using an autonumber ID for anything real, because when you might
replicate, you have to plan on random autonumber assignment from the very
beginning.

Stephen Rasey
WiserWays
Houston
http://excelsig.org

 

[Stephen Rasey]

Loosing the autonumber on an aborted AddNew is a problem. But it is a
problem no matter what system you use. When the new record is aborted, you
must still add the record to the database as a "torn up" receipt.

However, the achilles' heel to the torn up receipt might be in transaction
processing. If you rollback the transaction, the autonumber's used are
gone forever, aren't they. But that strikes me as a problem no matter
what sequential numbering system you use. Once you markup a receipt, you
cannot rollback the receipt table. You have to update it to mark it torn
up.

Any other way to do this?

Stephen Rasey
Houston
http://excelsig.org

 

[Douglas J. Steele]

If you're "rolling your own" number, when you don't save the record, then
you don't use the number, and presumably it'll be what you generate again
the next time. In other words, there shouldn't be a gap. Now, you might run
into problems with multi-user systems, but there are ways of finding gaps
and ensuring that you use those numbers.

 

[Ken Snell [MVP]]

I think your counter examples strain credulity.

Not mine, but that's ok. Everyone is entitled to an opinion.

If you need an sequential "autonumbering" system that will run on separate
instances of one logical database (like disconnected laptops), you would
have to do a lot of work building your own "invoice number" that was better
that that already supplied by the Access autonumber. Any numbering system
you create would suffer the same limitations. If 4 billion numbers of a
Long type aren't enough, then you have to switch to a 12+ character text key
as your index.

The question was Why you would want to provide a seed for the autonumber.
I provided a reason. Within certain caveates, the incremented Autonumber
satisfies the need with minimum effort.

When you want control over a number, then control it. Autonumber does not
give you control. But, if it works for you, great. My comments are intended
to provide additional perspective for the long-term situation as well as the
short-term question.