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
| Option Compare Database
Option Explicit
Public Sub loadTreeviewNested(oForm As Access.Form, sName As String)
On Error GoTo err_Handler
Dim tv As MSComctlLib.TreeView
Dim nod As MSComctlLib.Node
Dim odb As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim sFind As String
Dim sBook As String
Set tv = oForm(sName).Object
With tv
.Nodes.Clear
.Font.Size = 12
.Font.Name = "Arial"
strSQL = "SELECT node.category_id, node.category_name, node.lft, node.rgt, (Count(parent.category_name)-1) AS depth " _
& "FROM nested_category AS node, nested_category AS parent " _
& "WHERE node.lft Between [parent].[lft] And [parent].[rgt] " _
& "GROUP BY node.category_id, node.category_name, node.lft, node.rgt, node.lft " _
& "ORDER BY node.lft;"
Set odb = CurrentDb
Set rst = odb.OpenRecordset(strSQL, 4, 512)
sFind = "depth=0"
rst.FindFirst sFind
Do While Not rst.NoMatch
Set nod = .Nodes.Add(, , "K" & rst("category_id") & "|" & rst("lft") & "|" & rst("rgt"), rst("category_name"))
nod.Bold = True
sBook = rst.Bookmark
addChildren tv, nod, rst, rst("lft"), rst("rgt"), rst("depth")
rst.Bookmark = sBook
rst.FindNext sFind
Loop
End With
rst.Close
Set rst = Nothing
Set nod = Nothing
Set tv = Nothing
Sortie:
On Error Resume Next
rst.Close
Set rst = Nothing
Set odb = Nothing
Set nod = Nothing
Set tv = Nothing
Exit Sub
Exit Sub
err_Handler:
fuErr_Handler ("loadTreeviewNested Function")
Resume Sortie
End Sub
Private Sub addChildren(tv As TreeView, nodParent As Node, rst As DAO.Recordset, lLft As Long, lRgt As Long, ByVal lDepth As Long)
On Error GoTo err_Handler
Dim nodX As Node
Dim sBook As String
Dim sFind As String
sFind = "lft>" & lLft & " AND rgt<" & lRgt & " AND depth = " & lDepth + 1
rst.FindFirst sFind
Do While Not rst.NoMatch
Set nodX = tv.Nodes.Add(nodParent, tvwChild, "K" & rst("category_id") & "|" & rst("lft") & "|" & rst("rgt"), rst("category_name"))
sBook = rst.Bookmark
addChildren tv, nodX, rst, rst("lft"), rst("rgt"), rst("depth")
rst.Bookmark = sBook
rst.FindNext sFind
Loop
Sortie:
Exit Sub
err_Handler:
fuErr_Handler ("addChildren Function")
Resume Sortie
End Sub |
Partager