Hi shery,
Here is a non-recursive, top-down technique for populating a treeview control with data from a self-referencing table developed by Sarah Worthen. The code includes a call to the Timer function that you can use during development. You should find the non-recursive approach to be about 2 to 3 times faster than using recursion.
The trick to the technique is to use a little constructive error trapping. As the code loops through the recodset, it attempts to set mNodeParent equal to a node already in the tree whose 'childID'
matches the 'parentID' of the current record. If the matching node isn't present, error 35601 is trapped and processed; if the matching node is present, a new node is added to the tree.
The code is using a classic Employee/Supervisor equi-join scenario:
Table: tblEmployees
------------------
Join: [EmployeeID] <-> [SupervisorID]
Field: EmployeeID
Type: Number
Field: EmpLastNAme
Type: Text
Field: EmpFirstName
Sort: Text
Field: SupervisorID
Sort: Number
Query: EmployeesAndSupervisors
------------------------------
Type: Select Query
Field: SupervisorID
Field: EmployeeID
Field: EmployeeName
Field: SupervisorName
SQL:
SELECT tblEmployees.SupervisorID, tblEmployees.EmployeeID, tblEmployees.EmpLastName AS EmployeeName, tblEmployees_1.EmpLastName AS SupervisorName
FROM tblEmployees LEFT JOIN tblEmployees AS tblEmployees_1 ON tblEmployees.SupervisorID = tblEmployees_1.EmployeeID
WHERE (((tblEmployees.SupervisorID) Is Not Null))
ORDER BY tblEmployees.SupervisorID, tblEmployees.EmployeeID, tblEmployees.EmpLastName;
The recordset opened in the code uses the query as its source.
**Code*************
Option Compare Database
Option Explicit
Private Sub Form_Load()
On Error GoTo Error
'fill the treeview with the records from the table
Dim timStart As Single
Dim timEnd As Single
timStart = Timer
Dim rst As Recordset
Dim db As Database
Dim mNodeParent As node
Dim mNodeChild As node
Dim fExists As Boolean
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEmployeesAndSupervisors")
Application.Echo False
Do Until rst.EOF
'assume the parent node is already in the tree. If not, the next line
'will cause error 35601 - Element Not Found
'Trap the error, and at that point add the missing parent.
Set mNodeParent = TreeView.Nodes("Key_" & rst("SupervisorID"))
Set mNodeChild = TreeView.Nodes.Add( _
relative:=mNodeParent.Index, _
relationship:=tvwChild, _
Key:="Key_" & rst("EmployeeID"), _
Text:=rst("EmployeeName"))
With mNodeChild
'set other properties
End With
rst.MoveNext
Loop 'through recordset
Application.Echo True
For Each mNodeChild In TreeView.Nodes
mNodeChild.Expanded = False
Next
timEnd = Timer
MsgBox "Non-recursive form load took " & timEnd - timStart & " seconds!"
Exit Sub
Error:
Select Case Err.Number
Case 35601 'node does not exist
Set mNodeParent = AddParentNode( _
Key:="Key_" & rst("SupervisorID"), _
Text:=rst("SupervisorName"))
Resume Next
Case 35602 'duplicated node key
'if we are duplicating the key, we have "misfiled" node.
'move the existing node with its children to report to the correct parent
Call AssignNewParent( _
Key:="Key_" & rst("EmployeeID"), _
ParentNodeIndex:=mNodeParent.Index)
Resume Next
Case Else
Application.Echo True
MsgBox Err.Number & ": " & Err.Description
End Select
End Sub
Private Function AddParentNode(Key As String, Text As String) As node
On Error GoTo Error
Dim mNodeParent As node
Set mNodeParent = TreeView.Nodes.Add(, , Key, Text)
With mNodeParent
'other properties
End With
Set AddParentNode = mNodeParent
Exit Function
Error:
Application.Echo True
MsgBox Err.Number & ": " & Err.Description
End Function
Private Sub AssignNewParent(Key As String, ParentNodeIndex As Integer)
On Error GoTo Error
Set TreeView.Nodes(Key).Parent = _
TreeView.Nodes(ParentNodeIndex)
Exit Sub
Error:
Application.Echo True
MsgBox Err.Number & ": " & Err.Description
End Sub
**Code**********
HTH,
Bob
|
