autono (2k)

Re: autono (2k)

In the following, I will call the table in which you want to set the start value for the AutoNumber field the 'target' table.

Create a temporary table with one field of type Number, Long Integer.
Give the field the same name as the AutoNumber field in the target table.
Create one record in the temporary table by entering the value 9999 (one less than the starting number you want)
Create a query based on the temporary table.
Add the field to the query grid.
Change the query to an append query (Query | Append...) and select the target table as table to append to.
The name of the AutoNumber field should be entered automatically in the Append To row.
Select Query | Run or click the Run button on the toolbar.
Close the query (you don't have to save it unless you want to repeat it later)
You can also delete the temporary table unless you want to reuse it.
Open the target table and delete the record with AutoNumber 9999.

Note: you should not compact the database before a new record has been added to the target table. If you do, the AutoNumber field may be reset to start at 1 again.

Re: autono (2k)

That is the way an AutoNumber field works. Once a number has been assigned, it will not be reused. The only exception is that compacting a database will make the AutoNumber continue at the highest existing number plus 1, and even that does not always work in recent versions of the Jet engine. AutoNumber will never fill in the gaps between existing numbers.

AutoNumber fields are intended to provide a unique, but essentially meaningless ID number for records. If you have special requirements for numbering the records, you should not use an AutoNumber field, but write code to handle the numbering yourself. That can be a lot of work, but you will have complete control over how the numbers are assigned.