Auto number field

[Eoluke]

Hello, I am in the process of designing and inventory control database. I would like to have an auto number field that either will increment or randomly choose numbers. The problem I am having is I want the numbers to be 6 or more digits. Also, this field will not be a primary field. i am using a product ID for primary. Thanks in advance.

[CJ_London]

Seems a strange requirement but just set the field type to be autonumber but not the primary key. Why 6 digits? you could just format the field/control to 000000 which will show 1 as 000001

If not then once you have your table design you can run this code before entering any records - change mytable and id to the name of your table and field

ALTER TABLE myTable ALTER COLUMN [id] COUNTER (100000,1)

Note that autonumber only guarantees to be unique and incremental, you cannot rely on it to be consecutive - e.g. if you start to enter a record then cancel, the autonumber is not reused

the alternative is to use what is often called dmax+1 - google it to find out more.

Ensure that your product ID cannot be changed once entered (if you have a typo - tough) otherwise links to other tables will be broken

You would be better to have the autonumber as your primary key and productID is then free to be corrected if necessary

[Eoluke]

Yes it is a strange requirement. I will be using this field to assign barcodes to be read with a handheld reader. Also the data is already inserted, I am adding the field to the records. Is this even possible?

[CJ_London]

only by creating a new table - running the query as suggested, then copy all the data across from the old table

you might be better with dmax+1 so you can populate existing records (autonumbers are not editable)