Access - Starting Record Autonumber

[NellyC]

I am setting up a purchase order system in access and have set the purchase order number to Auto Number. Does anyone know how I can get the autonumber to start at 800001 without pasting in 800000 false records

Thanks
NellyC

 

[sawedoff]

This is from "Help" in Access:
Change the starting value of an incrementing AutoNumber field
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.
How?

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.
How?

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.

Sawedoff

 

[pbrodsky]

Note that it is NOT a good practice to have an autonumber field whose value means anything. You simply do not have enough control over the behavior. You would be much better off developing the numbers yourself and using an ordinary numeric field. If you search this forum, you should find plenty of examples.

 

[fneily]

Here are the standard senarios for autonumbering:

Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.

 

[Storyteller]

Hello All,
A great "Autonumber" solution I have found is from the "Access Cookbook" published by O'Reilly.

The "Create and Use Flexible AutoNumber Fields" example paid for the book with just one use.

This example works well for PO's as the number is not created until the PO is committed.

Hope this helps.
Michael

 

[NellyC]

Great Help. You have all saved me a lot of time

Thanks

NellyC