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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
| Sub ActualiseNomenclatureView()
Dim db As DAO.Database, rs As DAO.Recordset, sSql As String, s As String, sKey As String, sLib As String
'
'
'On Error Resume Next
Set db = CurrentDb
'Effacer tous les noeuds
Me.NomenclatureView.Nodes.Clear
'MsgBox Me.Recherche_Liste.Value
' Mise en place des noeuds de niveau 0
' ------------------------------------
'Mot = "TDF156400"
Set rs = db.OpenRecordset("SELECT distinct [Article], [Statut], [Libelle] FROM Nomenclature ORDER BY [Article] DESC")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
sKey = Nz(rs![Article])
sLib = Nz(rs![Article]) & " : " & Nz(rs![Statut]) & " : " & Nz(rs![Libelle])
Me.NomenclatureView.Nodes.Add Key:="Art: " & sKey, Text:=sLib
rs.MoveNext
Wend
End If
rs.Close
' Mise en place des noeuds de niveau 1
' ------------------------------------
Set rs = db.OpenRecordset("SELECT DISTINCT [Article], [Article1],[Statut1], [Libelle1]FROM Nomenclature ORDER BY [Article], [Article1]")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
sKey = Nz(rs![Article]) & ";" & Nz(rs![Article1])
sLib = Nz(rs![Article1]) & " : " & Nz(rs![Statut1]) & " : " & Nz(rs![Libelle1])
Me.NomenclatureView.Nodes.Add "Art: " & Nz(rs![Article]), tvwChild, sKey, Text:=sLib
rs.MoveNext
Wend
End If
rs.Close
' Mise en place des noeuds de niveau 2
' ------------------------------------
Set rs = db.OpenRecordset("SELECT DISTINCT [Article], [Article1], [Article2],[Statut2], [Libelle2]FROM Nomenclature ORDER BY [Article], [Article1], [Article2]")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
sKey = Nz(rs![Article]) & ";" & Nz(rs![Article1]) & ";" & Nz(rs![Article2])
sLib = Nz(rs![Article2]) & " : " & Nz(rs![Statut2]) & " : " & Nz(rs![Libelle2])
If rs![Article2] <> "" Then
Me.NomenclatureView.Nodes.Add Nz(rs![Article]) & ";" & Nz(rs![Article1]), tvwChild, sKey, Text:=sLib
End If
rs.MoveNext
Wend
End If
rs.Close
' Mise en place des noeuds de niveau 3
' ------------------------------------
Set rs = db.OpenRecordset("SELECT DISTINCT [Article], [Article1], [Article2],[Article3],[Statut3], [Libelle3]FROM Nomenclature ORDER BY [Article], [Article1], [Article2],[Article3]")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
sKey = Nz(rs![Article]) & ";" & Nz(rs![Article1]) & ";" & Nz(rs![Article2]) & ";" & Nz(rs![Article3])
sLib = Nz(rs![Article3]) & " : " & Nz(rs![Statut3]) & " : " & Nz(rs![Libelle3])
If rs![Article3] <> "" Then
Me.NomenclatureView.Nodes.Add Nz(rs![Article]) & ";" & Nz(rs![Article1]) & ";" & Nz(rs![Article2]), tvwChild, sKey, Text:=sLib
End If
rs.MoveNext
Wend
End If
rs.Close
' Mise en place des noeuds de niveau 4
' ------------------------------------
Set rs = db.OpenRecordset("SELECT DISTINCT [Article], [Article1], [Article2],[Article3],[Article4],[Statut4], [Libelle4]FROM Nomenclature ORDER BY [Article], [Article1], [Article2],[Article3],[Article4]")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
While Not rs.EOF
If rs![Article4] <> "" Then
sKey = Nz(rs![Article]) & ";" & Nz(rs![Article1]) & ";" & Nz(rs![Article2]) & ";" & Nz(rs![Article3]) & ";" & Nz(rs![Article4])
sLib = Nz(rs![Article4]) & " : " & Nz(rs![Statut4]) & " : " & Nz(rs![Libelle4])
Me.NomenclatureView.Nodes.Add Nz(rs![Article]) & ";" & Nz(rs![Article1]) & ";" & Nz(rs![Article2]) & ";" & Nz(rs![Article3]), tvwChild, sKey, Text:=sLib
End If
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Sub |
Partager