ItemGenie location address

[ClaraBarton]

@MajP
Some years ago I used your treeview to organize some file cabinets. I had a
table of DRawers,
a table of DIvisions,
a table of FOlders,
and a table of ITems.
Then I used a union query for the treeview.
Each item had an address of
DRI:FD:IT or A:1:3:278. Or B:::45. All Good.
Then you came out with your ItemGenie and I really liked the logic. So I revamped the whole thing. It works great except for the LocAddress.
My items all have the same number as always and my drawers have the same letter. I can use a query to assign these.
However, I'm having a struggle with the rest of the address.
I'd like to use the ParentID (ParID) and the Nodelevel and the TreeSort to create a formula so that if they move, their address will change accordingly.
Here is a picture of part of the Location table. Am I trying to do the impossible?

 

[MajP]

Here is a picture of part of the Location table. Am I trying to do the impossible?

No, in fact I demo this concept in several places
In this image the "paragraph" outline numbering is done this way by a recursive call. As you move the node it recreates the outline number (location)
TopLevel.Child.grandchild.greatgranchild........levelOrder

https://www.access-programmers.co.uk/forums/attachments/e2e-jpg.107131/

I will try to demo this in item genie

 

[ClaraBarton]

I missed that in E2E. I'll study it. Knowing I'm not off the wall is a big deal.

 

[MajP]

There are two ways to do this. You can leverage the tree once it is loaded or you can do a recursive loop of the data. The E2E uses the tree, but it is kind of clunky. I used an intermediate dictionary to hold the values so I did not have to do it recursively.

Here is a more generic method using just the data and not the tree. I am going to admit this is hard to wrap your head around unless you done a lot of recursion.
To visualize how this works it is a depth first search. You start looping the recordset but first check if the record has child records. If it has child records start looping the child, but check if the child has child records, then start looping them....
Until you get to a node that does not have any children. At that point you pick up looping the siblings
Basically when you come to the point where you can go no farther down the branch you start falling out of the function and returning to the function that called.

I created in the location table LocationPath and in the items table ItemPath fields.

Then I recurse the union query and update the paths.

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
 
  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 = "qryNodeLocations_Items_ItemSort"
    Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If IsNull(ParentID) Then
      'nothing passed in at start
      strCriteria = "ParentID = 'Location_'"
      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
 
  RS.FindFirst (strCriteria)
 
  Do Until RS.NoMatch
   bk = RS.Bookmark
   If RS!ParentID = "Location_" Then Path = ""
   'reset the path when you come back to a top level node
   If Path = "" Then
        'Items need a path
        Path = RS!nodetext
   Else
      If RS!Identifier = "Location_" Then Path = Path & ">" & RS!nodetext
   End If
   Debug.Print Path
   If RS!Identifier = "Item_" Then Debug.Print RS!nodetext
   If RS!Identifier = "Location_" Then
     strSql = "Update tblLocations set LocationPath = '" & Path & "' where Location_ID = " & Replace(RS!ID, RS!Identifier, "")
     Debug.Print strSql
     CurrentDb.Execute strSql
   Else
     strSql = "Update tblItems set ItemPath = '" & Path & "' where Item_ID = " & 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

Image

Dynamic Paths

The only issue with that code is that it is updating the entire table and this could be slow with a giant table. When you move a node you can only change the paths of that node and all of its successors below within its branch. This code could be modified to do that.
If your tree is relatively small then it may be simpler to just update the location for all nodes . If you do not need to display it then you can just update when you close the tree.

 

[MajP]

Let me redo that. I can see that is not correct for the dining room shelves.

 

[ClaraBarton]

Just... Wow!
On E2E and Genie.. I am completely confused about LabelEdit. What calls it? It's shown as a property of the form in the object browser but I don't get it.

 

[MajP]

Here is the fix

Public Sub RecurseIGTree(Optional ByVal ParentID As Variant = Null, Optional RS As dao.Recordset = Nothing, Optional ByVal 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 = "qryNodeLocations_Items_ItemSort"
    Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    If IsNull(ParentID) Then
      'nothing passed in at start
      strCriteria = "ParentID = 'Location_'"
      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
 
  RS.FindFirst (strCriteria)
 
 
  pathIn = Path
 
  Do Until RS.NoMatch
   bk = RS.Bookmark
 
   '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 = "Location_" Then Path = ""
   'To keep you from adding to the path within a level
   Path = pathIn
 
   If Path = "" Then
        'Items need a path
        Path = RS!nodeText
   Else
      If RS!Identifier = "Location_" Then Path = Path & ">" & RS!nodeText
   End If
   Debug.Print Path
   If RS!Identifier = "Item_" Then Debug.Print RS!nodeText
   If RS!Identifier = "Location_" Then
     strSql = "Update tblLocations set LocationPath = '" & Path & "' where Location_ID = " & Replace(RS!ID, RS!Identifier, "")
     Debug.Print strSql
     CurrentDb.Execute strSql
   Else
     strSql = "Update tblItems set ItemPath = '" & Path & "' where Item_ID = " & 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

The only way I could understand this is to follow the logic in the debug. In short each time you call the function you path in a part of a path and each node in that group of child records should add to it.
If I pass in DiningRoom > Office to the next function call then Left Shelf should only add to the path for its children. But it was adding LeftShelf in the current function too. So you ended up with a sibling in the path.

 

[ClaraBarton]

Whew! Now to make it work for me. Thank you so much!

 

[MajP]

There is an afterLabelEdit and beforelabeledit event procedure of an active X treeview.

This event occurs when you edit the node text. The procedure gets the new node text passed into it as an argument. You also will know the active node since that is a property of a treeview.

Private Sub xTree_AfterLabelEdit(Cancel As Integer, NewString As String)
  Dim Description As String
  Dim PK As Long
  Dim Identifier As String
  Dim strSql As String
  Identifier = tvw.SelectedNodeIdentifier
  PK = tvw.SelectedNodePK
  If Identifier = "Location_" Then
   strSql = "update tblLocations Set LocationName = '" & NewString & "' where Location_ID = " & PK
  ElseIf Identifier = "Item_" Then
    MsgBox "Item Names are concatenated with quantity so this change will not persist. Need to decide benefit of qty vs edit"
    'strSql = "update t_E2E Set Level_ID = '" & LevelID & "', Category_Descr = '" & Description & "'WHERE E2E_ID = " & CLng(PK)
    Cancel = True
  End If
  If strSql <> "" Then CurrentDb.Execute strSql
End Sub

I rarely use this event because most of the time the Node text is a concatenation of lots of information. So looking at the code it is left over from E2E demo and not updated to the item genie.

There is a lableEdit property which can be set to allow or disallow editing

The LabelEdit property in a VBA TreeView control determines whether users can edit the text labels of tree nodes at runtime. Here's how it works and how to use it:

• Purpose: Enables or disables in-place editing of node labels.
• Values:
  • tvwAutomatic: Users can click once to edit the label.
  • tvwManual: You control when editing starts (e.g., via double-click or context menu).

 

[ClaraBarton]

Thanks.