Parallel sequential numbering based on a criterion.

[JayF]

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.

[patjones]

"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:

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

[ADezii]

If I read you correctly:

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.

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

[JayF]

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.

[ADezii]

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.

[JayF]

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.

[ADezii]

@JayF:
The pleasure was all ours.