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

[MajP]

I added the feature to move a node up and down in its level. These procedures are in the class module. I demo how you can update a node by updating the record in the subform.

 

[dgreen]

Thank you.

I added the feature to move a node up and down in its level. These procedures are in the class module. I demo how you can update a node by updating the record in the subform.

 

[dgreen]

Move up and down for individual nodes worked initially ….
Ran into an issue trying to move the L0 CM - Cost Management as a selection up to the top. Run-time error 91 - object variable or with block variable not set.

Liking the delete node and update changes button.

I added the feature to move a node up and down in its level. These procedures are in the class module. I demo how you can update a node by updating the record in the subform.

 

[dgreen]

Both L1.2.1 and L0, which are below the top level, error out when I try and move them up in the treeview.

 

[dgreen]

Could it be because I'm moving a grouping as opposed to a single value with nothing subordinate to it?

 

[dgreen]

Invalid object when moving the L0 - CM up.

 

[MajP]

I think I got it. There was a by reference issue here

Public Function ChildNodes(ByVal TheParentNode As node) As Collection
  Dim intCounter As Integer
  Set ChildNodes = New Collection
  Dim TheNode As node
  If Not TheParentNode.Child Is Nothing Then
    Set TheNode = TheParentNode.Child
  Else
    Exit Function
  End If
 
  Do While Not TheNode Is Nothing
    ChildNodes.Add TheNode
    Set TheNode = TheNode.Next
  Loop
End Function

I was reassigning the incoming parent node inadvertently.

 

[dgreen]

Ok, seems to be working right now.

 

[dgreen]

If this isn't your full time job don't feel obligated to respond immediately.

The update changes button isn't refreshing the values in the subform based on what's happened in the treeview. I see the changes taking place when I close the form and open t_E2E, after the fact. Thoughts on having that button, also take treeview changes and refresh the subform?

 

[MajP]

The update changes button isn't refreshing the values in the subform based on what's happened in the treeview.

For demo purposes I only demoed changes in the subform updating the changes on the node. I will demo using the node label change event to update the table. I can get you something later tonight.
Normally the way I have it set up is on a double click it pops open a small form asking "Add, Edit, Delete". Add takes you to an add form with the parent set to the clicked node, if no parent slected it adds it to the top level. The edit takes you to the edit form for that node, the delete choice deletes the node. The updates happen on close of the forms. The reason I do not do it the otherway is that normally the label is concatenated.
Currently it is two fields. So to update the table you have to split it into two strings. One updates the levelID and one the description. In order to split this in the query I modified
L1.1.1 Some text
to
L1.1.1: Some text
Then I can split on the ":"
L1.1.1 updates the level id
some text updates the description field.

 

[dgreen]

Would you consider using my attached version as your starting point for future improvements?

1) A column in a query that generates an autogenerated Level_ID (e.g. L.1.10.4)
2) A column in a query that generates a sortable value that went sorted A-Z matches the treeview.
3) In qryOutput, is the Path field supposed to be populated?
4) Populate the Level column in t_E2E as changes to the treeview occur.

I pulled out the tables and queries that weren't related to the E2E table, just to visually simplify things for me.
I anchored the treeview and subform, so you can pull the modal form wider and the box gets larger, supporting when the tree has long branches.
I modified the qryE2E to include the E2E_ID (primary key). I leveraged this query to make the combo box more dynamic (mid string text search).

I put the below code on the cmboNode to make this search work.

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 qryE2E.E2E_ID, qryE2E.nodeText FROM qryE2E WHERE qryE2E.nodeText LIKE '*" & Me.cmboNode.Text & "*' ORDER BY qryE2E.nodeText"
'show the search in real-time
Me.cmboNode.Dropdown
Else
'set to no
Me.cmboNode.RowSource = "SELECT qryE2E.E2E_ID, qryE2E.nodeText FROM qryE2E ORDER BY qryE2E.nodeText"
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 qryE2E.E2E_ID, qryE2E.nodeText FROM qryE2E ORDER BY qryE2E.nodeText"
End Sub

@dgreen
Update 5 Attached. It includes new features
1. Updating the table based on a change in the node label.
2. Auto generating your Levels.
3. Fixes to the move up move down

Still need to add a feature to add a new record, and do icons in the treeview. Any other things to demo? I have not put comments or organized the code so I hope you can follow.

 

[MajP]

Would you consider using my attached version as your starting point for future improvements?
Sure it would be clearer. The other form only showed that you could union many to many and still make a tree.

1) A column in a query that generates an autogenerated Level_ID (e.g. L.1.10.4)

The code to make the auto levels is in the button UpdateLevels. It seems to work well in IMO. I move everything around then update the levels. This is pretty complicated and could never be done in any form of SQL this is done by reading through the try, creating a dictionary, and then reading back through the dictionary. However if you want it to renumber every time you drag and drop then call it at that time or call it on close. The issue is it has to reload the tree and could be an annoyance.

2) A column in a query that generates a sortable value that wen sorted A-Z matches the treeview.

Not sure what you are asking. When you close the sort order is saved so then it opens as last sorted. That is how the move up and down stay sorted.

3) In qryOutput, is the Path field supposed to be populated?

No demo purposes can delete

4) Populate the Level column in t_E2E as changes to the treeview occur.

If you want the levels you can populate on load and update when moved. I built a method for node level. You want levels in the table populated?

Careful modifying the qryE2E. That query is the basis for loading the tree. Adding a field is ok, but needs to have those specific fields and format. That is the trick to making it generic. If you can build the query you can load a treeview with one line of code. That is the magic of custom classes. To do this from scratch would be hours and hours of work. Now I can do it in 5-10 minutes. And this has been helpful for adding and fixing features.

 

[dgreen]

Again, thanks for showing me how you make the magic happen.

#1 - if it updated on form closing or click the update button, that would be great.
#2 - I missed the fact you have it sorting 1-n, I had thought it was just the sort within the node.
#4 - yes please.

Would you consider using my attached version as your starting point for future improvements?
Sure it would be clearer. The other form only showed that you could union many to many and still make a tree.


The code to make the auto levels is in the button UpdateLevels. It seems to work well in IMO. I move everything around then update the levels. This is pretty complicated and could never be done in any form of SQL this is done by reading through the try, creating a dictionary, and then reading back through the dictionary. However if you want it to renumber every time you drag and drop then call it at that time or call it on close. The issue is it has to reload the tree and could be an annoyance.


Not sure what you are asking. When you close the sort order is saved so then it opens as last sorted. That is how the move up and down stay sorted.


No demo purposes can delete


If you want the levels you can populate on load and update when moved. I built a method for node level. You want levels in the table populated?

I pulled out the tables and queries that weren't related to the E2E table, just to visually simplify things for me.
I anchored the treeview and subform, so you can pull the modal form wider and the box gets larger, supporting when the tree has long branches.
I modified the qryE2E to include the E2E_ID (primary key). I leveraged this query to make the combo box more dynamic (mid string text search).

I put the below code on the cmboNode to make this search work

 

[MajP]

#1 - if it updated on form closing or click the update button, that would be great.

I will call it on form load as well. That way if you forget it will ensure it auto levels. But I would keep the button so you can move around a few things and then auto level. You could try it on the drag drop, but it may be annoying if the data gets large.

#2 - I missed the fact you have it sorting 1-n, I had thought it was just the sort within the node.

That one you can see. Notice sorted in "tree" sort.
qryE2ESort

Level_ID	Parent_ID	Sort
L1	​	1​
L1.1	106​	2​
L1.1.1	1​	3​
L1.2	106​	4​
L2	​	5​
L2.1	142​	6​
L3	​	7​
L4	​	8​
L4.1	288​	9​
L4.1.1	796​	10​
L4.1.1.1	104​	11​
L4.1.2	796​	12​
L4.1.2.1	289​	13​
L5	​	14​
L5.1	795​	15​
L5.1.1	140​	16​
L5.1.1.1	144​	17​
L5.1.1.1.1	146​	18​
L5.1.1.2	144​	19​
L5.1.2	140​	20​
L5.1.2.1	148​	21​
L5.2	795​	22​
L5.2.1	797​	23​

#4 - yes please.

That is easy the code is basically there.

 

[dgreen]

Another improvement request, which would have been part of your normal process.....
- Right click on a node value and it takes you to a filtered form.
- Right click and get a filtered report.

 

[MajP]

I do not think you can trap a right click.
As I said, my simple preferred approach is to have a double click event that pops open an intermediate menu form. You would pass this form the Primary key in openargs. On this form you have choices to open other forms and reports based on the node clicked and the choice made.
Edit, Delete, Add at Current Location, Add Root Level, View Report for This Node .....
Make a selection and it passes the PK again to the next form or report.
Is the filter just on a PK or what kind of filter?
I can demo popping up this intermediate form, and after that it is just basic Access that you can code.
I see to be able to sort with no problem no, but in a different version of the db. I have to see what is going on with the most recent version.

 

[dgreen]

So would this code not be able to provide that custom right click (e.g. shortcut menu)?
https://docs.microsoft.com/en-us/do...-to-attach-a-shortcut-menu-to-a-treeview-node
https://www.syncfusion.com/faq/566/...e-user-right-clicks-on-a-node-in-the-treeview

Acknowledge how you'd have an intermediary form menu.
The filter would be on the primary key. Once you demo it out, I think I could figure out variations from that point.

 

[MajP]

So would this code not be able to provide that custom right click (e.g. shortcut menu)?

That code is in .net and not vba. I do not know if something like that exists in vba.

 

[dgreen]

Just doing some fast looking around. Maybe these are good starting points?

Treeview with right-click menu? - Microsoft: Access Forms

Does anyone know a way of adding a right-click menu for a treeview control? I'd like to give the users some options when right-clicking items in my treeview, such as "open form", "delete item", etc. Is this possible? any help is appreciated!!!!

www.tek-tips.com

UtterAccess.com

www.utteraccess.com

 

[MajP]

Interesting, ties into some discussions on command bars. Those are pretty good tricks. I will show you the intermediary and you can decide if write click is still desired. I will give it a try just to learn it.