Populating a TreeView control from a recordset without Recursion? (1 Viewer)

[Edgar_]

I was playing with the tree view control and found I can just populate all nodes of a tree view control and then assign a parent to each of them with another pass... You guys see any problem in that method?

I know there's a bunch of threads about this topic by @MajP, but this is likely a different method, I just want to know if you can foresee some problems with it. Tried with recursion, but I didn't like the experience, so I went with another route.

Function GrowTree(rs As Recordset, tvw As TreeView)
    Do While Not rs.EOF
        tvw.nodes.Add , , "k" & rs!division_id, rs!title
        rs.MoveNext
    Loop
   
    rs.MoveFirst
    Do While Not rs.EOF
        If rs!parent_id <> 0 Then
            Set tvw.nodes("k" & rs!division_id).parent = tvw.nodes("k" & rs!parent_id)
        End If
        rs.MoveNext
    Loop
End Function

Should be simple, right?

 

[MajP]

Not required in cases where you can determine what constitutes a root node and guarantee you can sort the rs in exact order to ensure parent records are read before a child record. It would fail if you try to create a node where the parent does not exist yet. I have many cases where that is not possible. Never be able to drag and drop. You are often creating parent records after a child record in the table.
There are plenty of cases that you load non self referencing data and definitely recursion is not needed.

 

[gemma-the-husky]

I may be getting the wrong end of the stick here.

I think @MajP explained that he can manage a recursive structure without having to load it all first, which is a slick thing to do. I don't know if he uses a tree view control to do that. I don't know exactly what a tree view control does, to be honest, but generally speaking I would have thought it's easier to manage recursive structures with recursion. Although I'm told that VBA does provide the use of pointer data types, it's only a roundabout way, isn't it? I couldn't get the hang of it.

It would be much easier if you could move a subtree by simply manipulating a couple of pointers for the subtree root, with something like this, I would have thought, where a node is a record structure including pointers to it's parent and children.

with thisnode
     .parent.thischild is nothing
     .parent = newnode
    newnode.thischild=thisnode
end with

 

[MajP]

I do this recursively in my helper class because it guarantees I can load any data structure into a tree view, as long as I get a single query correct. So if I can get the query correct it takes one line of code to instantiate the class and load the tree. It is universal method that can handle all cases. This allows a use to add a tree view to any db and create a tree view in a matter of minutes.

If you are rolling your own code to load a tree you may or may not need recursion. This demonstrates why you might unless you want to add additional code.
Assume you have employees and who they report to.

EmployeeID	EmpName	ReportsTo
1	Abe	6
2	Betty
3	Charile	2
4	Diane	6
5	Eric	3
6	Fred

Fred
---Abe
---Diane
Betty
----Charlie
----------Eric

If you tried to do this without recursion and load the tree, there is no way to read this in correct order and it will fail without more complex code.
Clearly you cannot sort by employeeID because you will error trying to load the first node
When you add a node you assign it as a root or you add it under another node. When you read Abe and try to load it under Node 6 it fails because node 6 does not exist yet.


You might assume you could avoid this by sorting by ReportsTo

EmployeeID	EmpName	ReportsTo
2	Betty
6	Fred
3	Charlie	2
5	Eric	3

You could try sorting by ReportTo, but still fails without additional code.
In this case you would make it to Eric, but Eric can not be loaded under node 3 because node 3 is not in the tree yet.

However, here is how you could do this without recursion and still be flexible. Never thought about it, but could work well

1. Read records in any order
2. If Record represents a root node (normally I leave the Parent ID null) then create a root node
3. If Record does not represent a root node (there is a reports to) then
4. Check if a parent node exists in tree
5. If parent node already loaded then create node under parent
6. I parent node does not exist create current as root node
complete first pass
7. Read the recordset again and reassign each node parents
I cannot remember if nodes have a visible property but if so you could add and move them hidden if flicker is a problem with the moving.
8. If added and moved while hidden then Loop the nodes collection and unhide

This may be efficient and relatively simple in avoiding recursion, but may be slow due to moving objects in the tree.

For me this likely would not work especially for large Trees. When I build large trees I do something I call "lightload". I do not load all the nodes in order to save time. I show a demo of 20k nodes and this would take a very long time to load. So I only load the visible root nodes (and one additional child node). Then other nodes are loaded as you expand the branch. So on a 20k node tree I might only load a few hundred at start.

 

[MajP]

It would be much easier if you could move a subtree by simply manipulating a couple of pointers for the subtree root, with something like this, I would have thought, where a node is a record structure including pointers to it's parent and children.

You can do exactly that, and this is what is really nice using a Tree view for something like a BOM especially with drag and drop.

Fred
---Abe
---Diane
Betty
----Charlie
----------Eric

Lets say Diane is picking up some of Betty's duties and Charlie moves under Diane. I can drag Charlie under Diane (or some other way to reassign charlie's reportsTo) and update the database so that Charlie's report to changes from 2 to 6. This now looks like this by changing this value.

EmployeeID	EmpName	Reports2
3	Charlie	6

Fred
---Abe
---Diane
-------Charlie
-------------Eric
Betty

There is no real linkage between the records and the nodes (it is not "bound") but when you make changes in the table you make corresponding changes to the nodes parent property.

 

[gemma-the-husky]

@MajP

Well, It's quite easy to manipulate the values in a table. I'm thinking more of holding the tree in memory, and manipulating the pointers to manage the tree. Then you can also maintain multiple sort orders, depending on what you need to do.

Msxml must do that with an XML file for instance.

 

[MajP]

I think we may still be talking the same thing. You could load a tree view and then add, delete, and move node/branches around and not update your table. But if the table does not get updated then you have not accomplished anything.

But you could do this. Do a bunch of manipulations and not make any changes back to the table. Then when you close the treeview you could prompt to save the changes to the table. If you added a node then add a record, delete a node delete a record, reassign a parent node reassign a parent key.

There may be cases you would want to do that. However for a lot of reasons it is simpler to do corresponding actions at the same time. Add a record add a node. Add a node add a record. Delete a node, delete a record. etc.

The Treeview is nice for displaying Hierarchical. Doing this with forms and subforms is almost impossible to make sense of things. Manipulating things add, edit, move would be even more confusing with a standard forms/subforms. This video shows how intuitive it is to build a BOM quickly and easily. Doing these same manipulations with standard access forms would be so difficult to present the user with something understandable.

Looking for some direction.

I am realizing that the Tree View works well for navigation, but would not be our preferred method for input/queries. Too many mouse clicks. That depends on your data. I can demo some structures that adding a node and moving a node would be way faster then any other method. Treeviews work well...

www.access-programmers.co.uk

 

[Edgar_]

It would fail if you try to create a node where the parent does not exist yet

Before reading the rest of the thread, I want to clarify that the quoted above is the reason I'm doing this in two steps with the same recordset.
The first "pass" adds one node for each record. The second pass assigns a parent to each record, if they have one.
I only tested against two scenarios:
1. when parents always appear before their children in the recordset
2. when children may appear before their parent does in the recordset

There are plenty of cases that you load non self referencing data and definitely recursion is not needed.

I'm talking about self referencing data exclusively.

Never be able to drag and drop

I haven't inspected what methods the treeview control has for the implementation of drag and drop gestures, but I'm guessing it's something similar to the step where I simply assign a parent, is that not the case?

 

[The_Doc_Man]

For my genealogy DB, recursion WAS necessary because of aunts and uncles and cousins. Turned out to also be necessary for cases of divorce and for multiple sequential marriages due to death of a spouse. WAY too many pointers to make ME happy - but I did manage to get it working.

The "tree view" never became an issue for me, but I might be tempted to try it sometime.

 

[Edgar_]

I think @MajP explained that he can manage a recursive structure without having to load it all first, which is a slick thing to do

I guess you add parent nodes and, on some event like double click, then you call the children of that node? in large recordsets, it would be like limiting the results to a few and keeping a counter to know which records to add when you click load more or something similar like it's done sometimes to "lazy load" data? Since it's based on query results, I'm guessing I can implement that with this method too (I could be wrong, of course).

I would have thought it's easier to manage recursive structures with recursion. Although I'm told that VBA does provide the use of pointer data types, it's only a roundabout way, isn't it? I couldn't get the hang of it.

It would be much easier if you could move a subtree by simply manipulating a couple of pointers for the subtree root, with something like this, I would have thought, where a node is a record structure including pointers to it's parent and children.

No idea of how the TreeView does what it does, but low level is never easy in VBA... as far as I know...

EDIT: I built one with JS, but it wasn't a TreeView per se, more like a json viewer that you could expand on demand... that I do know how it works, JS has nice features and libraries to do this very easy, but VBA is another beast.

 

[Edgar_]

If you tried to do this without recursion and load the tree, there is no way to read this in correct order and it will fail without more complex code.

Mind elaborating why in your recordset Fred comes before Betty? Because I did test my method on your example and I have no problem:

Of course, I had to sort EmpName in descending order to get Fred before Betty, which appear in reverse in the given table:

Private Sub Form_Load()
    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM employees1 ORDER BY EmpName DESC")
    
    Do While Not rs.EOF
        tv.Nodes.Add , , "k" & rs!EmployeeID, rs!EmpName
        rs.MoveNext
    Loop
    
    rs.MoveFirst
    Do While Not rs.EOF
        If rs!ReportsTo <> 0 Then
            Set tv.Nodes("k" & rs!EmployeeID).Parent = tv.Nodes("k" & rs!ReportsTo)
        End If
        rs.MoveNext
    Loop
    tv.LineStyle = tvwRootLines
End Sub

I have not tested this on a larger recordset, I'm testing here on the fly so far.

 

[MajP]

Before reading the rest of the thread, I want to clarify that the quoted above is the reason I'm doing this in two steps with the same recordset.
The first "pass" adds one node for each record. The second pass assigns a parent to each record, if they have one

Yes, that is what I think I said. Two pass will work fine. Like I said, I have never done it and with a big tree you might get flicker as you move things. If you can do this without screen updating, or hiding until done that may solve any potential problems.

I haven't inspected what methods the treeview control has for the implementation of drag and drop gestures, but I'm guessing it's something similar to the step where I simply assign a parent, is that not the case

You do not have to do anything to the tree, but you likely want to update your table. The drag and drop allows you to get the drag node and the drop node. With that you can update your table so that that the dragged nodes new parent ID is the drop node ID

I guess you add parent nodes and, on some event like double click, then you call the children of that node? in large recordsets, it would be like limiting the results to a few and keeping a counter to know which records to add when you click load more or something similar like it's done sometimes to "lazy load" data? Since it's based on query results, I'm guessing I can implement that with this method too (I could be wrong, of course).

You could easily. The reason to add one child record is so the Plus sign will show up on nodes that have children. If you only added the root nodes you would get no plus signs on nodes you can expand.
If you where doing this when you expand a node it would be easy. You would have the ID of the parent node (the node to expand) and simply load all children to that node. The only trick is there is one child node already added (so you get the plus sign). You just need to avoid adding that again.

Mind elaborating why in your recordset Fred comes before Betty? Because I did test my method on your example and I have no problem:

Because I typed it by hand into the table for the example. Notional data.

 

[Edgar_]

For my genealogy DB, recursion WAS necessary because of aunts and uncles and cousins. Turned out to also be necessary for cases of divorce and for multiple sequential marriages due to death of a spouse. WAY too many pointers to make ME happy - but I did manage to get it working.

The "tree view" never became an issue for me, but I might be tempted to try it sometime.

Mind sharing some example data to see if it fails there?

 

[MajP]

One thing that is very useful is the Tree sort order. The nodes are in order
Fred 1
---Abe 2
---Diane 3
Betty 4
----Charlie 5
----------Eric 6

You can loop the nodes and write this order back to the table. Then the next time I load I order by sort order.
This way I can move Diane up (using the tree) above Abe if for example I want to move team leads to the top. Also you can determine every nodes Level. This is also very helpful to persist in back to the table. Then you can use the table to do queries by level.

Then you can do things like get the count of 1st level managers.

 

[Edgar_]

Thanks, MajP. I'll see when I can expand on this idea, I doubt it's faster than recursion, but won't know until I test. All I know is it loads fine and the code is super simple. IF recursion is faster, the simplicity of this is probably worth the lost milliseconds.

 

[MajP]

I doubt it's faster than recursion, but won't know until I test. All I know is it loads fine and the code is super simple. IF recursion is faster, the simplicity of this is probably worth the lost milliseconds.

I do not think it really matters if you did this sequentially or recursively, because any difference is overshadowed in the time it takes to physically create the nodes. If you are doing a light load that will be orders of magnitude faster then building all the nodes. If it is a few hundred nodes should not be a problem.
When testing this in the past I did the recursive code but commented out actually creating the nodes. The code executed in around a second. Then I had it create the nodes and it took around a minute. Also from what I observed, moving a node is far faster than building the node. The second pass likely will be much faster than the first and not "double" the loading process.