Treeview address (1 Viewer)

[ClaraBarton]

@MajP
Because I'm looking for an address and the nodetext is very lengthy, I changed the NodeText to NodeLevel. That's not right either.
What I'm looking for, and I think it's in E2E... sort of
Each item has a DocNo that will be the ending number.
But the LOPath of the locations should be:
Top Drawer - 1
Numeric - 1 - 1
Religion - 1 - 2
On down to
Bottom Drawer - 3
Computer - 3 - 1
Excel - 3 - 1 - 3
Definitely outside my skillset.

Public Sub RecurseIGTree(Optional ParentID As Variant = Null, Optional RS As DAO.Recordset = Nothing, Optional Path As String = "")
  'This assigns the levels and tree sort
  'if the top level parent ID is null do not pass in anything
 
  Dim strCriteria           As String
  Dim bk                    As String
  Dim PathIn                As String
 
    If RS Is Nothing Then
    'On the first call to the method open a recordset then pass it in recursion
  Dim strSql                As String
 
    strSql = "qryUnion"
    Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
   
    If IsNull(ParentID) Then
      'nothing passed in at start
      strCriteria = "ParentID = 'LO'"
      RS.FindFirst (strCriteria)
      If RS.NoMatch Then
         MsgBox "There is no record with a Parent ID of " & ParentID
         Exit Sub
      End If
    End If
   
  Else
    'all other calls you pass in the parentID
    strCriteria = "ParentID = '" & ParentID & "'"
   
  End If
   Debug.Print strCriteria
  RS.FindFirst (strCriteria)
 
    PathIn = Path
 
  Do Until RS.NoMatch
   bk = RS.Bookmark
   If RS!ParentID = "LO" Then Path = ""
   'reset the path when you come back to a top level node
     
   'This reset has to occur in the loop to account for coming back to root level
   If RS!ParentID = "LO" Then Path = ""
   'To keep you from adding to the path within a level
   Path = PathIn
 
   If Path = "" Then
        'Items need a path
        Path = " > " & RS!NodeLevel
   Else
      If RS!Identifier = "LO" Then Path = Path & " > " & RS!NodeLevel
   End If
   Debug.Print Path
   If RS!Identifier = "IT" Then Debug.Print RS!nodeText
   If RS!Identifier = "LO" Then
     strSql = "Update tblLocations set LOPath = '" & Path & "' where LocID = " & Replace(RS!ID, RS!Identifier, "")
     Debug.Print strSql
     CurrentDb.Execute strSql
   Else
     strSql = "Update tblItems set ITPath = '" & Path & "' where ItemID = " & Replace(RS!ID, RS!Identifier, "")
     Debug.Print strSql
     CurrentDb.Execute strSql
   End If
'     Debug.Print
     'The current Record is passed as the parent.
     RecurseIGTree RS!ID, RS, Path
     RS.Bookmark = bk
     RS.FindNext strCriteria
  Loop
 
End Sub

 

[MajP]

I am not following your numbering scheme. Is it the outline level like E2E demo
1
--1.1
--1.2
--1.3
-----1.3.1
-----1.3.2
2
3

 

[ClaraBarton]

Exactly. I just don't get how to get there. The LOPath is NOT what I want but what your sub is returning after I messed with it. I need to turn node texts to numbers.

 

[MajP]

These are the values that I normally save for most Trees.

TreeviewSort - If you read the nodes from the top to bottom that is the order they appear regardless of level
NodeLevel - How far it is indented in the branch. Top level is 1
LevelSort - For a give group of child nodes this is the order that the siblings appear. Needed to add to the LevelIdentifier
LevelIdentifier - Outline level

Loop the nodes
When you loop the nodes iterate from top to botton so that is the treeview sort order. You can sequentially number them as you loop.
Node level comes from the function getNodeLevel
If the returned node is a sibling of the previous node then increment the Level sort else restart at 1
for the current node, get its parent levelIdentifier that was already saved in the table since you would always read that before the current node and your current level sort. Concatenate to get the new levelidentifier

Public Sub UpdateSortAndLevel()
  'On Error GoTo UpdateSortAndLevel_Error
  Dim i As Variant
  Dim LevelSort As Integer
  Dim strSql As String
  Dim nd As Node
  Dim Identifier As String
  Dim Level As Integer
  Dim PK As Long
  Dim ParentNode As Node
  Dim ParentLevelIdentifier As String
  Dim TopLevelCounter As Integer
  Dim tvw As TreeviewForm
 
  Set tvw = mdlGlobalVariables.ActiveTreeView
 
  For Each nd In tvw.TreeView.Nodes
    i = i + 1
   
    Identifier = tvw.getNodeIdentifier(nd)
    Level = tvw.GetNodeLevel(nd)
    PK = CLng(tvw.getNodePK(nd))
    Set ParentNode = nd.Parent
    If Level <> 1 Then LevelSort = tvw.GetNodeLevelSort(nd)
  '  Debug.Print "Key " & nd.key & " PK " & PK
   If Identifier = "Header_" Then
      If Level = 1 Then
        TopLevelCounter = TopLevelCounter + 1
        strSql = "UPDATE HeadersT set TreeviewSort = " & i & ", NodeLevel = " & Level & ", LevelIdentifier = " & TopLevelCounter & " where HeaderID = " & PK
      Else
        ParentLevelIdentifier = GetLevelIdentifier(tvw.getNodePK(ParentNode), "Header_")
       
        'Debug.Print "Update Sort and level node " & nd.Key & "Parent " & ParentNode.Key & "  ParentLevelIdentifier  " & ParentLevelIdentifier & vbCrLf
        strSql = "UPDATE HeadersT Set treeviewSort = " & i & ", NodeLevel = " & Level & ", LevelSort = " & LevelSort & ", LevelIdentifier = '" & ParentLevelIdentifier & "." & LevelSort & "' where HeaderID = " & PK
      End If
   ElseIf Identifier = "BillItem_" Then
        ParentLevelIdentifier = GetLevelIdentifier(tvw.getNodePK(ParentNode), "Header_")
        strSql = "UPDATE BillItemsT Set treeviewSort = " & i & ", NodeLevel = " & Level & ", LevelSort = " & LevelSort & ", LevelIdentifier = '" & ParentLevelIdentifier & "." & LevelSort & "' where BillItemID = " & PK
   End If
   'Debug.Print "in Level updates: " & strSql
   If strSql <> "" Then
     CurrentDb.Execute strSql, dbFailOnError
   End If
 
 Next nd

    On Error GoTo 0
    Exit Sub

UpdateSortAndLevel_Error:
    'Not set error on the tree view
    If Err.Number <> 91 Then MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateSortAndLevel, line " & Erl & "."

End Sub

This example has two tables in the union "headers" , "billIitems" similar to Locations and Items.