Easy Autonumber Question

[spencern]

Hi,
I was wondering how you can set the autonumber field in Access to start at a number other than zero and go from there. In the northwind DB, the order numbers start at 10248 and go up from there. I'm guessing there is a way to do this other than adding 10,247 blank records to get the autonumber up high enough.

Anyone know how it's done?

Thanks,
Spencer

 

[petersdaniel]

You can set the Autonumber in the database table at a specific level by running the following query. This assumes that no other fields are set as "required"

PARAMETERS [Number] Long; INSERT INTO tblNameHere ( intNumber ) SELECT [Number] AS Expr1; petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[spencern]

I copied and pasted the stuff into a new query and replaced tblTableNameHere and intNumber with the table name and the number that I wanted it to start at, but when I run the qurey I get the box asking for a param. value for 'number'.

Is there anything else I should change in the query you posted?

Thanks,
Spencer

 

[petersdaniel]

intNumber should be the fieldname that you are currently autonumbering. so if your table was tblFoo and the field that you are autonumbering is fooID, the query would be...

PARAMETERS [Number] Long; INSERT INTO tblFoo ( fooID ) SELECT [Number] AS Expr1;

The parameter value being requested would be the number you want to start at for Auto numbering. So if you want to start at 5057, that would be the number you feed to the parameter. Each new record added after you set the number would increment upward from that point... petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[Henaniy]

NewValues Property


You can use the NewValues property to specify how AutoNumber fields increment when new records are added to a table.

Note The NewValues property applies only to AutoNumber fields.

Setting

The NewValues property uses the following settings.

Setting Description
Increment (Default) AutoNumber field values increment by 1 for new records.
Random AutoNumber field values are assigned a random Long Integer value for new records.


You can set this property in the table's property sheet in table Design view by clicking the General tab in the Field Properties section.

Remarks

When you replicate a database, AutoNumber field settings are set to Random to ensure that new records entered in different replicas will have unique values.