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, 08:43
Joined
May 21, 2018
Messages
2,789
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:43
Joined
May 21, 2018
Messages
2,789
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
 

MickJav

AWF VIP
Local time
Today, 13:43
Joined
Nov 28, 2005
Messages
1,432
@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, 13:43
Joined
Sep 27, 2004
Messages
1,433
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!
 

MickJav

AWF VIP
Local time
Today, 13:43
Joined
Nov 28, 2005
Messages
1,432
@MajP Works perfectly on my 32 machine It's a very very nice tool, I can already see loads of ways I can make use of it Thanks
I'll download it on my 64 machine later but as It doesn't use any API calls it may only be a case of reselecting the reference.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:43
Joined
May 21, 2018
Messages
2,789
64 machine later but as It doesn't use any API calls
It is not the code that is an issue, it is the actual treeview control. The MS Common Controls 6.0 I did not think were 64 bit compatible, but that article suggests they are since 2017. Maybe it is an issue of registering, but I know I could not get it to work. Maybe someone else knows. If that is true, then take a look here.
It is done using only MSFORMS controls. This is the greatest vba I have ever seen done. What my code here does is really try to tie the database to the treeview by associating keys with nodes and providing properties. I have not gotten that far yet, but have written code to populate it like I have done here. I have a demo, but I have to fix it. Something I did made the db explode in size and there is not that much in there.
 

MickJav

AWF VIP
Local time
Today, 13:43
Joined
Nov 28, 2005
Messages
1,432
It is not the code that is an issue, it is the actual treeview control. The MS Common Controls 6.0 I did not think were 64 bit compatible, but that article suggests they are since 2017. Maybe it is an issue of registering, but I know I could not get it to work. Maybe someone else knows. If that is true, then take a look here.
I'll check now be back soon
 

MickJav

AWF VIP
Local time
Today, 13:43
Joined
Nov 28, 2005
Messages
1,432
Just run it on my mrs 64 bit access and seems to work without aany issues
2020-02-23.png
 

dgreen

Member
Local time
Today, 07:43
Joined
Sep 30, 2018
Messages
296
@MajP I'm stuck on stupid. Tried to recreate, just to figure it out a little bit better and I broke something. I've copied all the code back in and I'm getting an error on the Form Load() of "not object in this control" on
Code:
 tvw.Init Me.xTree.Object, "qryE2E", "E2E"
The qryE2E exists and opens and is populated. the Treeview is named xTree. There is an identifier field called "E2E".
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:43
Joined
May 21, 2018
Messages
2,789
@Lightwave take a look at the example
It is a lot to wade through, but it this is the breeding program link. This is an example of a record having two parents. The recursion is a little different because you need to span the male side and female side so you need to do it twice.

Also look at the Pairing table. At first I did not think this was necessary to store the 3 keys (motherID, fatherID, and PairingID), but it had a lot of advantages IMO.

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!
I would assume on your form when you enter a record it is in a subform to assign it to a parent. Definetly cannot add a child record that is older than either parent. And you could at least prompt the user if the ages are close. In the breeding program all lineages for each node gets saved. So if you did that then you could check that no child node is pulling in an ancestor. But like I said if you are breeding birds and not people, you can get some weird looking results.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 07:43
Joined
Feb 28, 2001
Messages
16,219
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!
I have replied to your other thread. A "tree view" control is not quite going to help you with the analysis though it might help you with the display of your data.
 

JackStockton

New member
Local time
Today, 05:43
Joined
Jun 19, 2013
Messages
4
There is now a shared version (32bit and 64bit) of the Common Controls. You can switch back and forth from running a frontend accdb with either O365 Access 32bit or 64bit with no issues. Of course that doesn't work with an accde, you have to have two different compiled versions.

If you import a form with a TreeView into a accdb, you must always create a blank from and insert the TreeView control first. It has been my experience that if I just import a from with a TreeView and open it, I could not get it to work. I had to delete the form, create a form and insert the control, then import the form to get it to work.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:43
Joined
May 21, 2018
Messages
2,789
There is now a shared version (32bit and 64bit) of the Common Controls. You can switch back and forth from running a frontend accdb with either O365 Access 32bit or 64bit with no issues. Of course that doesn't work with an accde, you have to have two different compiled versions.

If you import a form with a TreeView into a accdb, you must always create a blank from and insert the TreeView control first. It has been my experience that if I just import a from with a TreeView and open it, I could not get it to work. I had to delete the form, create a form and insert the control, then import the form to get it to work.
Thanks for the info. I think I understand the latter. If you drop a TV into a form it automatically creates the reference to mscomctl. If you import than no reference is created automatically. Is that what you mean by not working? Or actually not working even with a reference?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:43
Joined
May 21, 2018
Messages
2,789
@MajP I'm stuck on stupid. Tried to recreate, just to figure it out a little bit better and I broke something. I've copied all the code back in and I'm getting an error on the Form Load() of "not object in this control" on
Code:
 tvw.Init Me.xTree.Object, "qryE2E", "E2E"
The qryE2E exists and opens and is populated. the Treeview is named xTree. There is an identifier field called "E2E".
The not object in the control means you need to delete and readd the control. Better yet delete and copy from a backup. Like I said when I add it from the user controls, some of the features are wrong and I have not had enough time to figure out what is going on. I also found if I did not rename it Xtree and ran it, that would corrupt it. Had to recopy. These ActiveX controls can be real sensitive, and I would routinely do backups. They have a tendency to crash things.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:43
Joined
May 21, 2018
Messages
2,789
@dgreen Here is an update with the search, updated delete, sortorder. The module has been updated so you would need to pull it in.
One important point is if you plan to delete nodes. You have to ensure cascade deletes or you have to do complex recursion to cascade the deletes. This may look strange for referential integrity, but it works.



Relationship.jpg

The ability to move things up and down is still not the complete solution. If you have the following and move them around
L1.1.1
L1.1.2
L1.1.3
to
L1.1.2
L1.1.3
L1.1.1

On your form I think you would want to be able to rename the Level_ID and update and when you do that reflect it on the form.
However, if this was me I think I would want to do this numbering automatically that would be a lot better. As you move things around then it creates the updated Level names. That should be easy I think to do the auto naming

So I will work on the
1. Move up and down. I think that is easier than I was thinking. I thought you had to do this recursively, but I think maybe not
2. Auto level names if you want them
3. Add new node.

And thanks for these questions, going to give me lots of ideas that I never thought. The auto level id, I can already see great utility. There is a thread here asking "What do you have to do to make the perfect database?". To me this is exactly it. Get a user to provide feedback and iterate updates with them, because you never know what people want and how they would use it. I did this primarily for systems and networks, where in each level there is no sort order, so that never occurred to me.
 

Attachments

Last edited:

JackStockton

New member
Local time
Today, 05:43
Joined
Jun 19, 2013
Messages
4
Thanks for the info. I think I understand the latter. If you drop a TV into a form it automatically creates the reference to mscomctl. If you import than no reference is created automatically. Is that what you mean by not working? Or actually not working even with a reference?
Not working even after adding a reference. Its like there is something more than just a reference to mscomctl that is set.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:43
Joined
May 21, 2018
Messages
2,789
If you import a form with a TreeView into a accdb, you must always create a blank from and insert the TreeView control first. It has been my experience that if I just import a from with a TreeView and open it, I could not get it to work. I had to delete the form, create a form and insert the control, then import the form to get it to work
Interesting, I do not have that problem but the opposite. If I import into new databases that works no problem. However, if I add a new Treeview to a blank form I cannot get it to work. I think it is a default property. But I have yet to figure it out which. I am on 32 bit though with 2010 version of Access. The joys of ActiveX. That is why it would sure be great to have Native Access versions of these controls.
 

dgreen

Member
Local time
Today, 07:43
Joined
Sep 30, 2018
Messages
296
Thank you for the updated version. I have a team mate that will be playing with this so expect some additional questions, but this has been a great engagement.

@dgreen Here is an update with the search, updated delete, sortorder. The module has been updated so you would need to pull it in.
One important point is if you plan to delete nodes. You have to ensure cascade deletes or you have to do complex recursion to cascade the deletes. This may look strange for referential integrity, but it works.



View attachment 79331

The ability to move things up and down is still not the complete solution. If you have the following and move them around
L1.1.1
L1.1.2
L1.1.3
to
L1.1.2
L1.1.3
L1.1.1

On your form I think you would want to be able to rename the Level_ID and update and when you do that reflect it on the form.
However, if this was me I think I would want to do this numbering automatically that would be a lot better. As you move things around then it creates the updated Level names. That should be easy I think to do the auto naming

So I will work on the
1. Move up and down. I think that is easier than I was thinking. I thought you had to do this recursively, but I think maybe not
2. Auto level names if you want them
3. Add new node.

And thanks for these questions, going to give me lots of ideas that I never thought. The auto level id, I can already see great utility. There is a thread here asking "What do you have to do to make the perfect database?". To me this is exactly it. Get a user to provide feedback and iterate updates with them, because you never know what people want and how they would use it. I did this primarily for systems and networks, where in each level there is no sort order, so that never occurred to me.
 

dgreen

Member
Local time
Today, 07:43
Joined
Sep 30, 2018
Messages
296
Agree that having the auto level id would be valuable. In some instances you'd want to know what the sort and level was beforehand so you can track the history of changes. For example, if a person moved from one organization to another, you could see the change from one point in time to another.
 

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

Top Bottom