Numbering starting at 4000

[jbphoenix]

I have a form to enter RMA data. I need to start my numbering at 4000 without using an autonumber. The ID field is text and cannot be changed. The Customer_Order table is a linked table. A letter is going to be added infront of the ID depending what the user chooses from a combobox. The letter(R,A,C) signifies what type of transaction it is. Here is an example -

R4000
A4001
A4002

Currently there are about 100,000 records in the database but none with an ID between 4000 and 10000. The majority of the records have a letter preceeding the number. Any ideas on how to start numbering at 4000?

 

[Moniker]

Store the R, A, or C is a separate field so that you can isolate the number. You manually start the first one to be 4000. From there, you can automatically do it by using a DMax(Number_Field)+1

A way to do it while using an autonumber (which may be easier for you) is to start your autonumber at 1, and then read it and add 4000 to it for each record.

The trick will be to store the R, A, or C value separately, which makes playing with the number easy. To get it back to how you want it, you just format it as a calculated field when needed, as in RMA_Num = [YourRACField] & [YourRMANumberField].

 

[neileg]

A way to do it while using an autonumber (which may be easier for you) is to start your autonumber at 1, and then read it and add 4000 to it for each record.

Just be aware that an autonumber does not guarantee contiguous numbering. In other words, there will be gaps in the sequence.

Using DMax()+1 as the very last action in saving a new record almost guarantees no gaps.

 

[jbphoenix]

I've used the Dmax function in the past but since the ID field is stored as text I can't get the Dmax function to work.

Here's what I have-
Private Sub CustID_AfterUpdate()
Text115 = DMax("ID", "Customer_Order") + 1
End Sub

After I update the CustID I get a Run Time Error 13 and I click on debug and it takes me the Text115=Dmax("ID",:Customer_Order")+1 line of code.

Do I need to convert the text to a number then use the Dmax function?

 

[Moniker]

As I initially stated, you need to take the R, A, and C off of the RMA and store it separately. Then you have a numeric value on which DMax will work.

TABLE: RMA_Type

RMA_ID   RMA_Type
1        R
2        R
3        C
4        A
5        C

TABLE: RMA_Number

RMA_ID   RMA_Num
1        4000
2        4001
3        4002
4        4003
5        4004

Join RMA_Type to RMA_Number on the RMA_ID field, and conditionally use =[RMA_Type] & [RMA_Num] to get your full RMA string.

 

[jbphoenix]

Actually I do have the RMA_Type stored in a different field. I am only adding it to the ID after updating the RMA_Type. Sorry I should have mentioned that.

Private Sub RMAType_AfterUpdate()
Me.ID = Me.RMAType & Me.ID
End Sub

The ID field is a text field. The table is a linked table from our MRP. What about using the Val() function?

Thanks for you help. This is the only thing keeping me from finishing this project.

 

[Moniker]

Val should work, but is there any reason you can't just convert the data type of ID to be Long Integer? I understand that it's coming in as text from an outside source, but an ALTER TABLE data definition query can change the data type for use in Access.

 

[jbphoenix]

I've never done a data definition query. Do you know of anywhere I can find some documentation on how it works?

 

[Moniker]

Check out response #2 here.