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

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

 

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

 

[MajP]

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

 

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