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, 14:35
Joined
May 21, 2018
Messages
3,260
If interested here is how the light load works and the difference in code. This probably should be the default load method.
Original code that adds all nodes
Code:
Public Sub addBranch(rsTree As DAO.Recordset, parentIDValue As Variant, identifier)
On Error GoTo errLable
Dim strCriteria As String
Dim bk As String
Dim currentID As Variant
Dim currentText As String
Dim currentNode As Node
Dim ParentNode As Node
  Dim strKey As String
  strCriteria = mParentIDfield & " = '" & parentIDValue & "'"

'Since it is not a root node must determine the parent node. All node keys are "ID " & the primary key
Set ParentNode = Me.Nodes(parentIDValue)
'MsgBox strCriteria
rsTree.FindFirst (strCriteria)
'MsgBox strCriteria
Do Until rsTree.NoMatch
currentID = rsTree.Fields(mIDfield)
currentText = rsTree.Fields(mNodeTextField)
identifier = rsTree.Fields(mTypeIdentifierField)
Set currentNode = Me.Nodes.Add(ParentNode, tvwChild, currentID, currentText)
currentNode.Tag = identifier
bk = rsTree.Bookmark
Call addBranch(rsTree, currentID, identifier)
rsTree.Bookmark = bk
rsTree.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
Here is the light load. It only loads the first node in a level and any other first nodes for added nodes. This is still a recursive call but only for the first node. Notice the Do until is replaced with a simple if then. That is the only difference. If you did not add at least one child then you would not get the plus sign.
Code:
Public Sub addLiteBranch(rsTree As DAO.Recordset, parentIDValue As Variant, identifier)
On Error GoTo errLable
Dim strCriteria As String
Dim bk As String
Dim currentID As Variant
Dim currentText As String
Dim currentNode As Node
Dim ParentNode As Node
  Dim strKey As String
  strCriteria = mParentIDfield & " = '" & parentIDValue & "'"

'Since it is not a root node must determine the parent node. All node keys are "ID " & the primary key
Set ParentNode = Me.Nodes(parentIDValue)
rsTree.FindFirst (strCriteria)
'Only going to get the top node
If Not rsTree.NoMatch Then
currentID = rsTree.Fields(mIDfield)
currentText = rsTree.Fields(mNodeTextField)
identifier = rsTree.Fields(mTypeIdentifierField)
Set currentNode = Me.Nodes.Add(ParentNode, tvwChild, currentID, currentText)
currentNode.Tag = identifier
bk = rsTree.Bookmark
Call addLiteBranch(rsTree, currentID, identifier)
rsTree.Bookmark = bk
rsTree.FindNext (strCriteria)
End If
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
When you expand a node the Expand event takes place. Here you have to add all the additional child nodes for the first child level and one child node (if exists) to each node added to get the plus sign. Since the first node exists already you can not add it or you throw a "key already exists" error.
Code:
Private Sub mTVW_Expand(ByVal Node As MSComctlLib.Node)
'Flush out any remaining nodes in this expanded level and liteload child level
Dim strCriteria As String
Dim bk As String
Dim currentID As Variant
Dim currentText As String
Dim currentNode As Node
Dim ParentNode As Node
Dim currentIdentifier As String
Dim rsTree As DAO.Recordset
Debug.Print Node.Text & " " & "Selected Node"
Set rsTree = Me.Recordset
strCriteria = mParentIDfield & " = '" & Node.key & "'"
rsTree.FindFirst (strCriteria)
Debug.Print strCriteria
If rsTree.NoMatch Then
MsgBox "There is no record with a Parent ID of " & Node.key
End If
if node.children > 1 then exit sub
Do Until rsTree.NoMatch
currentID = rsTree.Fields(mIDfield)
Debug.Print currentID & " current"
currentText = rsTree.Fields(mNodeTextField)
currentIdentifier = rsTree.Fields(mTypeIdentifierField)
If Node.Child.key = currentID Then
Node.Child.Tag = currentIdentifier
RaiseEvent ExpandedBranch(Node.Child)
Else
Set currentNode = mTVW.Nodes.Add(Node.key, tvwChild, currentID, currentText)
currentNode.Tag = currentIdentifier
RaiseEvent ExpandedBranch(currentNode)
bk = rsTree.Bookmark
Call addLiteBranch(rsTree, currentID, currentIdentifier)
End If
rsTree.Bookmark = bk
rsTree.FindNext (strCriteria)
Loop
End Sub
So I tested this with 10k nodes. It is not even a question if this should be the approach on larger TVs. I could not even load it originally and this is immediate. However, for both methods it seems the issue is more the amount of nodes in a single level. Even in this method if I have over a couple hundred nodes in one level it is slow. But if there are lots of branches and a relatively few (like <100) it is very fast.
So I need to
1) Update the initialize to allow the user to choose full load or light load.
2) Add an optional parameter to define which field the name of the image is located. If you are adding an image often it is defined in the table which image. But this will be an optional parameter.
3) Still need to address updating only the branch autolevel on a drag and drop
4) Reorganize the code and add commentary.
I will post the next update this evening.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
Here is V13. I did a lot of cleaning up of the class so if you are importing I have changed some names.
1. Added the feature to allow you to provide a field in the query with the name of the image if applicable. This reduces additional code to load the images. Made it an optional parameter so it loads as the node loads.
2. Added an optional parameter to do a full load or a light load. The light load caused trickle down problems so I had to write code to address several issues. SInce those nodes are not loaded there are problems with sorting and autolevel and expanding branches. (These features will not work well in a very big tree). If it is a small tree (less then maybe 2k, I would do a full load)
3. Add a "TestData" form demonstrating 10K nodes in a light load. Works well but you will see nodes with several hundred children will run slow. However, this would not work at all any other way.
4. Still demonstrates the dynamic image list load.

This Treeview probably has every bell and whistle I can think of. Any others?

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
 

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
The Search combo seem to be broke.
Recommend Compiling. I'm seeing an issue at createCommandBarPopUpNoNode().

Once the above are resolved.....

Remove the extra tables, queries, forms and vba so it's a clean database.
Implement the dynamic search code, from page 2 of this string, to enable a mid string search.
Anchor the treeview to down and right; anchor the controls and combo search to top right; the subform to bottom left.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
@dgreen
I updated the previous file.
1) I added my class module for a Find as you Type combo box which you will find far superior. You will want this for your library because it turns any combobox into a FAYT with one line of code. It also works much better because it handles up and down arrow keys. I instantiate on the load event.
2) The problem with the combo is an issue with a light load. It is searching for a node that is not loaded yet. As I said you probably want to use a full load (modify the initialize method) and will not have an issue, but I put in error checking to explain it. Try now. Pick something like 1.2 (second level) and you will get the error message. Expand that node and then try it.
3) I fixed the select node feature so that it is highlighted blue and not the faint highlight. The method is drop highlight.

All those other features are your specifics, and not necessary for demonstrating the Treeview features. Unless you have additional features or code, do whatever you want formatting and removing of other objects. Some of those buttons are for demo purposes and can be removed because of the combo. You may no longer want to capture the double click event since you can do that off the right click.
 

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
Understood.. Thank you for the effort and time. I'll take a look later today. Off to fix a flat tire issue.

@dgreen
I updated the previous file.
1) I added my class module for a Find as you Type combo box which you will find far superior. You will want this for your library because it turns any combobox into a FAYT with one line of code. It also works much better because it handles up and down arrow keys. I instantiate on the load event.
2) The problem with the combo is an issue with a light load. It is searching for a node that is not loaded yet. As I said you probably want to use a full load (modify the initialize method) and will not have an issue, but I put in error checking to explain it. Try now. Pick something like 1.2 (second level) and you will get the error message. Expand that node and then try it.
3) I fixed the select node feature so that it is highlighted blue and not the faint highlight. The method is drop highlight.

All those other features are your specifics, and not necessary for demonstrating the Treeview features. Unless you have additional features or code, do whatever you want formatting and removing of other objects. Some of those buttons are for demo purposes and can be removed because of the combo. You may no longer want to capture the double click event since you can do that off the right click.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
I deleted the previous and added the new one in same location.
FYI,
When creating a new DB it is best to do a fresh import and not delete from an existing. Just remember the three references. I list them out in the class at the very top. So create a brand new db. Import tables first. Then import other objects. Add references. Compact and repair. Anytime a DB starts acting weird I do this especially in development.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
@dgreen,
One more thing. You are not limited to this formatting. You can apply colors, fonts, bold, etc. So after you load the tree you could iterate the node collection and apply different formats based on content or level. There are also different Treeview styles. Some of them do not make much sense to me.
 

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
I realize I'm asking and we've been improving for weeks..... You've done a lot for this and I appreciate every bit of it....

Thank you for sharing the filter as you type module. I'll consider it for my other databases.

I could use a little more clarity on where do you Initialize the Light or Full Load for the TreeView. Where do I make the change or selection?

1) When you click to delete a node, could there be a final option to say No/Cancel?
2) When you click on any of the Node Information buttons, the highlight of the node in the treeview you're looking for info on goes away. Can the code keep the highlight on that record in the treeview, so you visually know what you've asked for data on?
3) AddNewNode button next to the Delete Node. Complements the RightClick option for demo purposes.
[Added]
4) Add root node option on the frmAddEditDelete Form isn't working. The other options are.

Recommendations:

Add to the notes in the class module TreeViewForm reference Microsoft Windows Common Controls 6.0 (SP6)
Where to navigate to, in order to enable the MSCOMCTL.OCX (32 -bit systems: C:\Windows\System32). Note on some computers you might have to remove the filter to show All Files in order to find the .ocx file type.
Also where to download the file if it doesn't exist (https://www.microsoft.com/en-us/download/confirmation.aspx?id=10019)

Add some pictures to the right click command bars (picture below). Just need to add the numbers within your msoControlButton. I've found a couple from the Office Fluent User Interface Controls that would work.
'https://www.microsoft.com/en-us/download/confirmation.aspx?id=36798

Expand Tree
Set cbCtrl = cbNoNode.Controls.Add(msoControlButton, 3986)
Collapse Tree
Set cbCtrl = cbNoNode.Controls.Add(msoControlButton, 3987)

I'm not sure how to do the pictures with the other command bar since it's in a different format.
 

Attachments

Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
I could use a little more clarity on where do you Initialize the Light or Full Load for the TreeView. Where do I make the change or selection?
In the form's on load you initialize the class.
TVW.Initialize Me.XTree.Object, "qryNodeE2E", "E2E", True, lt_lightload
-The first parameter is the Treeview object
-Second is the node query specifically built. Details are in the class. You have to alias certain fields
-Third is the Starting ParentID. For all queries, you have to create a field called identifier which is a name representing the table it came from. This is needed if you do a union query, making hierarchial data out of a 1 to many. I demo this in the other form. In this case the root node values have no parent, but when you concatenate the identifier with the null parent you get just "E2E". The big trick I do is save the pk and identifer as the key, and the identifier is saved in the tag. Remove the tag from the key and you have the PK and you know which table it resides in
-Fourth is the new boolean that says you have the names of the icons saved in the table and are in the query as NodeImage
-Fifth is the type of load. Change it to lt_fullload.

1) When you click to delete a node, could there be a final option to say No/Cancel?
Just wrap the code in an if then
If MsgBox("Do you really want to delete " & TVW.Selectednode.Text & "? ", vbYesNo) = vbYes Then
TVW.DeleteNode TVW.Selectednode.key
delete query code
End If
Fixed

2) When you click on any of the Node Information buttons, the highlight of the node in the treeview you're looking for info on goes away. Can the code keep the highlight on that record in the treeview, so you visually know what you've asked for data on?
No once it loses focus it will unhighlight. See modification in right click where I repeate the node text in the message box and provide all information in one window.

3) AddNewNode button next to the Delete Node. Complements the RightClick option for demo purposes.
Added

Recommendations:

Add to the notes in the class module TreeViewForm reference Microsoft Windows Common Controls 6.0 (SP6)
Where to navigate to, in order to enable the MSCOMCTL.OCX (32 -bit systems: C:\Windows\System32). Note on some computers you might have to remove the filter to show All Files in order to find the .ocx file type.
Also where to download the file if it doesn't exist (https://www.microsoft.com/en-us/download/confirmation.aspx?id=10019)
Added

Add some pictures to the right click command bars (picture below). Just need to add the numbers within your msoControlButton. I've found a couple from the Office Fluent User Interface Controls that would work.
'https://www.microsoft.com/en-us/download/confirmation.aspx?id=36798
Added. RightClick.jpg NodeInfo.jpg
 

Attachments

Last edited:

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
Nice icons for the other right click menu

One more catch.
Add root node option on the frmAddEditDelete Form isn't working. The other options are.
 

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
@MajP could you fix the last issue I found and republish?

Add root node option on the frmAddEditDelete Form isn't working
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
Version 15. If you want to spend some time on the subform and add edit form, there should be a combo box to pick "responsible unit" so you can choose which icon to use. You can add a node now, but there is no way to pick their unit symbol.
 

Attachments

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
I have truly enjoyed partnering with you on this and thank you for delivering a highly sought after capability.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:35
Joined
May 21, 2018
Messages
3,260
Yeah it has been fun, I have learned as much as you. There are things I never would have thought about, and definitely something I can incorporate. I know a lot about Access and VBA mainly because I try to answer a lot of questions that I have to figure out how to do it.

For example I now know how to to a Dijkstra shortest path algorithm in Access with a disconnected graph
I am also one of probably a handful of people who has ever calculated a coefficient of inbreeding in Access. No idea what that even was

The drag and drop and autoleveling will come in handy for system specifications, requirements generation, tasks and sub tasks.
 

dgreen

Member
Local time
Today, 13:35
Joined
Sep 30, 2018
Messages
396
@MajP - A note for the vba code.... The folder that holds the .BMP images, can only contain records that are in the table, NO Extras. If you have extra pictures in the folder, the code errors (run-time '481', invalid picture) and the treeview won't display.
 

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

Top Bottom