Alternate Auto number

[jodilees]

I am trying to assign each entry a number. The database has a number in place of BA4415 etc. I want to automatically assign a new number to the next record created in a form. Is there an easy way to do this or does this number have to be manually typed by the user each time. As far as I can see Auto number starts at 1 and can not be controlled with any additional text.

Regards

Jodi

 

[MikeAngelastro]

See my reply for "Incrementing Values?" in this forum.

 

[Pat Hartman]

If you strip off the text characters and store them in a separate field, you can use an autonumber for the numeric portion. To convert your existing data follow the following steps.

1. Back up your db
2. Create a new empty table with an autonumber column.
3. Create an append query that selects data from the original table and appends it to the new table. You'll need to calculate values for the new text prefix field and the autonumber.
4. Run the append query
5. If everything is ok, delete the original table and rename the new table.

The calculated fields:

Left(YourOldKey, 2) As KeyPrefix, Mid(YourOldKey, 3) As KeyAutonumberPart