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
|
Function AttribNumero(Champ As String, Table As String, Lettre As String, Optional FormatDuNb As Integer = 4) As String
'Attribution d'un numéro d'analyse de type alphanumérique:AAxx/yyyy
'où AA est un préfixe, xx l'année et yyyy le numéro d'analyse sur 4 chiffres
On Error GoTo Err_AttribNumero
Dim Bds As DAO.Database
Dim Rst As DAO.Recordset
Dim PNo As Long
Dim MaPos As Integer
Dim FormatNb As String
Dim PDeb As String
Dim ValNum As String
Dim i As Integer
Set Bds = CurrentDb()
Dim Lg As Integer
Rq = "SELECT MAX(" & Champ & ") as ValMax FROM " & Table & " WHERE " & Champ & " Like '" & Lettre & "*' ;"
Set Rst = Bds.OpenRecordset(Rq)
If Not IsNull(Rst!ValMax) Then
ValNum = Rst!ValMax
If Len(ValNum) > 0 Then
'Recherche de la barre de séparation:"/"
MaPos = InStr(1, ValNum, "/", 1)
If MaPos > 0 Then
Lg = Len(ValNum) - MaPos
PDeb = Left(ValNum, MaPos)
PNo = Right(ValNum, Lg) + 1
Else
PDeb = ""
PNo = CLng(ValNum) + 1
End If
For i = 1 To Lg
FormatNb = FormatNb & "0"
Next
AttribNumero = PDeb & Format(CStr(PNo), FormatNb)
End If
Exit Function
Else
FormatNb = Format(FormatNb, "0000")
PDeb = Right(CStr(Year(Date)), 2)
AttribNumero = Lettre & PDeb & "/" & Format(1, FormatNb)
End If
Exit_AttribNumero:
Set Rst = Nothing
Set Bds = Nothing
Exit Function
Err_AttribNumero:
MsgBox "Fct AttribNumero " & vbCrLf & Err.Description
Resume Exit_AttribNumero
End Function |
Partager