How do i start an autonumber field at a specific number eg. 1000

[Brthomps]

I have a table that must automatically create incremental numbers starting at
1000.
I have tried other suggestions I have seen on the web- eg Append queries,
they do not seem to work. I am using Access 2007

 

[Evi]

Hi, just had to do this yesterday so that I could archive some compacted
records. It's scary so do it on a copy of your db first and, when you are
sure it is OK, then rename the copy (while still keeping the original just
in case: The instructions in the MS website misses out an important stage.

Note: Do not do this if your Autonumber field is the Foreign Key Field in
another table or you will really mess up your DB



1. Open the Relationships window and note which fields are joined to your
table as Foreign key Fields. Ensure that it is not joined to another table.

2. Note which fields in your table are required fields

3. Copy Paste your table twice with only the Structure (no data) Call
them TblTemp1 & TblTemp2

4. in TblTemp1 change the Autonumber datatype to a Long Integer Number

5. Type in 1 record with the number before you want the Autonumber to
start eg 99 if you want the AN to start at 100 adding dummy data to the
required fields.

6. Add all the fields in TblTemp1 to a Query

7. Choose to Append to TblTemp2

8. Because TblTemp2 is empty and you are appending, this will work and
the Autonumber field will be set to 99

9. Delete the 1 record from TblTemp2

10. *Do not Compact until you have done the following*

11. Append all your data from your real table to TblTemp2 *except
the Autonumber field*

12. Note the exact name of your real table

13. Delete your real table

14. Rename TblTemp2 to the same name that your real table had.

16. Rejoin fields in the Relationships window

 

[BruceM]

Just remember that your new series of numbers will almost certainly have
gaps over time. If you need a series of numbers without gaps, do not use
autonumber. One way to do this is shown here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
In the example you can either manually enter the number for your first
record, or use Nz:
Nz(DMax("YourField","YourTable"),999)+1

 

[Evi]

Just re-read your reply. The method below works for other versions of Access
but may not work for 2007 which (for instance) won't reset the Autonumber to
0 after a total delete and Compact.
Evi

 

[Jeff Boyce]

Why?

Access Autonumbers are designed to be unique row identifiers. Why does it
matter what value they start with?

If you are displaying these to users, be aware that autonumbers are not
really FDA-certified for human consumption...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/