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

[Dreamweaver]

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

Dozens of Free Excel Downloads For You

jkp-ads.com: Free Downloads Excel files, Excel add-ins

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.

 

[Dreamweaver]

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

 

[Dreamweaver]

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.

 

[dgreen]

I'm on the treeview and I'm able to select an individual node's text and change it in the treeview. Reopening the treeview proves that nothing has changed but why is it behaving this way? If the node was just pointed to a single field (vice a join) could we update the field's value and have it publish the change back to the table?

 

[dgreen]

I would love the help with a single sort column in a query that will match the treeview. I'm still trying to follow what your doing.

 

[dgreen]

To make the Search box more flexible (* "Term" *), add in a query called q_E2E_Search.

SELECT t_E2E.E2E_ID, [Level_ID] & ": " & [category_Descr] AS IDD, t_E2E.Sort
FROM t_E2E
ORDER BY t_E2E.Sort;

Then put the following on the the cmboNode.

Private Sub cmboNode_Change()
'https://stackoverflow.com/questions/48133260/display-records-in-access-db-combobox-on-any-text-typed-by-user
'test number of characters entered - if greater then 2 then assign rowsource
cmboNode.SetFocus

If Len(Me.cmboNode.Text) > 0 Then
    'set the rowsource to match user search criteria
Me.cmboNode.RowSource = "SELECT [q_E2E_Search].E2E_ID, [q_E2E_Search].IDD FROM q_E2E_Search WHERE [q_E2E_Search].IDD LIKE '*" & Me.cmboNode.Text & "*' ORDER BY [q_E2E_Search].Sort"

'show the search in real-time
Me.cmboNode.Dropdown
Else
'set to no
Me.cmboNode.RowSource = "SELECT [q_E2E_Search].E2E_ID, [q_E2E_Search].IDD FROM q_E2E_Search ORDER BY [q_E2E_Search].Sort"
End If
End Sub
Private Sub cmboNode_Click()
'http://www.utteraccess.com/forum/index.php?showtopic=2033577
'Clear combo box on click. Reduce time to clear values in order to search for a new record.
Me.cmboNode = Null
End Sub
Private Sub cmboNode_LostFocus()
Me.cmboNode.RowSource = "SELECT [q_E2E_Search].E2E_ID, [q_E2E_Search].IDD FROM q_E2E_Search ORDER BY [q_E2E_Search].Sort"
End Sub[\code]

 

[MajP]

Remember the database and tree view are not really bound. They act like they are by adding in add, delete, and update queries based on certain actions. Normally I do a click or double click to pop open a form for editing. You would edit the form and then update the node on the forms after update. Just changing the node text is not going to change the underlying data. If you want to keep that subform on the main page you could simply do you edits and update the node on the after update. If you do it from a pop up you change it on the close event. So you can do it the other way and capture the nodes update event (would have to write more class module code) and then update the record from the node. I would prefer to it the former method since it is easier.

 

[dgreen]

Understood. I was surprised that the treeview would allow that kind of behavior.

Remember the database and tree view are not really bound. They act like they are by adding in add, delete, and update queries based on certain actions. Normally I do a click or double click to pop open a form for editing. You would edit the form and then update the node on the forms after update. Just changing the node text is not going to change the underlying data. If you want to keep that subform on the main page you could simply do you edits and update the node on the after update. If you do it from a pop up you change it on the close event. So you can do it the other way and capture the nodes update event (would have to write more class module code) and then update the record from the node. I would prefer to it the former method since it is easier.