Multiple AutoNumbers?

[Starnheavn]

I am a somewhat intermediate user of Access (I currently work with 2007). I have created a database for our RFQs (requests for proposals). In the database, I have RFQColumn Autonumbered (this column is to number our RFQs incrementally and sequentially). In going over the project with my boss, I need to add another field where a quotation number is given (different from the RFQ # which is our internal reference the quotation number being the number for the customer to reference). Is it possible to create the Quotation Number field and have it autonumbered (wanting number to start at 12-112)? Please help?

 

[Beetle]

I have RFQColumn Autonumbered (this column is to number our RFQs incrementally and sequentially).

If you mean that this field is using the Autonumber data type then you will not get the results you expect. The Autonumber data type is not guaranteed to be sequential, or even positive. It is only guaranteed to be unique. You will end up with gaps in the sequence. If you want incremental and sequential record numbering, you've got to "roll your own", so to speak, which is usually done with the DMax function. Example;

DMax("[RFQ]", "YourTable") + 1

This would be done in the Before Update event of your form, so the code might look like;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Me.NewRecord Then
    Me![RFQ] = DMax("[RFQ]", "YourTable") + 1
End If

End Sub

As for the quotation number, that would typically not be stored in the table, just "calculated" for display in a form control or a query field. That might involve some use of DMax as well, but it would hard to be specific without knowing your logic for how the number should be generated.

 

[Starnheavn]

Thanks for responding. The RFQ Number is autonumbered starting at 5066. The Quotation Number for the Quote would be 12-XXX (12 being the year 2012-and whatever number I decide to start out with as example 112 so Quote number would be 12-112) no calculations needed. Just need it to be autonumbered like RFQ so that the numbers are not duplicated.

I am not sure how to create another field with autonumber since RFQ is the primary key and autonumbered. Hope I explained this right. ;-)

 

[Mile-O]

Starnheavn...listen to what Beetle has said and get the idea of Autonumber out of your head. It's a meaningless number in Access, used only to ensure a record has a unique code. It should not be used to apply any sort of logic to, which is what you are doing.

Or, to put it another way, reserve Autonumber as an access term and an access term alone. Leave it at that. You don't need it any further. What you want is a field that auto-increments with each new record. (An autonumber will not do that.)

Now do as Beetle says...or are you not using forms in your database?

 

[Starnheavn]

I am using forms.

 

[Starnheavn]

Thanks Beetle, appreciate the help. I will keep you posted on how this works for me.