435,426 Members | 3,368 Online
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

# Parallel sequential numbering based on a criterion.

 P: 45 Hi all, I'm thinking this might be fairly simple and use Dmax, but I'm struggling to get my tired, amateur brain around it and surprisingly can't find any examples in the mighty Goo. I need to produce two sets of sequential reference numbers within the same field based on the value of another field. REFA000001, ...2, ...3, etc. and REFB000001, etc. The primary key would not be involved. It would work something along the lines of (bad syntax just for demonstration): If [criteriafield] > 0 Then [REF] = "REFA" & DMax(REFA) + 1 Else If [criteriafield] = 0 Then [REF] = "REFB" & DMax(REFB) + 1 I guess the code would execute on the After Update event of the [criteriafield] control. I hope that is clear enough. Help, clues or criticism would be hugely appreciated. Feb 12 '12 #1

1. You have a Field named [REF] in a Table, let's call it Table1.
2. [REF] must be exactly 10 characters in Length.
3. [REF] can begin with either one of the 2 Characters (REFA* or REFB*).
4. Based on the Value in a Text Box, let's call it txtCriteria, you must Increment the Maximum Value in the [REF] Field by +1.
5. Values in the [REF] Field must be sequential in nature.
6. There are pre-existing Values in this Field in the Table.
Expand|Select|Wrap|Line Numbers
1. Dim txt As TextBox
2. Dim strLastSeq As String
3. Dim strNextSeq As String
4.
5. Set txt = Me![txtCriteria]
6.
7. If IsNull(txt) Or Not IsNumeric(txt) Then Exit Sub
8. If txt < 0 Then Exit Sub
9.
10. Select Case txt
11.   Case Is > 0
12.     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFA*'")
13.     strNextSeq = Left\$(strLastSeq, 4) & Format\$(Val(Mid\$(strLastSeq, 5)) + 1, "000000")
14.   Case Else
15.     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFB*'")
16.     strNextSeq = Left\$(strLastSeq, 4) & Format\$(Val(Mid\$(strLastSeq, 5)) + 1, "000000")
17. End Select
18.
19. MsgBox "Next Sequential Number is: " & strNextSeq

6 Replies

 Expert 100+ P: 931 "REFA000001" is not a number, so DMax() + 1 isn't quite going to work. As long as there are always six digits on the rightmost of the string, try this function: Expand|Select|Wrap|Line Numbers Public Function AddToREF(strREF As String) As String   Dim strNextDigit As String   'Get the next digit  strNextDigit = CStr(Right(strREF, 6) + 1) 'Build the string  AddToREF = Left(strREF, 4) & String(6 - Len(strNextDigit), "0") & strNextDigit   End Function Feb 12 '12 #2

 Expert 5K+ P: 8,638 If I read you correctly: You have a Field named [REF] in a Table, let's call it Table1. [REF] must be exactly 10 characters in Length. [REF] can begin with either one of the 2 Characters (REFA* or REFB*). Based on the Value in a Text Box, let's call it txtCriteria, you must Increment the Maximum Value in the [REF] Field by +1. Values in the [REF] Field must be sequential in nature. There are pre-existing Values in this Field in the Table. Expand|Select|Wrap|Line Numbers Dim txt As TextBox Dim strLastSeq As String Dim strNextSeq As String   Set txt = Me![txtCriteria]   If IsNull(txt) Or Not IsNumeric(txt) Then Exit Sub If txt < 0 Then Exit Sub   Select Case txt   Case Is > 0     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFA*'")     strNextSeq = Left\$(strLastSeq, 4) & Format\$(Val(Mid\$(strLastSeq, 5)) + 1, "000000")   Case Else     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFB*'")     strNextSeq = Left\$(strLastSeq, 4) & Format\$(Val(Mid\$(strLastSeq, 5)) + 1, "000000") End Select   MsgBox "Next Sequential Number is: " & strNextSeq Feb 12 '12 #3

 P: 45 Thanks guys. @ADezii In corresponding numbers: 1. Let's not beat about the bush, let's call it [Invoice Number] in tblOrders 2. [Invoice Numbers]'s length will be adjusted, those were examples I pulled from the air. 3. Correct, can begin with REFA* or REFB* 4. Based on the value of a field in the same table, let's not call it [Tax Rate], [Invoice Number] will begin with either REFA or REFB and both will be independently sequential. i.e. there will be: REFA00001 REFA00002 REFA00003 ... AND REFB00001 REFB00002 REFB00003 ... mix up in the same [Invoice Number] field, giving two sets of sequential numbers. 5. Both will be sequential in their own right. 6. Database is the blank slate you looked at yesterday, still in development so no restrictions or current data. Feb 12 '12 #4

 Expert 5K+ P: 8,638 You should then only need to 'Seed' the initial Values of REFA000001 and REFB000001. The Code will do the rest, keeping a neatly Formatted, 10 Character, Sequential String for succeeding [REF] Values. Feb 12 '12 #5

 P: 45 Amazing. Combined with Me![Invoice Number] = strNextSeq, which I managed to figure out all by my big self, it works perfectly. I don't know what to say. Sir, you are a genius, a gentleman and a scholar. You, and your fellow posters, have surely saved forum members - and lurkers - millions of man hours. You are an unsung hero. Thank you for your time. I will be sure to credit you in the code, in case someone in future comes across it. Feb 12 '12 #6

 Expert 5K+ P: 8,638 @JayF: The pleasure was all ours. Feb 12 '12 #7