G
Guest
Thanks for taking the time to read my question.
I am using the sample code provided by Microsoft for Tree View found here.
(http://support.microsoft.com/?kbid=209898). It works great.
My problem is I need to change the code so that it works with my record set.
I can get the date (top of tree) to show up, but I can't get any of the sub
records to get applied. I've spent a lot of time trying to figure this out.
I've attached my code and data below. I get through the code with no errors,
but also no real results.
Thanks,
Brad
Code:
'==================================================================
'This procedure populates the TreeView control when the form opens.
'==================================================================
Private Sub Form_Current()
On Error GoTo ErrForm_Load
Dim db As Database
Dim rst As Recordset, qdf As QueryDef
Dim nodCurrent As Node, nodRoot As Node
Dim objTree As TreeView
Dim strText As String, bk As String
Set db = CurrentDb
'Open the Employees table.
Set qdf = db.QueryDefs("qryBudgetTree")
qdf.Parameters(0).Value = [Forms]![frmtblBudgetTest]![tblBudgetID]
Set rst = qdf.OpenRecordset()
'Create a reference to the TreeView Control.
Set objTree = Me!xTree.Object
'Find the first employee who is a supervisor.
rst.FindFirst "[ParentID] Is Null"
'Build the TreeView list of supervisors and their employees.
Do Until rst.NoMatch
'Extract the supervisor's name.
If rst![Amount] = 0 Then
strText = rst![Category]
Else
strText = rst![Category] & " = " & rst![Amount]
End If
'Add a root level node to the tree for the supervisor.
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!ID, _
strText)
Debug.Print nodCurrent
'Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
'Run a recursive procedure to add all the child nodes
'for employees who report to this supervisor.
AddChildren nodCurrent, rst
'Return to your placeholder.
rst.Bookmark = bk
'Find the next supervisor.
rst.FindNext "[ParentID] Is Null"
Loop
ExitForm_Load:
Exit Sub
ErrForm_Load:
MsgBox Err.Description, vbCritical, "Form_Load"
Resume ExitForm_Load
End Sub
'===================================================================
'This procedure adds child nodes to the tree for all employees who
'report to a particular supervisor, and calls itself recursively
'to add child nodes for all other employees they supervise.
'
'Note that this procedure accepts the open Employees recordset by
'reference so you do not have to open a new recordset for each call.
'===================================================================
Sub AddChildren(nodBoss As Node, rst As Recordset)
On Error GoTo ErrAddChildren
Dim nodCurrent As Node
Dim objTree As TreeView
Dim strText As String, bk As String
'Create a reference to the TreeView control.
Set objTree = Me!xTree.Object
'Find the first employee who reports to the supervisor for this node.
Debug.Print "[ParentID] ='" & Mid(nodBoss.Key, 2) & "'"
Debug.Print nodBoss.Key
rst.FindFirst "[ParentID] ='" & Mid(nodBoss.Key, 2) & "'"
'Build the list of employees who report to this supervisor.
Debug.Print nodBoss.Key
Do Until rst.NoMatch
'Extract the employee's name.
If rst![Amount] = 0 Then
strText = rst![Category]
Else
strText = rst![Category] & " = " & rst![Amount]
End If
'Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodBoss, tvwChild, "a" & _
rst!ID, strText)
Debug.Print nodCurrent
'Save your place in the recordset.
bk = rst.Bookmark
'Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent, rst
'Return to your place in the recordset and continue to search.
rst.Bookmark = bk
'Find the next employee who reports to this supervisor.
Debug.Print "[ParentID]=" & Mid(nodBoss.Key, 2) & "'"
rst.FindNext "[ParentID]='" & Mid(nodBoss.Key, 2) & "'"
Loop
ExitAddChildren:
Exit Sub
ErrAddChildren:
MsgBox "Can't add child: " & Err.Description, vbCritical, _
"AddChildren(nodBoss As Node) Error:"
Resume ExitAddChildren
End Sub
'---------------------------------------------
Data:
ID Category Amount ParentID
B3 01-Jan-2006 $0.00
BD2 Expenses $0.00 B3
BD3 Utilities $0.00 B3
BSD3 Building Fund $55.00 BD2
BSD4 Food $50.00 BD2
BSD5 Janitorial Expenses $22.00 BD2
BSD6 Electricity $55.00 BD3
BSD7 Natural Gas $99.00 BD3
BSD8 Water $200.00 BD3
--------------------------------------------------------
So Expenses and Utilities are sub records of 01-Jan-2006
Electricity, Natural Gas, and Water are sub records of Utilities.
Thanks,
Brad
I am using the sample code provided by Microsoft for Tree View found here.
(http://support.microsoft.com/?kbid=209898). It works great.
My problem is I need to change the code so that it works with my record set.
I can get the date (top of tree) to show up, but I can't get any of the sub
records to get applied. I've spent a lot of time trying to figure this out.
I've attached my code and data below. I get through the code with no errors,
but also no real results.
Thanks,
Brad
Code:
'==================================================================
'This procedure populates the TreeView control when the form opens.
'==================================================================
Private Sub Form_Current()
On Error GoTo ErrForm_Load
Dim db As Database
Dim rst As Recordset, qdf As QueryDef
Dim nodCurrent As Node, nodRoot As Node
Dim objTree As TreeView
Dim strText As String, bk As String
Set db = CurrentDb
'Open the Employees table.
Set qdf = db.QueryDefs("qryBudgetTree")
qdf.Parameters(0).Value = [Forms]![frmtblBudgetTest]![tblBudgetID]
Set rst = qdf.OpenRecordset()
'Create a reference to the TreeView Control.
Set objTree = Me!xTree.Object
'Find the first employee who is a supervisor.
rst.FindFirst "[ParentID] Is Null"
'Build the TreeView list of supervisors and their employees.
Do Until rst.NoMatch
'Extract the supervisor's name.
If rst![Amount] = 0 Then
strText = rst![Category]
Else
strText = rst![Category] & " = " & rst![Amount]
End If
'Add a root level node to the tree for the supervisor.
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!ID, _
strText)
Debug.Print nodCurrent
'Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
'Run a recursive procedure to add all the child nodes
'for employees who report to this supervisor.
AddChildren nodCurrent, rst
'Return to your placeholder.
rst.Bookmark = bk
'Find the next supervisor.
rst.FindNext "[ParentID] Is Null"
Loop
ExitForm_Load:
Exit Sub
ErrForm_Load:
MsgBox Err.Description, vbCritical, "Form_Load"
Resume ExitForm_Load
End Sub
'===================================================================
'This procedure adds child nodes to the tree for all employees who
'report to a particular supervisor, and calls itself recursively
'to add child nodes for all other employees they supervise.
'
'Note that this procedure accepts the open Employees recordset by
'reference so you do not have to open a new recordset for each call.
'===================================================================
Sub AddChildren(nodBoss As Node, rst As Recordset)
On Error GoTo ErrAddChildren
Dim nodCurrent As Node
Dim objTree As TreeView
Dim strText As String, bk As String
'Create a reference to the TreeView control.
Set objTree = Me!xTree.Object
'Find the first employee who reports to the supervisor for this node.
Debug.Print "[ParentID] ='" & Mid(nodBoss.Key, 2) & "'"
Debug.Print nodBoss.Key
rst.FindFirst "[ParentID] ='" & Mid(nodBoss.Key, 2) & "'"
'Build the list of employees who report to this supervisor.
Debug.Print nodBoss.Key
Do Until rst.NoMatch
'Extract the employee's name.
If rst![Amount] = 0 Then
strText = rst![Category]
Else
strText = rst![Category] & " = " & rst![Amount]
End If
'Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodBoss, tvwChild, "a" & _
rst!ID, strText)
Debug.Print nodCurrent
'Save your place in the recordset.
bk = rst.Bookmark
'Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent, rst
'Return to your place in the recordset and continue to search.
rst.Bookmark = bk
'Find the next employee who reports to this supervisor.
Debug.Print "[ParentID]=" & Mid(nodBoss.Key, 2) & "'"
rst.FindNext "[ParentID]='" & Mid(nodBoss.Key, 2) & "'"
Loop
ExitAddChildren:
Exit Sub
ErrAddChildren:
MsgBox "Can't add child: " & Err.Description, vbCritical, _
"AddChildren(nodBoss As Node) Error:"
Resume ExitAddChildren
End Sub
'---------------------------------------------
Data:
ID Category Amount ParentID
B3 01-Jan-2006 $0.00
BD2 Expenses $0.00 B3
BD3 Utilities $0.00 B3
BSD3 Building Fund $55.00 BD2
BSD4 Food $50.00 BD2
BSD5 Janitorial Expenses $22.00 BD2
BSD6 Electricity $55.00 BD3
BSD7 Natural Gas $99.00 BD3
BSD8 Water $200.00 BD3
--------------------------------------------------------
So Expenses and Utilities are sub records of 01-Jan-2006
Electricity, Natural Gas, and Water are sub records of Utilities.
Thanks,
Brad