Auto Increment / Auto Number without using standard "autonumber" field

[Eddy Sincere]

Can I create an auto increment field based off the value of the AutoNumber field? My table has an AutoNumber filed as the PK. Because MS Access always increments it ensures the field value is unique. I want to use this as the base for creating the value of a new field ([Field1], e.g...



[Field1] = [AutoNumber] + 10000

If I had three records, the value of [Field1] would be 10000, 10001 and 10002.

This calculation can't happen until the record becomes active and Access assigns a value to the [AutoNumber] field. I was thinking I may be able to write some VBA on the "After Update" event for the record that runs the calculation and inserts the data into [Field1]? Is this possible or am I stuck using the NZ(DMax) option?

[ranman256]

no, autonumber is the only autonumber.
you CAN write code to build your own, but its not really auto at that point.
Dmax is it.

Autonum is a passive mindless method that works. Why make extra work for yourself?

[Eddy Sincere]

Thanks for the response. I was hoping that was a trick I didn't know about.

In answer to your question, I have an Autonumber field as the PK, however, per the good database practices, it doesn't "mean" anything. The user never sees it and it's only purpose is to serve as the PK. For this application I wanted to create a number for vendors, is unique and has some logic built into it. The Autonumber function but then I start to deviate from the good database practices and that's usually not a good idea in the long term. I'll just use the DMax.

Cheers!