sub treeview (1 Viewer)

[yahoo]

Hi there

Im trying to build a subsystem treeview. So I'd like to build a treeview For selected node's children only. I thought I could do that by building hierarchy levels in another field called 'Sublevel' in my treeview table. So macro works like: clear all existing sublevels (not done yet), add 1 for selected node and + 1 for every level below. What I struggle with is looping through children and updating table for each row.
See line 32 (Set NodSelected.Child = NodSelected.Child.Next)- I thought I could do that using child.next but when I run it I get an error message saying that cant edit as it's read only.
Any thoughts or other ideas?

Code just for first couple levels below:

Private Sub BuildSystemTree_Click()
Dim NodSelected As MSComctlLib.Node, aaa As MSComctlLib.Node, bbb As MSComctlLib.Node
Dim a As String, sqlly1 As String, sqlly2 As String, sqlly3 As String
Dim i As Long
Dim PS As ADODB.Recordset
Dim tag As String
DoCmd.SetWarnings False
Set NodSelected = Me.TreeView.SelectedItem
'Use a reference to the Parent node
With NodSelected
    getTag tag, NodSelected

    sqlly1 = "UPDATE TreeLevels SET Sublevel = '1' WHERE ([Functional location]) = '" & tag & "'"
    DoCmd.RunSQL sqlly1
    getTag tag, .Child
    'Loop through the children
    Next
    For i = 1 To .Children
        getTag tag, .Child
        sqlly2 = "UPDATE TreeLevels SET Sublevel = '2' WHERE ([Functional location]) = '" & tag & "'"
        DoCmd.RunSQL sqlly2
        'Determine if the child has a sub node
            If .Child.Children > 0 Then
            
            For j = 1 To .Child.Children
                getTag tag, .Child.Child
                sqlly2 = "UPDATE TreeLevels SET Sublevel = '3' WHERE ([Functional location]) = '" & tag & "'"
                DoCmd.RunSQL sqlly2
                .Child.Child = .Child.Child.Next
            Next
        End If
        Set NodSelected.Child = NodSelected.Child.Next
    Next
End With

DoCmd.SetWarnings True
End Sub

 

[MarkK]

I don't get your post. You say you want to build a treeview but to do so you need to use the Add method of the Treeview.Nodes collection, which doesn't appear anywhere. By contrast, your code appears to attempt to enumerate the nodes in an existing treeview to update a table.

And as you observe, you cannot assign a node to the Child property of a Node. Again, to create the Child, you use the Nodes.Add method, and once it is created it is read-only. Or you can remove it (and all the other siblings) and re-add it.

So maybe clarify your purpose?

 

[yahoo]

thats sorted now thanks to one of your previous post (dont have rights to post links)
I already have a nice treview built (for all data), but I want to allow user to clear existing tree and create a new one with selected node as a root node.

I build my trees basing on hierarchy levels. So first step is assigning levels (code below) for a new tree in a sublevel field. Tree will be built in the second stage using another function which looks only at rows with levels filled in in 'sublevel'. Couldnt think of better logic for delivering tree for only part of my data.

Private Sub BuildSystemTree_Click()
Dim NodSelected As MSComctlLib.node, b As MSComctlLib.node, c As MSComctlLib.node, d As MSComctlLib.node
Dim a As String, sqlly1 As String, sqlly2 As String, sqlly3 As String
Dim i As Long
Dim PS As ADODB.Recordset
Dim tag As String
DoCmd.SetWarnings False
Set NodSelected = Me.TreeView.SelectedItem
'Use a reference to the Parent node
DoCmd.RunSQL "Update [Treelevels] SET [sublevel] = ''"
With NodSelected
    getTag tag, NodSelected
    'TraverseChildren NodSelected, tag
    sqlly1 = "UPDATE TreeLevels SET Sublevel = '1' WHERE ([Functional location]) = '" & tag & "'"
    DoCmd.RunSQL sqlly1
    Set b = .Child
    'getTag tag, b
    'Loop through the children

    For i = 1 To .Children
        getTag tag, b
        sqlly2 = "UPDATE TreeLevels SET Sublevel = '2' WHERE ([Functional location]) = '" & tag & "'"
        DoCmd.RunSQL sqlly2
        'Determine if the child has a sub node
        If b.Children > 0 Then
            'Recurse this method
            Set c = b.Child
            For j = 1 To b.Children
                getTag tag, c
                sqlly2 = "UPDATE TreeLevels SET Sublevel = '3' WHERE ([Functional location]) = '" & tag & "'"
                DoCmd.RunSQL sqlly2
                'Determine if the child has a sub node
                If c.Children > 0 Then
                    Set d = c.Child
                    'Recurse this method
                    For k = 1 To c.Children
                        getTag tag, d
                        sqlly2 = "UPDATE TreeLevels SET Sublevel = '4' WHERE ([Functional location]) = '" & tag & "'"
                        DoCmd.RunSQL sqlly2
                        'Determine if the child has a sub node
                        If d.Children > 0 Then
                            Set e = d.Child
                            For l = 1 To d.Children
                                getTag tag, e
                                sqlly2 = "UPDATE TreeLevels SET Sublevel = '5' WHERE ([Functional location]) = '" & tag & "'"
                                DoCmd.RunSQL sqlly2
                                'Determine if the child has a sub node
                                If e.Children > 0 Then
                                    Set f = e.Child
                                    For m = 1 To e.Children
                                        getTag tag, f
                                        sqlly2 = "UPDATE TreeLevels SET Sublevel = '6' WHERE ([Functional location]) = '" & tag & "'"
                                        DoCmd.RunSQL sqlly2
                                        'Determine if the child has a sub node
                                        If f.Children > 0 Then
                                            Set g = f.Child
                                            For o = 1 To f.Children
                                                getTag tag, g
                                                sqlly2 = "UPDATE TreeLevels SET Sublevel = '7' WHERE ([Functional location]) = '" & tag & "'"
                                                DoCmd.RunSQL sqlly2
                                                If g.Children > 0 Then
                                                    Set h = g.Child
                                                    For p = 1 To g.Children
                                                        getTag tag, h
                                                        sqlly2 = "UPDATE TreeLevels SET Sublevel = '8' WHERE ([Functional location]) = '" & tag & "'"
                                                        DoCmd.RunSQL sqlly2
                                                        Set h = h.Next
                                                    Next
                                                End If
                                                Set g = g.Next
                                            Next
                                        End If
                                        Set f = f.Next
                                    Next
                                End If
                                Set e = e.Next
                            Next
                        End If
                        Set d = d.Next
                    Next
                End If
                Set c = c.Next
            Next
        End If
        Set b = b.Next
    Next
End With
subTree
DoCmd.SetWarnings True
End Sub

 

[MarkK]

With treeviews you commonly use recursion, which is what you call it when a subroutine calls itself. See if this makes any sense to you . . .

Private Sub BuildSystemTree_Click()
    DoCmd.RunSQL "Update [Treelevels] SET [sublevel] = ''"
    CreateNodes Me.treeview.SelectedItem
End Sub

Private Sub CreateNodes(nd As MSComctlLib.Node, Optional ByVal Depth As Integer)
    Dim nc As MSComctlLib.Node
    Depth = Depth + 1          [COLOR="Green"] 'increment the depth[/COLOR]
    DoCmd.RunSQL "UPDATE TreeLevels SET Sublevel = '" & Depth & "' WHERE ([Functional location]) = '" & GetTag(nd) & "'"
    Set nc = nd.Child           [COLOR="Green"]'get the first child node[/COLOR]
    For i = 1 To nd.Children    [COLOR="Green"]'if nd has no children, this block never executes[/COLOR]
        CreateNodes nc, Depth   [COLOR="Green"]'this routine calls itself -> recursion[/COLOR]
        Set nc = nc.Next       [COLOR="Green"] 'get the next sibling[/COLOR]
    Next
End Sub

. . . or Google recursion for more info.

I haven't tested it so I'm sure it won't work without some tweaks, but do you see what's going on and how it eliminates the need for your nested loops and all the variables for each loop? It's like a loop except the loop is terminated when a new call doesn't occur, which in this case happens when a node has no children. In that case control of execution is passed back to the calling instance of the routine, but recursion with a treeview can save you a ton of code.

Cheers,