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

[dgreen]

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

 

[MajP]

Screen resolution should not matter. Maybe the mouse could. Can you try a different mouse? Definitely doing the opposite of mine

In a query, how do you extract the "Get Level" value for each record that you are able to obtain with the button click? I wish it was as easy as SelectedNodeLevel([E2E_ID]), but that didn't work

You could write to the table on close, or you could calculate show it on the fly. Where and how do you want to display this. You could do it in a query, but the information would come from the tvw. I could be done, but you would need a function on the form. The query calls the function passing in the PK and gets the level back.

You have units of different Echelons and both operational and supporting establishment. Is that the interests in the levels or are these just random organizations. Do you want certain insignia at specific levels?

 

[dgreen]

Ref the icons, it's just a proof of concept, not tied to a level but to a primary key.

Ref the get level, I'd like to see it calculate on the query run.

 

[MajP]

I do not think logically tying an image to a PK would make sense. Instead I assumed these were taskings an added a field "ResponsibleUnit" (Command) to the table. Then I simply gave the tasks assignements. TRADCOC, FORSCOM, AFC, AMC. I named the bitmaps the same as the commands. So the code is simply this for loading the images

Public Sub LoadImages()
  Dim nd As Node
  For Each nd In TVW.Nodes
    nd.Image = DLookup("ResponsibleUnit", "t_E2e", "E2E_ID = " & TVW.getNodePK(nd))
  Next nd
End Sub

I do not know why I thought adding the images was hard. It is simply associating the imagelist with the treeview and then using the name of the image.

 

[dgreen]

I do not think logically tying an image to a PK would make sense. Instead I assumed these were taskings an added a field "ResponsibleUnit" (Command) to the table. Then I simply gave the tasks assignements. TRADCOC, FORSCOM, AFC, AMC. I named the bitmaps the same as the commands. So the code is simply this for loading the images

Public Sub LoadImages()
  Dim nd As Node
  For Each nd In TVW.Nodes
    nd.Image = DLookup("ResponsibleUnit", "t_E2e", "E2E_ID = " & TVW.getNodePK(nd))
  Next nd
End Sub

I do not know why I thought adding the images was hard. It is simply associating the imagelist with the treeview and then using the name of the image.

View attachment 79486

Thank you again. Nice visual.
I've been using the PK since sometimes abbreviations change. Allows the link to stay live. But having your way shows another TTP.

 

[MajP]

You can use a name of your choice "Manpower", "Training", "Facilities" etc. instead of command, unit, agency names. As long as you match the names with those of the images in the imagelist. My point was that if you only assign images to certain nodes and not others, the tree view will be ugly. Those without images will have a blank, and there is no way you could have a different image for every node.

 

[dgreen]

@MajP still having the same issue with the mouse or no mouse (using touch pad) figuring out where the nodes are. Visually, it looks like it thinks the nodes are on the right and the open space is on the left. I've rebooted, changed out the mouse, used the trackpad, etc....

Visuals attached below (yellow arrow shows where the mouse cursor was on the right click). I can get the functionality of moving the node up/down, editing but it's a different experience that what you have on your screen.

Regardless, I think we're down to showing me how to pull out the level of each node in a query (within qryE2ESort).
Adding back in the search vba code I added.
I'd recommend anchoring the treeveiw (down and across), the subform (bottom left) and code buttons (top right), so you can pull on the edges of the form and see more of the treeview without using the scrollbar.
Then it's removing the extra tables, fields, queries and code so we have a clean demonstration product.

 

[MajP]

That is definitely opposite of what I would expect to see. I asked someone else to take a look. It is like your computer does think the nodes are on the other side. As a workaround for this machine you could try swapping the code in the two procedures. Not sure if that would work, but might. Besides that there is nothing I can do.

Private Sub tvw_RightClickOffNode()
  createCommandBarPopUpNoNode Me, Me.TVW
End Sub

Private Sub tvw_RightClickOnNode(PK As Variant)
  Me.TVW.TreeView.Nodes("E2E" & PK).Selected = True
  createCommandBarPopUpNode Me, Me.TVW
End Sub

Is it ok to write the levels to the table, similar to Sort on the forms close? Doing it in a query can be done, but will be strange. You will have to build a public function in the E2E Form that returns the node level. Then the query would have a calculated control I have never in a query called a form to return a public class function value. Need to understand how you want to use it, because calculating on the fly can be done but it will be a simple but convoluted process. Also it would be the same as referencing control values on a form, you can only use the query when the treeview is open. It would be a query calling a procedure on a form, that references an object , that references another object, that references a collection,, and then loops the objects in that collection to return in a function back to the calling function a count. Make sense

 

[dgreen]

Flipping the vba code didn't work. I got a compile error on the PK portion of the below line. Variable not defined.

Private Sub tvw_RightClickOffNode()
  Me.TVW.TreeView.Nodes("E2E" & PK).Selected = True
  createCommandBarPopUpNode Me, Me.TVW
End Sub

Writing the values to a table is fine, I was hoping to reduce the amount of data I'm storing but based on how your describing the process pushing the values to the table is fine.

 

[MajP]

actually that would not work
Try in the class changing

Private Sub mTVW_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
  Dim NodeHit As Node
  If Button = 2 Then
    Set NodeHit = mTVW.HitTest(x, y)
    If Not NodeHit Is Nothing Then
      RaiseEvent RightClickOnNode(Me.getNodePK(NodeHit))
    Else
      RaiseEvent RightClickOffNode
    End If
  End If
End Sub

To

Private Sub mTVW_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
  Dim NodeHit As Node
  If Button = 2 Then
    Set NodeHit = mTVW.HitTest(x, y)
    If Not NodeHit Is Nothing Then
      RaiseEvent RightClickOffNode
    Else
      RaiseEvent RightClickOnfNode(Me.getNodePK(NodeHit))
    End If
  End If
End Sub

I do not have much confidence.

 

[dgreen]

Compile error on RaiseEvent RightClickOnfNode.
Is there additional code I'd need to run it?

 

[MajP]

That is a typo. Remove the leftover "f". OnNode not OnfNode

 

[MajP]

I added the calculation and update of the levels. In the autolevel procedure I just added code to write the levels to the table. The procedure to get a level for a given node already existed in the class.

   strSql = "Update t_E2E SET Level_ID = '" & LevelID & "', [Level] = " & TVW.GetNodeLevel(TVW.getNode("E2E" & PK)) & " WHERE E2E_ID = " & CLng(PK)

 

[dgreen]

While the code compiles, when I right click using the modified code, I end up with a run-time error '91' Object variable or with block variable not set.

This is frustrating, seeing it working on your end and not on mine.

That is a typo. Remove the leftover "f". OnNode not OnfNode

 

[dgreen]

Thank you.

I added the calculation and update of the levels. In the autolevel procedure I just added code to write the levels to the table. The procedure to get a level for a given node already existed in the class.

   strSql = "Update t_E2E SET Level_ID = '" & LevelID & "', [Level] = " & TVW.GetNodeLevel(TVW.getNode("E2E" & PK)) & " WHERE E2E_ID = " & CLng(PK)

 

[dgreen]

So, for the pictures, have you seen a more dynamic way to do it, other than the current technique of embedding the images and tagging them?

I do not think logically tying an image to a PK would make sense. Instead I assumed these were taskings an added a field "ResponsibleUnit" (Command) to the table. Then I simply gave the tasks assignements. TRADCOC, FORSCOM, AFC, AMC. I named the bitmaps the same as the commands. So the code is simply this for loading the images

Public Sub LoadImages()
  Dim nd As Node
  For Each nd In TVW.Nodes
    nd.Image = DLookup("ResponsibleUnit", "t_E2e", "E2E_ID = " & TVW.getNodePK(nd))
  Next nd
End Sub

I do not know why I thought adding the images was hard. It is simply associating the imagelist with the treeview and then using the name of the image.

View attachment 79486

 

[MajP]

So, for the pictures, have you seen a more dynamic way to do it, other than the current technique of embedding the images and tagging them?

I assume you can enter the images via code to the image list, it can be done in .net but not sure of vba. What are you thinking of how you would like it to work. I am guessing you may want to add additional ResponsibleUnits as you go and therefore other icons. If that is the case, I would think you need a table with ResponsibleUnit name and their symbol stored in the DB. But you would probably then have to write code to export to a folder all the icons, put the form into design view, load the images by code into the listview, then save the form. That is my guess, but untried. The other alternative may be simply to have an import image button that allows an import of the image. It will prompt for the correct Name to use. The user will need to make sure it is a .BMP. I will test that to see if it can be done.

Can you test the DB on another machine? I did this in Access 2010 and maybe there is some incompatibility. I recommend you create a blank db. Import all the objects. Make sure to add the two references. Import the tables first then. Then import the other objects. If you cannot test I will see if I can get someone else to test.

 

[dgreen]

For the pictures, I'd love for the code to look to a fixed folder on my computer, find the picture with a matching file name and apply it to the treeview. Thus I'm not having to maintain a non-dynamic picture list in 2 locations.

I'll work on testing out the db elsewhere to see if it performs differently.

 

[MajP]

For the pictures, I'd love for the code to look to a fixed folder on my computer, find the picture with a matching file name and apply it to the treeview. Thus I'm not having to maintain a non-dynamic picture list in 2 locations.For the pictures, I'd love for the code to look to a fixed folder on my computer, find the picture with a matching file name and apply it to the treeview. Thus I'm not having to maintain a non-dynamic picture list in 2 locations.

If I get time I will test tonight. From what I have read it appears you can load the imagelist at runtime. So I will try that.

 

[MajP]

I tested this and works fine. Will post this evening.