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

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

 

[dgreen]

I'm am waiting in anticipation. This sounds promising.

 

[dgreen]

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.

Downloaded the file to another computer and the right click works correctly. Not sure how I'm going to isolate what's different in order to fix it.

 

[MajP]

This dynamically loads the images from the folder at runtime.
For the demo purpose I used a folder in the same folder as the application called "Bitmaps". FYI, previously I had to get new bitmaps the ones you provided for some reason would not load. In an application you would probably want a system setting in some table to allow the user to browse and store the default path.

  Dim path As String
  Dim file As String
  Dim PathAndFile As String
  Dim oFile As Variant
  Dim oFiles As Collection
  path = CurrentProject.path & "\" & "bitmaps"
  Set oFiles = AllFiles(path)
  For Each oFile In oFiles
    PathAndFile = path & "\" & oFile
    Me.imageListNodes.listimages.Add , Replace(oFile, ".bmp", ""), LoadPicture(PathAndFile)
    'Debug.Print Me.imageListNodes.listimages.Count
    'Debug.Print PathAndFile
  Next oFile
 
End Sub
Public Function AllFiles(ByVal FullPath As String) _
  As Collection
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject

'PARAMETER: FullPath = FullPath to folder for
'which you want all files

'************************************************

Dim oFs As New FileSystemObject
Dim sAns As New Collection
Dim oFolder As Folder
Dim oFile As file
Dim lElement As Long

If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)
 
    For Each oFile In oFolder.Files
      If Right(oFile.Name, 4) = ".bmp" Then
        sAns.Add oFile.Name
      End If
   Next
End If
'Debug.Print sAns.Count
Set AllFiles = sAns
ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function

 

[dgreen]

I'll check it out tmrw am

 

[gemma-the-husky]

shouldn't this be in code samples, say?

 

[dgreen]

Once we get the product to a cleaned up state, then I'd recommend yes. We're close on a beta product but still have to remove the extra tables, queries and comment the vba code.

shouldn't this be in code samples, say?

 

[gemma-the-husky]

I love recursion. One problem with vba is that there is no true pointer data type which makes it hard to represent recursive objects.
It's pretty elegant, for example, to walk a family tree with a handful of lines of code.

fwiw, I constructed a program to play draughts/checkers using a recursive minimax algorithm as my final year degree project. Very educational, if not ever so fast.

 

[dgreen]

Do you have any recommendations on the code that we've been working through and posting on this thread?

I love recursion. One problem with vba is that there is no true pointer data type which makes it hard to represent recursive objects.
It's pretty elegant, for example, to walk a family tree with a handful of lines of code.

fwiw, I constructed a program to play draughts/checkers using a recursive minimax algorithm as my final year degree project. Very educational, if not ever so fast.

 

[dgreen]

It works. I love the dynamic nature it uses to find the picture vice having to load them into the database and tag them individually.

I also tested if you changed a file name, would the code pick up a new picture, when you reopened the treeview form. It worked.
Also, if the nodes picture didn't exist in the folder, what would happen.... you get a blank space before the text and a message saying the value couldn't be located. Seemed reasonable.

This dynamically loads the images from the folder at runtime.
For the demo purpose I used a folder in the same folder as the application called "Bitmaps". FYI, previously I had to get new bitmaps the ones you provided for some reason would not load. In an application you would probably want a system setting in some table to allow the user to browse and store the default path.

  Dim path As String
  Dim file As String
  Dim PathAndFile As String
  Dim oFile As Variant
  Dim oFiles As Collection
  path = CurrentProject.path & "\" & "bitmaps"
  Set oFiles = AllFiles(path)
  For Each oFile In oFiles
    PathAndFile = path & "\" & oFile
    Me.imageListNodes.listimages.Add , Replace(oFile, ".bmp", ""), LoadPicture(PathAndFile)
    'Debug.Print Me.imageListNodes.listimages.Count
    'Debug.Print PathAndFile
  Next oFile
 
End Sub
Public Function AllFiles(ByVal FullPath As String) _
  As Collection
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject

'PARAMETER: FullPath = FullPath to folder for
'which you want all files

'************************************************

Dim oFs As New FileSystemObject
Dim sAns As New Collection
Dim oFolder As Folder
Dim oFile As file
Dim lElement As Long

If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)
 
    For Each oFile In oFolder.Files
      If Right(oFile.Name, 4) = ".bmp" Then
        sAns.Add oFile.Name
      End If
   Next
End If
'Debug.Print sAns.Count
Set AllFiles = sAns
ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function

 

[MajP]

The biggest feature I would still like to add is a "Light Load". Currently it loads all of the nodes on open. In this example no problem. I have a tree with 50K nodes and that can take a real long time. Ideally you would simply add the top level, and then when the user expands a node it only loads the next level for that node. Unfortunately there is a problem doing that. If you do not add the child node (at least 1) there is no plus symbol so you do not know to expand it. The trick I am thinking is adding the first child node per each node in the level. That one is going to take some thinking but I think it is doable. That is not specific to your case
For your's need to try to update the autolevel values on drag and drop without having to reload the whole tree.
Besides that I think you have covered about as many features as you can cram into a tree view.

 

[dgreen]

Our dummy dataset is definitely not my full data load. I also would want to ensure that this loads, updates and refreshes as quickly as possible. Looking forward to seeing how you figure out this option.

 

[gemma-the-husky]

Some years ago, I posted an xml file tree walker previously in the sample databases area. It iterates an xml file, and exports the structure to a table.

It basically walks the xml tree using the msxml dom document model, and extracts all the different nodes it finds with their position in the tree, assuming that the xml file is properly structured - ie consists of n instances of a repeating structure. The library includes a function to check that the file is indeed a valid xml file.

This analysis formed the basis of a system I developed to collect and analyse EDI files into a linear format for subsequent processing.


The biggest problem I found was that vba doesn't give you a pointer type, so you can't simply define a new object as a pointer to a structure, and build a dynamic linked structure in memory. Some said you can, but I just can't get it. With pointers you can simply create a new instance of a record on the heap (or is it stack), and link it into the appropriate order in the of the structure by manipulating the pointers of the previous and next records at the point you want it to be inserted. It doesn't need sorting - it already is sorted logically - Its just hard to do this without a pointer. I believe this is how database maintain their internal indexes, with b-tree structures I imagine.

@MajP
I just checked both an xml file, and a folder structure, and they seem to preload all the nodes. Maybe you need a different mechanism to load your 50K nodes.

 

[MajP]

@MajP
I just checked both an xml file, and a folder structure, and they seem to preload all the nodes. Maybe you need a different mechanism to load your 50K nodes.

Already figured it out, as I predicted it would work. Just add a single child node per level and expand the next lower levenel on the tree view expand event. I am going to make it an optional parameter to load full or load light. That way can compare to see if it makes a big difference, which I expect will be.