How to show all nodes in tree view for repetitive hierarchy?

I'm an access hobbyist, not a professional, so could use some help here for an application I'm working on.

I am trying to create a tree view control which shows all of the subunits (and their subunits, and so on) in a military formation.  For instance, a battalion has three companies, each company has three platoons, each platoon has three squads.

To start with, I used this excellent article from Experts Exchange:
Expanding a Hierarchical Data Structure

This article was great, and showed me how to create a tree view showing all of the subunits (and their subunits), but the problem is that it only shows each subunit, and its subunits, once, and I need to show all of them.  In other words, it showed one company (and all of its subunits and subsubunits) rather than three (with all of their subunits, etc.).  

I figured out how to show the correct number of subunits, but the tree view still only shows the subsubunits for one subunit rather than all of them.  In other words, it will show three companies, but only the platoons (and squads) for one of the companies.   Here is a screen shot of the tree view control so far;  note that the first two "Grenadier Companies" are "empty", and there is no + to expand them.  The same applies to the platoons, etc--only one subunit of each type is populated.
I thought I could fix this with a simple loop, but I cannot get it to work correctly--can anyone point me in the right direction?  Here is the code I'm using for creating the tree view, let me know if anything else would be helpful:

Sub AddTreeData(objTV As TreeView, rsTreeViewAdd As DAO.Recordset, strFIDField As String, strSubField As String, strParentField As String, strDescField As String, intSubQty As Integer, Optional varParentID As Variant)
    Const Modulename As String = "mTreeViewCreateUnit"
    Const subname As String = "AddTreeData"
    Dim nodChild As Node
    Dim nodParent As Node
    Dim strLabel As String
    Dim strNodeID As String
    Dim strCriteria As String
    Dim strBookmark As String
    Dim strUnitDesc As String
    Dim strSubUnitParentNode As String
    Dim strSubUnitsSQL As String
    Dim intHierUnitCount As Integer
    Static intUnitCount As Integer
     Dim rsSubUnits As DAO.Recordset
'    On Error GoTo HandleErrors

'   Test for a circular reference
    If rsTreeViewAdd(strSubField) = rsTreeViewAdd(strParentField) Then GoTo EH_CircularReference
'   Test for Infinite Loop
    If intSubUnitCount = 1000 Then GoTo EH_InfiniteLoop
    ' If the optional parameter is missing, then this is the first(non-recursive) call to this function.
    ' Set the critieria to look for a ParentUID of 0.
    If IsMissing(varParentID) Then
       strCriteria = "[ParentUID] = '0'"

        ' Otherwise, extract the SubUID portion of the nodeID, which was passed as an optional parameter.
          strCriteria = "[ParentUID] = '" & Mid(varParentID, InStr(1, varParentID, "N") + 1, 8) & "'"

        ' Define the parent node
        Set nodParent = objTV.Nodes("node" & varParentID)
    End If
    ' Look for records having the specified ParentUID
     rsTreeViewAdd.FindFirst strCriteria
    strSubUnitParentNode = "node" & rsTreeViewAdd(strParentField) & "N" & rsTreeViewAdd(strSubField) & Str(1)

    Do Until rsTreeViewAdd.NoMatch

        intUnitCount = 0
'       strSubUnitParentNode = rsTreeViewAdd(strParentField) & "N" & rsTreeViewAdd(strSubField) & Str(intHierUnitCount)

       Do While intUnitCount < rsTreeViewAdd!SubQty
            intUnitCount = intUnitCount + 1
            intSubUnitCount = intSubUnitCount + 1
'            strSubUnitParentNode = rsTreeViewAdd!ParentUID & "N" & rsTreeViewAdd!SubUID & Str(intSubUnitCount)
'            strSubUnitParentNode = "0N110420011"

 '           If intUnitCount = 1 Then intHierUnitCount = intSubUnitCount
    '       Read node caption from the UnitDesc field
            strUnitDesc = rsTreeViewAdd!UnitDesc
    '       Create a new nodeID in format ParentUID & "N" & SubUID & SubUnitCount
            strNodeID = "node" & rsTreeViewAdd(strParentField) & "N" & rsTreeViewAdd(strSubField) & Str(intSubUnitCount)

            ' If optional parameter is missing (first call to this function)...
            If Not IsMissing(varParentID) Then
              'add new node to the next higher node for this record
               Set nodChild = objTV.Nodes.Add(nodParent, tvwChild, strNodeID, strUnitDesc)

                ' Otherwise, add new node to the top level of the tree
                Set nodChild = objTV.Nodes.Add(, , strNodeID, strUnitDesc)
            End If

        ' Bookmark our place in the recordset so that we can resume the search from the same point after the recursive call to this function.
        strBookmark = rsTreeViewAdd.Bookmark

'        AddTreeData objTV, rsTreeViewAdd, strFIDField, strSubField, strParentField, strDescField, rsTreeViewAdd!SubQty, strSubUnitParentNode

        ' call this function recursively for "children"
        AddTreeData objTV, rsTreeViewAdd, strFIDField, strSubField, strParentField, strDescField, rsTreeViewAdd!SubQty, rsTreeViewAdd(strParentField) & "N" & rsTreeViewAdd(strSubField) & Str(intSubUnitCount)

        ' Return to bookmarked place in the recordset
        rsTreeViewAdd.Bookmark = strBookmark

        ' Find the next record having the same parentID
        rsTreeViewAdd.FindNext strCriteria

    Exit Sub
    MsgBox "Exiting because of a circular reference in which a subunit was determined to be it's own parent unit."
    Exit Sub

    MsgBox "Exiting because of infinite loop."
    Exit Sub

    Dim intAction As Integer
    'call generic error handler, passing it the error
    'number and description, as well as the module name
    'and subroutine name
    intAction = ErrorHandler(lngErrorNum:=Err.Number, _
        strErrorDescription:=Err.Description, _
        strModuleName:=Modulename, _
'   Evaluate return value to determine what action to take
    Select Case intAction
        Case ERR_CONTINUE
            Resume Next
        Case ERR_RETRY
        Case ERR_EXIT
            Resume ExitHere
        Case ERR_QUIT
        End Select

End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
If you want a more in-depth explanation of the treeview control, you can watch this video tutorial series I created on treeviews:
That particular series is focused on pulling data from a single hierarchical table, but if you need to pull from multiple tables, I touch base on that in this online presentation:

I hope that can help you get a better understanding.
tmreiterAuthor Commented:
Thanks, those videos look helpful and I'll certainly watch them, but I think that in this case the problem is VBA-loop related.
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Could you provide a screenshot of the table data, with the name, ID and parentID of each record visible?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

tmreiterAuthor Commented:
I think this is what you're asking for...  Note that the ParentUID and SubUID fields are text data.  

One of the difficulties I've encountered in doing this is ensuring that each of the duplicate subunits (ie, each of the three companies) has a unique node number for the tree view, even though they have identical ParentUIDs and SubUIDs (ie, they are the same "units" as far as the tables are concerned.  I've tried to address this by adding a counter number and adding that to the node number for the tree view, which has worked for what I've done so far.

Data Fields
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Okay, I think it see it more clearly now. You want some items to appear multiple times in the treeview, allthough they only appear once in the table. That complicates it quite a bit, especially in a hierarchical structure, since the amount of sub nodes multiplies.

I did something similar to this a while ago for someone else, but I will have to go look for it. In the meantime, it would be helpful if you could export your table to a csv, and attach it here.
tmreiterAuthor Commented:
Thanks a lot; I've attached the CSV file, I think this is the only table you'll need.  Note that I don't use the FID column and will probably delete it.  Note also that the HierLevel field shows the "depth" in the heirarchy--0 is the top, 4 is the bottom.  SubQty is the number of each subunit existing in its parent unit.  ParentUIDs and SubUIDs are taken from the UID column of a separate table which includes all units, of whatever level in the Hierarchy.  

Initially I didn't think it would be very complicated to show duplicate hierarchical entities as individual nodes, but I have to say that I have not been able to figure it out despite several days of effort...  

I also expected that this kind of hierarchical database would be fairly common--in addition to military formations, for instance a company might have several regional offices, each overseeing local sites, which are all identical from a hierarchical perspective but yet are individual entities (each has their own manager, address, etc.).  While I've not been able to find any similar databases via Google, etc.  I've wondered if I've just been searching with the wrong terms.
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
I will take a look at the file.

The structure is fairly common, your data setup however is not. Of course a company could have several regional offices, but they would not call them all by the same name. Sure, they are of the same LEVEL in the hierarchy, but they wouldn't share name.

Now I admit I have no knowledge of military rank nor how it is structured, but I imagine they wouldn't call all Brigadier companys for simple brigadier company, but more likely it could be 1st brigadier, 2nd... or Alpha company or something. In that way, each Brigadier company would get 1 entry in the table.

Now I don't know what your purpose for the app is (Please share) but for most purposes I think including the name would make more sense. And having 1 entry per unit in the table. Imagine you wanted to take it a step further and wanted to list each soldier in each company. Without a unique record for each company (Which you don't have now, you have 1 record for 3 brigadier companies) you wouldn't have a unique key to assign to the soldiers record.

Does that make sense?
tmreiterAuthor Commented:
You are right to a certain extent--companies are usually labelled 1st, 2nd, 3rd, or A, B, C, etc.  But this doesn't really work as you descend through the hierarchy--for instance, there are three of the same platoons in each company, and generally they are labelled 1, 2, 3 within each company, and not 1-9 for the whole battalion.  Even more so for squads--ie, each platoon has squads 1-3, rather than squads 1-x at the battalion level.  

But what makes the whole thing unworkable is that you're only looking at one type of battalion, when I need to model a couple of dozen.  Many of them use the same subunits, so the number of different units would become unmanageable...   For instance, a single type of  tank or squad could be used a few dozen times within a single type of battalion, and could be used within several different types of battalions.

I recognize the need for unique node IDs in the treeview, but thought (and continue to think) that that can be dealt with by adding a unique number (determined by a counter within the loop) to the end of the existing node ID, which I did with the last bit of this line of code:
 strSubUnitParentNode = rsTreeViewAdd!ParentUID & "N" & rsTreeViewAdd!SubUID & Str(intSubUnitCount)

Open in new window

This seems to work as long as the looping works correctly, but that's where I'm encountering problems.

A few words about this application--basically I'm creating an external database for players of a commercial wargame, which would allow them to create and track units outside of the game.  This tree view is part of the process of creating a unit, and is intended to represent the template for generic "Grenadier Battalion"--once such a unit is selected/created, there will be a separate table (and treeview) showing actual, individual units created from the generic model.  Unlike this table, that table would include details about actual individual units--losses, commanders, etc.
tmreiterAuthor Commented:
Any chance of getting more feedback from anyone on this?
tmreiterAuthor Commented:
So no other tips from the experts?  Assistance would be appreciated.
tmreiterAuthor Commented:
After much trial and error, I finally figured out how to do what I wanted; ultimately it involved a simple tweak (adding a loop) to the code for the SQL query creating the table which was then used to create the TreeView, rather than the code involving the TreeView itself.  The key was to have the table include rows for all units (by looping for units with a quantity of more than one) and then using letting the basic TreeView code create the TreeView on that basis.

Since I've resolved this myself, I'm not sure how to close it out?  Moreover, while Anders' last comment was not a solution per se, it got me thinking along lines that ultimately resolved the issue, so I'd like to throw him some points.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmreiterAuthor Commented:
I didn't get much interest in this problem, so ultimately resolved it myself.  General information about the solution is in my last comment
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.