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

[dgreen]

I just reopened this website and the zip file isn't showing?

 

[dgreen]

Downloaded. Again thanks.

@dgreen
See if this works. I did not reversion so still 13.

 

[MajP]

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

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.

 

[MajP]

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.

I put the Final code examples in the first Thread

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

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

 

[dgreen]

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]

@MajP could you fix the last issue I found and republish?

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

 

[dgreen]

I have truly enjoyed partnering with you on this and thank you for delivering a highly sought after capability.

 

[MajP]

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

https://www.access-programmers.co.uk/forums/posts/1675982/bookmark

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

https://www.access-programmers.co.uk/forums/posts/1601847/bookmark

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

 

[dgreen]

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

 

[MajP]

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

I do not think that is the problem. When you originally gave me the .bmps, I could not load any of them and had to get new .bmps. Something is corrupted in how / where you download the .bmps. I loaded my folder up with other pictures without any problem.

 

[dgreen]

How did you make or convert the files to be .bmps? My technique was to find the file, drag and drop to this website (https://image.online-convert.com/convert-to-bmp), size at 38 x 38 pixels and download the file to the folder.

 

[MajP]

I use Irfanview. Free and really good for batch conversion and renaming

IrfanView - Official Homepage - One of the Most Popular Viewers Worldwide

IrfanView ... one of the most popular viewers worldwide.

www.irfanview.com

You can test by going into design view. Right click on the image list and choose to edit. Should be able to load images manually. The video show you how. I got the same error trying to manually load the images.

 

[Fran Lombard]

Update 7
Added Right click function
1) Command bar when off node
2) Command bar when over node
Duplicates a lot of the other functions

Do you have any icons you want? THey should be 32X32 pixel BMPs. With Drag and drop it will be very hard to modify the images per level of node.View attachment 79467

Any chance you can upload or send me a version of this for A2007?

 

[Fran Lombard]

Awesome job on this treeview.
I've been needing to add this functionality to my db and while I've played with trees in Vb6 some 15 years ago, I couldn't find any of the old code.

This example will save me many hours of relearning as well as give me functionality i never had.

Thank you
Fran

Ps. I assume its ok to reuse the code in my own app?

 

[Fran Lombard]

I would think it works in 2007. What does not work? In the class and in the thread it discusses how to load the references.

I got it working. It was really just the security warnings.

I did however port the table over to Mssql and updated dao to adodb and modified the findfirst & nomatch to the appropriate adodb calls. Works great.

 

[MajP]

If you can please post the adodb version.

 

[Gasman]

Ok, I have cocked up big time here.

I imported that class into my Test DB
Now every time I open it it gives the error message below and I am stuck accepting the error message multiple times, so much so I have to kill Access.

I cannot delete/remove it. I have tried highlighting all the code to comment/delete it, but just get the error messages.?

How to I get rid of this please.?

 

[MajP]

There are some other modules in the DB that are used. There is a module to handle the command bars and I do not see that listed. I do not have it open right now to know if there are even more modules. I would import all of them from the DB.

 

[Gasman]

Well I managed to delete all the code by pressing OK many many times, but I still cannot delete that module.

I used File to import it.

I've exported what I needed, renamed it Corrupt Test and gone to backup. Lesson learnt.