Solved

Re-number Treeview nodes after deleting a child node

Posted on 2014-11-25
8
39 Views
Last Modified: 2016-05-27
I have a treeview on a userform in MS Word. The treeview is populated from an excel spreadsheet using a ADO connection. There are two columns in the sheet: Task No and Description.
The Task No column contains a unique entry in  the form of numbers formatted as text:
1
1.1
1.1.1
1.1.2
2
2.1
and so on. The Description column just contains text corresponding to the Task No cell.

I can populate the treeview without any issues and add, update the nodes within it. However, if I want to delete a node, this is far more complex. For example, If I want to delete 1.1 and all children beneath it e.g. 1.1.1, 1.1.1.1, 1.1.2 etc; then the next child node and all children beneath that should be renumbered, starting from the node that has been deleted. e.g. 2.1 becomes 1.1, 3.1 becomes 2.1 etc;

The key for the nodes consists of the Task No with an alpha character appended to the end e.g. "1.1a" and the text for the node consists of the Task No, a hyphen and the description e.g. "1.1-Description text".

here is the code that I have so far:

If Me.tvScalar.SelectedItem Is Nothing Then
    MsgBox "Please select a node", vbInformation, "No node selected"
    Exit Sub
End If

nodeKey = tvScalar.SelectedItem.Key

nodeBool = countSeparators(nodeKey, ".")

If nodeBool = 0 Then
    MsgBox "Cannot delete Training Objectives"
    Exit Sub
ElseIf nodeBool = 1 Then
    MsgBox "Cannot delete an Enabling Objective"
    Exit Sub
End If

txtMsg = MsgBox("Deleting a parent task will delete all tasks below it. This action cannot be undone. Are you sure you want to delete this task?", vbExclamation + vbOKCancel, "Delete task")

If txtMsg = vbOK Then

    'get the scalar nodes
    Set objRecordset = New ADODB.Recordset

    'form the connection string. Use IMEX=0 to make readwrite
    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties='Excel 8.0;IMEX=0'"
    
    sqlString = "SELECT [Task No], [Description] FROM [Task Report$] ORDER BY [Task No];"

    'create recordset. Use adOpenDynamic to make editable
    objRecordset.Open sqlString, connString, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic

    'we need to do some preparatory work first, getting node references
    '*****************************************************************
    
    'get a reference to the selected node
    Set nd = Me.tvScalar.SelectedItem
    selNodeIndex = nd.Index
    
    
    
    'get the key of the parent node of the node that is selected
    Set tvParentNode = nd.Parent
    tvParentKey = tvParentNode.Key
    
    'trim the trailing 'a' from the parent node key
    tvTrimParentKey = Left(tvParentKey, Len(tvParentKey) - 1)
    
    
    'get a reference to the next node in the chain for the one selected
    If Not nd Is Nothing Then
        Set tvNextNode = nd.Next
'        Set nd = Me.tvScalar.SelectedItem
    End If
    'preparatory work done
    '*******************************************************************
    
    
    'now lets start going through the selected nodes chidren and setting any reference to them
    'in the Excel Task Report to null. Cannot delete a row in excel so the only option is to set
    'values to null.
    If Not nd Is Nothing Then
        For i = selNodeIndex To nd.LastSibling.Index - 1
            
            'set reference to the selected item
            Set tvNode = Me.tvScalar.Nodes.Item(i)
            
            'get the key of this node
            tvKey = tvNode.Key
            
            'trim off the trailing 'a'
            tvTrimKey = Left(tvKey, Len(tvKey) - 1)
            
            'now lets set the value of the Task No and Description for current node to null
            With objRecordset
                If Not .EOF Then
                    .Find "[Task No]=" & "'" & tvTrimKey & "'"
                    If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimKey) Then
                        If Not .EOF Then
                        Debug.Print .Fields("Task No").Value
'                        .Fields("Task No").Value = vbNullString
'                        .Fields("Description").Value = vbNullString
'                        .Update
                        End If
                    End If
                    
                    'remove the item
                    .MoveFirst
                End If
            End With
        Next i
    End If
    
    
    Me.tvScalar.Nodes.Remove (nd.Index)
    
    Set nd = Nothing
    
    //******This is where help is needed************************************************

    'now we need to re-number the remaining nodes in the selected chain
    For i = tvParentNode.Index To tvParentNode.LastSibling.Index
    
        Set tvNextNode = tvScalar.Nodes.Item(i)
        
        'trim off the trailing 'a' from the current node key
        tvNextKey = tvNextNode.Key
        
        tvTrimNextKey = Left(tvNextKey, Len(tvNextKey) - 1)
        
        'count the number of separators in the current node key
        tvKey = tvNextNode.Key
        count = countSeparators(tvKey, ".")
        
        Select Case count
            Case 0
                'do nothing as this is a TO
            Case 1
                'do nothing as this is an EO
            Case 2
                'KLP
                tvTrimKey = tvTrimParentKey & "." & tvParentNode.Children
                
                'find the current node and set the Task No to the new key (tvTrimKey)
                With objRecordset
                If Not .EOF Then
                    .Find "[Task No]=" & "'" & tvTrimNextKey & "'"
                    If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimNextKey) Then
                        If Not .EOF Then
                        Debug.Print tvTrimNextKey
        '                .Fields("Task No").Value = tvTrimKey
        '                .Update
                        End If
                    End If
                    .MoveFirst
                End If
                End With
            Case 3
                tvTrimKey = tvNextNode.LastSibling.Key & "." & tvParentNode.Children
                
                'find the current node and set the Task No to the new key (tvTrimKey)
                With objRecordset
                If Not .EOF Then
                    .Find "[Task No]=" & "'" & tvTrimNextKey & "'"
                    If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimNextKey) Then
                        If Not .EOF Then
                        Debug.Print tvTrimNextKey
        '                .Fields("Task No").Value = tvTrimKey
        '                .Update
                        End If
                    End If
                    .MoveFirst
                End If
                End With
            Case 4
                tvTrimKey = tvNextNode.LastSibling.Key & "." & tvParentNode.Children
                
                'find the current node and set the Task No to the new key (tvTrimKey)
                With objRecordset
                If Not .EOF Then
                    .Find "[Task No]=" & "'" & tvTrimNextKey & "'"
                    If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimNextKey) Then
                        If Not .EOF Then
                        Debug.Print tvTrimNextKey
        '                .Fields("Task No").Value = tvTrimKey
        '                .Update
                        End If
                    End If
                    .MoveFirst
                End If
                End With
        End Select
    Next i
    
    
    'clean up recordset
    objRecordset.Close
    Set objRecordset = Nothing
Else
    Exit Sub
End If

Open in new window


Function countSeparators(myString As String, mySeparator As String) As Integer
    countSeparators = UBound(Split(myString, mySeparator))
End Function

Open in new window


I would also like to implement drag drop on the treeview so that users can drag nodes to another position and I am having trouble getting this to work. It will be a similar situation where nodes are renumbered to account for the movement of nodes further up or down the tree.

Any help gratefully received.
0
Comment
Question by:Strongs
  • 3
  • 3
8 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40466658
2 is not a child of 1 and should not be renumbered.
0
 

Author Comment

by:Strongs
ID: 40467286
Aikimark,

Correct. Root nodes will not be deleted or renumbered, only the nodes beneath them.

I have attached an image of an example treeview populated with nodes.Populated treeview
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40467393
so, your description should have stated that 1.2 becomes 1.1 and 1.2.1 becomes 1.1.1?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Strongs
ID: 40467416
That's right. The dark red nodes will never be deleted(1,2,3 etc;).
If 1.1 is deleted and by default all of its children, then 1.2 and all of its children then become 1.1, 1.1.1 etc. 1.3 becomes 1.2 and so on.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40468616
Algorithm:
When a node gets deleted, set a variable (PN) to its parent node.
Iterate the children of PN
  If the child node (CN) is not in sequence, renumber and its descendants


* sequence starts at 1
* renumbering recurses down the CN branch, replacing the highest levels of the name with the newly sequenced CN name.
0
 

Author Comment

by:Strongs
ID: 40468872
Spot on!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
split53 challenge 7 77
outlook 2013 key shortcuts 28 52
Turning python script into an applet 12 101
TSQL previous 5 23
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now