MajP
You've got your good things, and you've got mine.
- Local time
- Today, 13:59
- Joined
- May 21, 2018
- Messages
- 8,908
I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to span something like a file directory with an unknown amount of levels and branches. Common in doing things like Family trees and working with assemblies sub assemblies or tasks and sub tasks. Tree Views are a good way to show these Hierarchies. Think of an outline with multiple levels.
Recursion
So here is a simple example to demo recursion. The procedure is told how many times to loop itself and the counter is incremented each time the procedure is called. It simply adds 10 to the starting value each time it calls itself. Recursion always needs a way to check if it should call itself again or stop (does not call itself). Recursion if often very inefficient and resource heavy, but for certain data structure it may be the only way. (Note the below demo could obviously be done without recursion).
test it
so the output is:
Value 50 Counter 0
Value 60 Counter 1
Value 70 Counter 2
Value 80 Counter 3
Value 90 Counter 4
Value 100 Counter 5
Final 110
Hierarchical data
When working with hierarchical data normally each record references a parentID in the same table. This is often called a "Self Referencing" table.
To get this into a Tree View or display the hierarchy you start at the first Root node then recursively find the children. In the demo below I use a tree view to span the nodes to build the tree. To see how this is done look at the class module TreeViewForm.
Tree Views
Working with treeviews is pretty code heavy, but I have created a custom class to make this very easy. If you build a query precisely as instructed, you can then build a tree view with only a couple lines of code. The class also ties database information (Primary Keys) to the nodes making it much easier to work between the treeview and the data. The TreeView is "pseudo-bound" to the data. Read the instruction in the TreeViewForm class module.
I have included Multiple Demos showing different data structures and features. The E2E demo has about all the bells and whistles I added after working with DGreen on a specific solution. However to simply load your own tree view requires you to build a query and a single line of code.
1. Load from common query
2. Drag and drop of nodes and update database
3. Add, edit delete record in database and update tree
4. Delete, edit node and update database
5. Move node up and down in level and update sort order
6. Apply icons to specific records
7. dynamically load icons from folder at runtime
8 Right click on node with pop up command bar
9. Right click off node with different command bar
10. Expand and collapse tree and branch
11. Node selected to synch subform
12. Node double click to add, edit, delete
13. Auto level creates outline numbering
14. Save sort
15. Autolevel levels
16. Full load of nodes or light load and add nodes when expanded
Also included is a demo of Tree Views using MS Forms instead of the Active X.
The Span Files and Directory show a recursive span of a file system logging the information with another recursion to load the tree view.
I have also added to this capability with a class module for loading this Tree View.
Other Threads on TreeViews
Other Threads on general Recursion
Part Two to this thread focusing on recursion and shows how to build a simulated tree view with only a listbox.
Recursion
So here is a simple example to demo recursion. The procedure is told how many times to loop itself and the counter is incremented each time the procedure is called. It simply adds 10 to the starting value each time it calls itself. Recursion always needs a way to check if it should call itself again or stop (does not call itself). Recursion if often very inefficient and resource heavy, but for certain data structure it may be the only way. (Note the below demo could obviously be done without recursion).
Code:
Public Sub RecursiveAdd10(HowManyTimes As Long, Optional Start As Long = 0, Optional Counter As Long = 0)
If HowManyTimes = Counter Then
Debug.Print "Final " & Start
Exit Sub
End If
Debug.Print "Value " & Start & " Counter " & Counter
Start = Start + 10
Counter = Counter + 1
RecursiveAdd10 HowManyTimes, Start, Counter
End Sub
Code:
Public Sub TestRecursive()
'add 10 5 times
RecursiveAdd10 6, 50
End Sub
Value 50 Counter 0
Value 60 Counter 1
Value 70 Counter 2
Value 80 Counter 3
Value 90 Counter 4
Value 100 Counter 5
Final 110
Hierarchical data
When working with hierarchical data normally each record references a parentID in the same table. This is often called a "Self Referencing" table.
This would give the structureNodeID ParentID
1
2 1
3 1
4 2
5 4
6 3
7
Show table1
--2
----4
------5
--3
----6
7
To get this into a Tree View or display the hierarchy you start at the first Root node then recursively find the children. In the demo below I use a tree view to span the nodes to build the tree. To see how this is done look at the class module TreeViewForm.
Tree Views
Working with treeviews is pretty code heavy, but I have created a custom class to make this very easy. If you build a query precisely as instructed, you can then build a tree view with only a couple lines of code. The class also ties database information (Primary Keys) to the nodes making it much easier to work between the treeview and the data. The TreeView is "pseudo-bound" to the data. Read the instruction in the TreeViewForm class module.
I have included Multiple Demos showing different data structures and features. The E2E demo has about all the bells and whistles I added after working with DGreen on a specific solution. However to simply load your own tree view requires you to build a query and a single line of code.
1. Load from common query
2. Drag and drop of nodes and update database
3. Add, edit delete record in database and update tree
4. Delete, edit node and update database
5. Move node up and down in level and update sort order
6. Apply icons to specific records
7. dynamically load icons from folder at runtime
8 Right click on node with pop up command bar
9. Right click off node with different command bar
10. Expand and collapse tree and branch
11. Node selected to synch subform
12. Node double click to add, edit, delete
13. Auto level creates outline numbering
14. Save sort
15. Autolevel levels
16. Full load of nodes or light load and add nodes when expanded
Also included is a demo of Tree Views using MS Forms instead of the Active X.
The Span Files and Directory show a recursive span of a file system logging the information with another recursion to load the tree view.
Other Threads on TreeViews
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
Solved - TreeView control
Dear All i m sorry to post this topic but i spend all evening of the last 2 weeks without get any good results. i m trying to add nodes to the Tree view control on Access, but basically i have 2 cases: 1 - if i stop to the first level of nodes i don't have any issue but when i go to the...
www.access-programmers.co.uk
Solved - Help to build a treeview
But I don't know like you. If you prefer, act freely and spontaneously. I still have a lot to learn in this world of programming
www.access-programmers.co.uk
Solved - Majp Treeview
I use the treeview control extensively. Thank you very much, Majp! However, I'm stumped at how to select the nodes I want. I use the PK to select individual records, but using it for a print selection, I'd like to be able to select the top levels and all under them would be selected. Or select a...
www.access-programmers.co.uk
Other Threads on general Recursion
Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....
Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
www.access-programmers.co.uk
link record to another record in the same table
Good to finally get some idea of what the business process actually is. Reward seems a bit upside down - those doing most of the work get least reward. And no chance of promotion since you said movements don’t happen still confused about when someone leaves since when someone joins, the...
www.access-programmers.co.uk
Is it possible to create a recursive sum in ms access by sql query or vba
t is impossible that there is no solution to this problem. In any way, I want to activate this query update accounts without disrupting the user Is it reasonable that not all Access programmers have a solution? I will say it one more time, not sure what is not clear. If you are stuck on doing...
www.access-programmers.co.uk
Question - calculate inbreeding
I do have a difficult project that is similar to what you are on working, that is why I approach you. OK sounds interesting. I would just post it and people will reply. It may be an area that someone has expertise. Unless it is really COI because now I know more about that than ever wanted...
www.access-programmers.co.uk
Part Two to this thread focusing on recursion and shows how to build a simulated tree view with only a listbox.
Hierarchical Data, Self Referencing Tables, and Recursion Part 2
This is part two to this thread. https://www.access-programmers.co.uk/forums/threads/hierarchical-data-recursion-tree-views-and-a-custom-class-to-assist.309753/ However the original thread dealt more specifically with tree views. This does not. The above threads has many links to other treeview...
www.access-programmers.co.uk
Attachments
Last edited: