Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

[MajP]

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).

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

test it

Public Sub TestRecursive()
  'add 10 5 times
  RecursiveAdd10 6, 50
End Sub

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.

NodeID ParentID
1
2 1
3 1
4 2
5 4
6 3
7

This would give the structure

1
--2
----4
------5
--3
----6
7

Show 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.

Excel, Access: An MSForms (all VBA) treeview

jkp-ads.com: Treeview control

jkp-ads.com

I have also added to this capability with a class module for loading this 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

 

[The_Doc_Man]

I also had to deal with recursion after I retired because of getting involved with Ancestry.COM and their GEDCOM files, which are flat-file representations of family tree data. I can tell you unequivocally that logically building / populating the family tree was a mess - but it was even crazier to try to analyze it. Still, I got it working until some yammerhead on the Ancestry site screwed up his tree and then THIS yammerhead (ME) was tired one night and imported a screwy tree segment without checking it thoroughly.

Let me tell you, recursion of family trees really doesn't work well when someone gets a generation mixed up and makes xxx Jr the parent of xxx Sr. Fortunately, I was familiar enough with recursion to have a secondary cut-off built-in - a flag that says "You've been through this person's node X number of times." Let's just say that recursion is not for the squeamish.

 

[dgreen]

As I'm trying to integrate with my existing database, here's the 1st issue when I compile.

"User defined not defined." Also does it matter if I'm using Access 2019 32 bit for any of the code?

Set tvw = New TreeviewForm

Looking at the TreeView class in my database it is MSComctlLib.TreeCtrl.2. Different from yours as COMCTL.TreeCtrl.2.

 

[MajP]

"User defined not defined." Also does it matter if I'm using Access 2019 32 bit for any of the code?

Thanks, Good point. The Treeview is part of the MS common controls 6.0 active X. You likely have to add this reference. Click references, browse, and select OCX. Easy to find it is in all capitlas.

 

[dgreen]

@MajP query1 error on opening .sort? How did the path get populated in the t_E2E?

 

[dgreen]

So, is the version you've got me using an older version?

I do have the common controls as a reference, so I'm not sure what's next????


Class: COMCTL.TreeCtrl.1
Reference: Microsoft Windows Common Controls 5.0 (SP2)
c:\windows\system32\comctl32.ocx (version: 6.00.8105)
Class: MSComctlLib.TreeCtrl.2
Reference: Microsoft Windows Common Controls 6.0 (SP6)
c:\windows\system32\mscomctl.ocx (version: 6.01.9545)

Thanks, Good point. The Treeview is part of the MS common controls 6.0 active X. You likely have to add this reference. Click references, browse, and select OCX. Easy to find it is in all capitlas.

View attachment 79301

 

[Dreamweaver]

@MajP I haven't played with this control so will download your example and add it to my project management system Thanks looks really interesting

 

[dgreen]

@MajP Did your code create the Level and Path fields in my table?

 

[MajP]

@MajP Did your code create the Level and Path fields in my table?

Yes that was the point of the first code. You have to add the fields. All the code is there. The MajPsort module is the code that will do the sorting.

 

[MajP]

Let me tell you, recursion of family trees really doesn't work well when someone gets a generation mixed up and makes xxx Jr the parent of xxx Sr

@The_Doc_Man Not quite that bad, no one was their own parent. But I helped on a very interesting thread

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

I did the calculations and assumed my results were wrong. In the first pedigree the Father was also the Grand Father and the current parent. Gross! I thought my code was incorrect, but I forgot these were breeding birds and not people. This was not West Virginia. The calculation was mathematically interesting determining how much of this inbreeding occurs, and gives a value for how in-bred any bird is. Obviously less is better, but at the same time you want to breed your prize birds as many times.

 

[dgreen]

@MajP I've taken the code, queries you've built and copied to a new Access database. I added 2 new columns to the t_E2E (Path and Level) but left the values blank. I've linked all the code on frmE2E to the VBA (Form - on Open, on Close, Buttons for the Expand, Collapse, etc...). My references match the database you've provided me.

BUT

When I open and close frmE2E I'm getting an error that a user-defined type hasn't been defined. I'm stuck... See attached, to hopefully show that I'm not going crazy.

 

[The_Doc_Man]

MajP - some years ago, not less than 10 I think, we had a thread from a pigeon breeder for whom having his birds do a little inbreeding was normal. Supposedly comes from various birds where a flock has an Alpha Male. It is not uncommon for the Alpha to go after his own daughters and granddaughters. Happens among chickens, certainly. Also happens among certain herds of herbivores. And carnivores too, like wolf packs and lion prides. Been known to happen among humans only rarely.

 

[dgreen]

Disregard. I pulled in the wrong VBA code for the treeview.

 

[dgreen]

It's working now. Can expand and collapse the tree and branches. The subform is linked and updates based on the treeview value clicked.
Open askers:
1) How to get the sort sequence of a query to match the treeview sequence?
2) Click a record and move it up or down. Right now drag and drop puts it at the top of the group when you drop onto the parent.
3) Linking an image to a node (pictures stored on the user's computer folder)
4) Using a search box to find a value and have the treeview expand to it.

 

[dgreen]

@MajP again thank you for getting me this much further.
@The_Doc_Man great side bar conversation.

 

[MajP]

1) How to get the sort sequence of a query to match the treeview sequence?
You can do that on the close of the form, by looping the node collection. I will let you try, but if you cannot figure it out come back and I will put the code in. But if you loop the nodes of a treeview they will be in the Node Sort order, and then just do an update query. You will have the nodepk and the sort order

2) Click a record and move it up or down. Right now drag and drop puts it at the top of the group when you drop onto the parent.
I will have to look at that one.
Do not know off the top of my head. I will give it a try.
In the past I probably cheated and sorted them the way I wanted in a query and then reloaded the Tree.

3) Linking an image to a node (pictures stored on the user's computer folder)
You do this using an image list. I did not code this into my class because it is a PIA I thought. Here is a video. If you get good at it, school me up. I thought it was a pain, the video says it is easy

4) Using a search box to find a value and have the treeview expand to it.
TreeView1.Nodes(key).Selected = True
We gave each node a key. The key is a combination of the "identifier" and PK. I provide a. So if you provide the key you can get a node you want. Then set selected to true.

Public Function getNode(strKey As String) As Node
On Error GoTo errLable
Set getNode = Me.Nodes(strKey)
Exit Function
errLable:
If Err.Number = 35601 Then
MsgBox "No Node exists with a key of " & strKey
Else
MsgBox Err.Number & " " & Err.Description
End If
End Function

 

[MajP]

Here is the search cmbo code. Where the bound column is the PK

Private Sub cmboNode_AfterUpdate()
  Dim strKey
  If Not IsNull(Me.cmboNode) Then
    strKey = "E2E" & Me.cmboNode.Value
    Me.tvw.TreeView.Nodes(strKey).Selected = True
    Me.tvw.TreeView.HideSelection = False
  End If
End Sub

 

[Dreamweaver]

@MickJav
I am not sure if this control is supported in 64 bit so consider that for portability. However, this post seems to suggest it is

MSCOMCTL.OCX 64-bit Support Term

social.msdn.microsoft.com

I cannot test it since I am running 32. I love tree views for data presentation. My biggest beef with Access is lack of a reliable native unbound grid control and native treeview.

I'm running both so will let you know but might be a few days as am playing catchup after my holiday

 

[Lightwave]

I also had to deal with recursion after I retired because of getting involved with Ancestry.COM and their GEDCOM files, which are flat-file representations of family tree data. I can tell you unequivocally that logically building / populating the family tree was a mess - but it was even crazier to try to analyze it. Still, I got it working until some yammerhead on the Ancestry site screwed up his tree and then THIS yammerhead (ME) was tired one night and imported a screwy tree segment without checking it thoroughly.

Let me tell you, recursion of family trees really doesn't work well when someone gets a generation mixed up and makes xxx Jr the parent of xxx Sr. Fortunately, I was familiar enough with recursion to have a secondary cut-off built-in - a flag that says "You've been through this person's node X number of times." Let's just say that recursion is not for the squeamish.

I'm just thinking about this kind of thing now. I'm thinking of a structure where you have one table for everyone and relatives (at least genetically) are referred to each other by their parentage. To try and prevent loop through input of wrong data. A parent mother or father must at least have a birth date a specific number of years before the birth date of the individual - lets say 13 years... Surely there is no one who was a parent at 12!

 

[Dreamweaver]

Surely there is no one who was a parent at 12!

I wouldn't count on that the way things are going plus there's the third world there standards differ from ours