changing start value in the auto number

[Fesco]

I am so frustrated trying to fix the Receipt Number with auto number field.
I want to assign an auto number field to records that we already have back
from December to date.

How can I start with 686486, the last number of the receipt we used.

Any help is greatly appreciated.

Thanks,
Jina

 

[Jeff Boyce]

If you are (or have been) using the Access Autonumber data type to generate
something you assign meaning to (e.g., "Receipt Number"), you need to be
aware that the Autonumber is only intended to be used as a unique record
identifier, and is generally unfit for human consumption.

If your application requires a "meaningful" number, you're better off
creating your own. You could keep the Autonumber field as a unique row
identifier, then add a new field to hold "Receipt Number". It will be
relatively easy to make the "Receipt Number" do what you want.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SuzyQ]

if you are trying to change the data in the autonumber field you cannot
change it or cause it to start at a specific number

autonumber just ensures that a unique number is assigned to a record, the
programmer has no control over what that number is

If you are trying to assign the data from the autonumber field to a
different field that can be done, but if you are trying to correct a related
table so that there are records that have the autonumber data from the
primary key of a parent table into a foreign key in the table that needs the
correction. I'm not sure how you are going to do that. Maybe a little
clarification on what you need.

 

[Larry Linson]

I am so frustrated trying to fix the Receipt Number with auto number field.
I want to assign an auto number field to records that we already have back
from December to date.

How can I start with 686486, the last number of the receipt we used.

Any help is greatly appreciated.

Autonumbers are only intended to be unique, and they are not necessarily
sequential, nor 'monotonically increasing'. If you intend to use them for
something where the particular values matter, you should re-think your
approach. They are intended for internal use, e.g., to join a related table
to its parent, not for display to a user.

You can't assign values to an Autonumber field, in any case. They just don't
work that way.

Larry Linson
Microsoft Office Access MVP

 

[Paul Shapiro]

When you import existing data into the new table you can copy the existing
values into the field even though it is specified as autonumber. If you're
not importing any existing data, you can run an append query to append a
single row with a specified receipt number.

Once you've done either one of those, the auto-numbering might fail to work
correctly. It may continue assigning values starting at 1, ignoring the data
you've entered. Compacting used to fix the autonumber seed, but it hasn't
for some time now, definitely not since Access 2003. Allen Browne's website
has the code to restore correct auto-numbering starting from the highest
existing data value.
http://www.allenbrowne.com/ser-40.html

Allen has also posted this code to reset the autonumber initial seed value,
and optionally the increment to be used between autonumbers.
1. Create a new query.
2. In first dialog, choose Design view.
3. Cancel the second dialog (Add Table.)
4. Switch to SQL View (left of ribbon.)
5. Paste this in (first value is the seed, second is the increment):
ALTER TABLE [Table1] ALTER COLUMN [ID] COUNTER(686486, 1);
6. Substitute your table name for Table1, and the name of your autonumber
field for ID.
7. Run the query.
8. No need to save the query.

However, if you need to assign receipt number values during ongoing
operations, an autonumber is probably not the best approach. If you add a
new row and then delete it, the deleted receipt number will not be re-used.
Autonumbers are only quaranteed to be unique, and are not suitable if you
don't want any holes in the numbering sequence. You'd be better off
assigning the receipt numbers for new rows in your own code in that case.