• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

Hierarchical Data, Recursion, Tree-View, and a another Great Custom Class (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
3,244
I have had some people ask questions on working with Hierarchical data, which always also involves using recursion and treeviews. 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. Also common in Family trees and working with assemblies sub assemblies. These usually go until they hit the end of the branch.

So here is a simple example for demo. The procedure is told how many times to loop and the counter is incremented each time the procedure is called. It simply adds 10 to the starting value each time it is called.
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


When working with hierarchical data normally each record references a parentID in the same 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

In order to get it into a proper order you can sort it. I made a field called sort, one called level, and path.
The sort puts it into the node order, the level is what level of the tree, and the path is the list of parents. So the path for 5 is 1,2,4

Here is the recursive code to span the tree. The process is the same for filling a treeview. Some people will do this in a single procedure, I do the base nodes (1 and 7) in a separate procedure because it is easier. You need a public variable for the recordset and in this case the sort.

Code:
Private SortRecordset As DAO.Recordset
Private Sort As Long
Public Sub AddRecursiveRecords()
   'Parent and Child IDs are numeric see the instructions on concatenation to make them strings
   Const Domain = "T_e2e"
   Const ParentField = "Parent_ID"
   Const IDField = "E2e_ID"

   Dim strCriteria As String
   Dim bk As String
   Dim SortLevel As Long
   Dim NodeID As Long
   Dim nodeLevel As Long
   Dim path As String

   Sort = 0
   Set SortRecordset = CurrentDb.OpenRecordset(Domain, dbOpenDynaset)
   strCriteria = ParentField & " Is Null "
   SortRecordset.FindFirst strCriteria
   nodeLevel = 1
   Do Until SortRecordset.NoMatch
    Sort = Sort + 1
    NodeID = SortRecordset.Fields(IDField)
    path = CStr(NodeID)
    SortRecordset.Edit
      SortRecordset!Sort = Sort
      SortRecordset!level = nodeLevel
    SortRecordset.Update
 
    bk = SortRecordset.Bookmark
    Call AddRecursiveBranch(NodeID, nodeLevel)
 
    'ensure you return back to where you were since the bookmark is moving in recursive calls
    SortRecordset.Bookmark = bk
    SortRecordset.FindNext strCriteria
  Loop

End Sub
The above procedure is for the base nodes and it is not a recursive call. The base in this case are records where the parent ID is null.

The above procedure calls the recursive procedure that spans the branches for each base node.
Code:
Private Sub AddRecursiveBranch(ByVal ParentID As Variant, ByVal nodeLevel As Long, Optional ByVal path As String = "")

  On Error GoTo errLable

  Const ParentField = "Parent_ID"
  Const IDField = "E2e_ID"

  Dim strCriteria As String
  Dim bk As String
  Dim NodeID As String

  strCriteria = ParentField & " = " & ParentID
  SortRecordset.FindFirst strCriteria

  If path = "" Then
    path = ParentID
  Else
    path = path & "-" & ParentID
  End If
  nodeLevel = nodeLevel + 1
  Do Until SortRecordset.NoMatch
    NodeID = SortRecordset.Fields(IDField)
 
    Sort = Sort + 1
    SortRecordset.Edit
      SortRecordset!Sort = Sort
      SortRecordset!path = path
      SortRecordset!level = nodeLevel
    SortRecordset.Update
    bk = SortRecordset.Bookmark
   'Recursive call
   Call AddRecursiveBranch(NodeID, nodeLevel, path)
   SortRecordset.Bookmark = bk
   SortRecordset.FindNext strCriteria
  Loop
Exit Sub
errLable:
  MsgBox Err.Number & " " & Err.Description & " In addBranch"
  If MsgBox("Do you want to exit the loop?", vbYesNo, "Error In Loop") = vbYes Then
     Exit Sub
   Else
     Resume Next
   End If
End Sub
Each time it is called the current node determines its Child. It updates the table, and then calls itself passing the current node in. When the procedure is called it determines its child ....
It spans the branch until no child exists.

Working with treeviews is pretty code heavy, but like I done in other classes where I wrap a control in a custom class I have done the same here. If you build your query as instructed in the class module you can build a tree view with a couple lines of code. (@moke123 @Tera) There are a ton of helpful properties. It is still a work in progress, but I have kind of gave up because I was running 64 bit, and thus coded the same in the MSFORMS version.

AFAIK the treeview control is no longer supported in 64 bit versions (but I have to check if there is an update). So there is a free version done with MS Forms control.

If you look at the demo you can see that you can use a union query to fake hierarchical data. I combined Orders and orderDetails. So you can do any one to many or many to many. I demo drag and drop and will come back and update this demo to show how you add nodes/records, and delete nodes/records. I have drag and drop enabled for some of the trees.
If anyone is further interested just hit me up, because it would take me pages to explain all of the capabilities I put into this.
Treeview.jpg
 

Attachments

Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
3,244
Here is the table using the sort routine
Query1

E2E_IDLevel_IDParent_IDSortLevelPath
729​
L0
1​
1​
730​
L0
2​
1​
1​
L1
730​
3​
2​
730
104​
L1.1
1​
4​
3​
730-1
140​
L1.1.1
104​
5​
4​
730-1-104
141​
L1.1.2
104​
6​
4​
730-1-104
142​
L1.1.3
104​
7​
4​
730-1-104
143​
L1.1.4
104​
8​
4​
730-1-104
144​
L1.1.5
104​
9​
4​
730-1-104
145​
L1.1.6
104​
10​
4​
730-1-104
146​
L1.1.7
104​
11​
4​
730-1-104
288​
L1.1.7.1
146​
12​
5​
730-1-104-146
439​
L1.1.7.1.1
288​
13​
6​
730-1-104-146-288
440​
L1.1.7.1.2
288​
14​
6​
730-1-104-146-288
441​
L1.1.7.1.3
288​
15​
6​
730-1-104-146-288
289​
L1.1.7.2
146​
16​
5​
730-1-104-146
449​
L1.1.7.2.1
289​
17​
6​
730-1-104-146-289
147​
L1.1.8
104​
18​
4​
730-1-104
794​
L1.1.9
104​
19​
4​
730-1-104
795​
L1.1.10
104​
20​
4​
730-1-104
796​
L1.1.11
104​
21​
4​
730-1-104
105​
L1.2
1​
22​
3​
730-1
106​
L1.3
1​
23​
3​
730-1
148​
L1.3.1
106​
24​
4​
730-1-106
 

The_Doc_Man

Happy Retired Curmudgeon and Occasional Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 28, 2001
Messages
17,286
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, 10:20
Joined
Sep 30, 2018
Messages
396
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, 11:20
Joined
May 21, 2018
Messages
3,244
"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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
3,244
@dgreen I added an updated version to the original thread that has drag and drop capability. Could not find the problem. Had to grab an older version of code, so hopefully did not break anything.
 

dgreen

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

dgreen

Member
Local time
Today, 10:20
Joined
Sep 30, 2018
Messages
396
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
 

MickJav

AWF VIP
Local time
Today, 16:20
Joined
Nov 28, 2005
Messages
2,075
@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, 11:20
Joined
May 21, 2018
Messages
3,244
@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, 11:20
Joined
May 21, 2018
Messages
3,244
@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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
3,244
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, 10:20
Joined
Sep 30, 2018
Messages
396
@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

The_Doc_Man

Happy Retired Curmudgeon and Occasional Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 28, 2001
Messages
17,286
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, 10:20
Joined
Sep 30, 2018
Messages
396
Disregard. I pulled in the wrong VBA code for the treeview.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
3,244
@dgreen
I had some left over modules. You kept the bad and deleted the good one. Needs to read "TreeviewForm" the one listed "TreeviewForm (newOld)" will not work. Also, there are some settings in the tree view that I cannot figure out. I had to copy one of mine. If the settings are not correct you do not get the + symbols and cannot expand the tree.
 

Attachments

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
3,244
@The_Doc_Man So what I was saying was not only do you learn Access here, but you can learn things you never thought you wanted to learn. I am now one of a small group of people who can code in VBA coefficients of inbreeding. So that is a great bar trick to have in the bag.
 

dgreen

Member
Local time
Today, 10:20
Joined
Sep 30, 2018
Messages
396
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:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom