1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
| Function modGetNewBillNumber(YrVetStructureID As Long) As String
'Create a new Bill Number
Dim strCrtYear As String, strCrtMonth As String, CrtComptrNameST As String, CrtComptCapsTag As String, _
strLastBillIncr As String, lngLastBillIncr As Long, _
lngNewBillIncr As Long, CntOfZero As Long
'1. Get string for current Year and current Month
strCrtYear = CStr(Year(Now))
If Len(CStr(Month(Now))) = 1 Then
strCrtMonth = "0" & CStr(Month(Now))
ElseIf Len(CStr(Month(Now))) = 2 Then
strCrtMonth = CStr(Month(Now))
End If
'2. Get current computer capital letters Tag (PosteAbr in Table "VetStructuresPostes")
CrtComptrNameST = Environ("Computername")
CrtComptCapsTag = DFirst("PosteAbr", "VetStructuresPostes", "ComputerName = '" & CrtComptrNameST & "' And VetStructureID =" & YrVetStructureID & "")
'3. Get last Bill increment issued by this computer, both as string and as long
If DCount("BLID", "BL") = 0 Then
strLastBillIncr = "0000000000000"
lngLastBillIncr = 0
Else
strLastBillIncr = Nz(DMax("Right([BLNr], 5)", "BL", "BLNr like '*" & CrtComptCapsTag & "*' And VetStructureID =" & YrVetStructureID & ""), 0)
lngLastBillIncr = Nz(DMax("Right([BLNr], 5)", "BL", "BLNr like '*" & CrtComptCapsTag & "*' And VetStructureID =" & YrVetStructureID & ""), 0)
End If
'4. Get new Bill increment
If lngLastBillIncr < 100000 Then
lngNewBillIncr = Nz(lngLastBillIncr) + 1
Else
lngNewBillIncr = 1
End If
'5. Concatenate Year + Month + Tag + Increment
CntOfZero = 5 - Len(CStr(lngNewBillIncr))
If CntOfZero = 4 Then
modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "0000" & CStr(lngNewBillIncr)
ElseIf CntOfZero = 3 Then
modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "000" & CStr(lngNewBillIncr)
ElseIf CntOfZero = 2 Then
modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "00" & CStr(lngNewBillIncr)
ElseIf CntOfZero = 1 Then
modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "0" & CStr(lngNewBillIncr)
ElseIf CntOfZero = 0 Then
modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & CStr(lngNewBillIncr)
End If
End Function |
Partager