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

[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

 

[MickJav]

@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!

 

[MickJav]

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

 

[MickJav]

@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]

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.

Excel: JKP-ADS Download page

jkp-ads.com: Downloads

www.jkp-ads.com

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]

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]

Just run it on my mrs 64 bit access and seems to work without aany issues

 

[dgreen]

@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

 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]

@Lightwave take a look at the example

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

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]

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]

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]

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]

@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

 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]

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

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.

 

[JackStockton]

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]

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]

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]

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.