Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist (2 Viewers)

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
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).
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
test it
Code:
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.
I have also added to this capability with a class module for loading this Tree View.

E2E.jpg


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.
 

Attachments

  • TreeView Demo V18.zip
    454.7 KB · Views: 253
  • MSFORMS MajP TreeView LightLoad.accdb
    2.3 MB · Views: 178
  • Span Files Directory V5.accdb
    1.2 MB · Views: 160
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 28, 2001
Messages
26,996
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

Member
Local time
Today, 07:47
Joined
Sep 30, 2018
Messages
397
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?

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

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
"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.

OCX.jpg
 

dgreen

Member
Local time
Today, 07:47
Joined
Sep 30, 2018
Messages
397
@MajP query1 error on opening .sort? How did the path get populated in the t_E2E?
 

dgreen

Member
Local time
Today, 07:47
Joined
Sep 30, 2018
Messages
397
Capture.PNG

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

Well-known member
Local time
Today, 12:47
Joined
Nov 28, 2005
Messages
2,466
@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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
@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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
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

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

Member
Local time
Today, 07:47
Joined
Sep 30, 2018
Messages
397
@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.
 

Attachments

  • Hierarchy.zip
    55.6 KB · Views: 725

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 28, 2001
Messages
26,996
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

Member
Local time
Today, 07:47
Joined
Sep 30, 2018
Messages
397
Disregard. I pulled in the wrong VBA code for the treeview.
 

dgreen

Member
Local time
Today, 07:47
Joined
Sep 30, 2018
Messages
397
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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
Here is the search cmbo code. Where the bound column is the PK
Code:
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

Well-known member
Local time
Today, 12:47
Joined
Nov 28, 2005
Messages
2,466
@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
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

Ad astra
Local time
Today, 12:47
Joined
Sep 27, 2004
Messages
1,521
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!
 

Users who are viewing this thread

Top Bottom