Solved Node level path (1 Viewer)

[ClaraBarton]

@MajP Me again
What am I doing wrong here?

Public Sub UpdateSortAndLevel()
10    On Error GoTo Err_Handler
           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
           Dim Path                   As String
          
20      Set tvw = GetItemTreeView
      
30      For Each nd In tvw.TreeView.Nodes
40        i = i + 1
        
50        Identifier = tvw.getNodeIdentifier(nd)
60        Level = tvw.GetNodeLevel(nd)
70        PK = CLng(tvw.getNodePK(nd))
80        Set ParentNode = nd.Parent
90        If Level <> 1 Then LevelSort = tvw.GetNodeLevelSort(nd)
          
100      If Identifier = "LO" Then
110         If Level = 1 Then
               TopLevelCounter = GetLevelIdentifier(PK, "LO")
'120           TopLevelCounter = TopLevelCounter + 1
130           strSql = StringFormatSQL("Update tblLocations" & _
                          " Set treesort = {0}," & _
                              " NodeLevel = {1}," & _
                              " LOPath = {2}" & _
                              " WHERE LocID = {3}", _
                              i, Level, TopLevelCounter, PK)
140         Else
150           ParentLevelIdentifier = GetLevelIdentifier(tvw.getNodePK(ParentNode), "LO")
160           Path = ParentLevelIdentifier & " > " & LevelSort
170               strSql = StringFormatSQL("UPDATE tblLocations" & _
                              " Set treesort = {0}," & _
                              " NodeLevel = {1}," & _
                              " LevelSort = " & LevelSort & "," & _
                              " LOPath = {2}" & _
                              " WHERE LocID = {3}", _
                              i, Level, Path, PK)
                            
                            Debug.Print i & "-" & Level & " = " & Path
180          End If
                        
190      ElseIf Identifier = "IT" Then
200           ParentLevelIdentifier = GetLevelIdentifier(tvw.getNodePK(ParentNode), "LO")
210           strSql = StringFormatSQL("UPDATE tblItems" & _
                              " Set treesort = {0}," & _
                              " NodeLevel = {1}," & _
                              " LevelSort = " & LevelSort & "," & _
                              " ITPath = {2}" & _
                              " WHERE ItemID = {3}", _
                              i, Level, Path, PK)
220      End If
        
230      If strSql <> "" Then
240        CurrentDb.Execute strSql, dbFailOnError
250      End If
      
260    Next nd

Exit_Handler:
270           Exit Sub
Err_Handler:
280           clsErrorHandler.HandleError "UpdateSortAndLevel", True
290           Resume Exit_Handler
End Sub
Public Function GetLevelIdentifier(PK As Long, Identifier As String) As String
  If Identifier = "LO" Then
    GetLevelIdentifier = DLookup("LocNo", "tblLocations", "LocID = " & PK)
  End If
End Function

This debug returns:

So on level 3 I'm not getting the top level, only 2 levels. I was getting it until I changed the PK to LocNo. I need to use this so I can change the organization of the levels.

 

[cheekybuddha]

So on level 3 I'm not getting the top level, only 2 levels.

Is that because you only Debug.Print when you are in a Level <> 1 ?

Perhaps move that line outside the End If ?

 

[MajP]

Public Function GetLevelIdentifier(PK As Long, Identifier As String) As String
  If Identifier = "LO" Then
    GetLevelIdentifier = DLookup("LocNo", "tblLocations", "LocID = " & PK)
  End If
End Function

I assume that this should be

    GetLevelIdentifier = DLookup("LOPath", "tblLocations", "LocID = " & PK)

When you read a node you get its parent path from the dlookup and add to it creating a concatenated path.
Node A
Node B
--Node C
--Node D
-----Node E

I am outling numbering my path
Node A (1)
Node B (2)
--Node C (2>1)
--Node D (2>2)
-----Node E (2>2>1)

You could make your path the PK instead.

Node A (A)
Node B (B)
--Node C (B>C)
--Node D (B>D)
-----Node E (B>D>E)

But then you would save the PK in the path and not the outline level.
You look like you are mixing both ideas.

I think in my example I did the text name too as an example.

 

[ClaraBarton]

Yes! exactly. Thank you so much.