Get second AutoNumber based on a condition?

[OrlandoG7]

I have one table that has general info such as the status of a Job--wheter it's an "Estimate" or wether it's "Booked". In this table there is one AutoNumber field set as Primary Key, named [EstimateNumber].

When the user changes the job status from "Estimate" to "Booked", I want a second autonumber, independant of the [EstimateNumber], to become available. Any suggestions?

 

[demoman]

This is actually more of a design suggestion. I have built MRP/ERP systems which use estimate/bid/award logic. What has worked well for me is, when an estimate is accepted (won) a new jobHeader record is created, with the estimateID as a linking foriegn key to the estimate table. This may solve your problem and is a proven design.

Cheers
Steve