Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
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.
 

dgreen

Member
Local time
Today, 11:21
Joined
Sep 30, 2018
Messages
397
I just reopened this website and the zip file isn't showing?
 

Attachments

  • Capture.PNG
    Capture.PNG
    98.8 KB · Views: 182

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
@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, 11:21
Joined
Sep 30, 2018
Messages
397
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

  • Picture1.png
    Picture1.png
    32.6 KB · Views: 176
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
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


I put the Final code examples in the first Thread

 
Last edited:

dgreen

Member
Local time
Today, 11:21
Joined
Sep 30, 2018
Messages
397
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, 11:21
Joined
Sep 30, 2018
Messages
397
@MajP could you fix the last issue I found and republish?

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

dgreen

Member
Local time
Today, 11:21
Joined
Sep 30, 2018
Messages
397
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, 12:21
Joined
May 21, 2018
Messages
8,463
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, 11:21
Joined
Sep 30, 2018
Messages
397
@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

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
@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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
I use Irfanview. Free and really good for batch conversion and renaming
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

Registered User.
Local time
Today, 12:21
Joined
Mar 12, 2014
Messages
132
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

Registered User.
Local time
Today, 12:21
Joined
Mar 12, 2014
Messages
132
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

Registered User.
Local time
Today, 12:21
Joined
Mar 12, 2014
Messages
132
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

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
If you can please post the adodb version.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:21
Joined
Sep 21, 2011
Messages
14,044
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.?

1586086567048.png
 

Attachments

  • majp error.PNG
    majp error.PNG
    11 KB · Views: 182

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:21
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom