How to re-arrange nodes in treeview with drag n drop

I've got a treeview control to work well, I can populate it, drag n drop changing parent nodes etc.. using a modified version of the northwind sample code from msdn. But now I'm to the point that I need to re-arrange the nodes within there parent branch and I havent been able to figure it out. This is the code to do the normal drag n drop to a new parent from the sample:

Private Sub xTree_OLEDragOver(Data As Object, Effect As Long, _
        Button As Integer, Shift As Integer, x As Single, y As Single, _
        State As Integer)
    Dim oTree As TreeView

    'Create a reference to the TreeView control.
    Set oTree = Me!xTree.Object

    'If no node is selected, select the first node you dragged over.
    If oTree.SelectedItem Is Nothing Then
        Set oTree.SelectedItem = oTree.HitTest(x, y)
        'MsgBox oTree.SelectedItem.Index
    End If

    'Highlight the node being dragged over as a potential drop target.
    Set oTree.DropHighlight = oTree.HitTest(x, y)
    'MsgBox oTree.DropHighlight.Index
End Sub

Private Sub xTree_OLEDragDrop(Data As Object, Effect As Long, _
        Button As Integer, Shift As Integer, x As Single, y As Single)
    On Error GoTo ErrxTree_OLEDragDrop

    Dim oTree As TreeView
    Dim strKey As String, strText As String
    Dim nodNew As Node, nodDragged As Node
    Dim db As Database
    Dim rs As Recordset
    Dim ctrlDown As Integer
    ctrlDown = (acCtrlMask) > 0
    Set db = CurrentDb
     
    'Open the OUTAGE_BK_TREE table for editing.
    Set rs = db.OpenRecordset("OUTAGE_BK_TREE", dbOpenDynaset)

    'Create a reference to the TreeView control.
    Set oTree = Me!xTree.Object

    'If nothing is selected for drag, do nothing.
    If oTree.SelectedItem Is Nothing Then
    Else
        'Reference the selected node as the one being dragged.
        Set nodDragged = oTree.SelectedItem
        'If ctrlDown Then MsgBox "ctrl down"
               
        'If the node was dragged to an empty space, update the
        'OUTAGE_BK_TREE table and make  a root node.
        If oTree.DropHighlight Is Nothing Then
            'Save the key and the text to use when you re-add the node.
            strKey = nodDragged.Key
            strText = nodDragged.Text
            'Delete the current node for the employee.
            oTree.Nodes.Remove nodDragged.Index
            'Locate the record in the OUTAGE_BK_TREE table and update it.
            rs.FindFirst "[ID]=" & Mid(strKey, 2)
            rs.Edit
            rs![FedBy] = Null
            rs.Update
            'Add this employee as a root node.
            Set nodNew = oTree.Nodes.Add(, , strKey, strText)
            'Add all the child nodes for this employee.
            AddChildren nodNew, rs
            'If you are not dropping the node on itself.
        ElseIf nodDragged.Index <> oTree.DropHighlight.Index Then
            'Set the drop target as the selected node's parent.
            Set nodDragged.Parent = oTree.DropHighlight
           
           
            'Locate the record in the OUTAGE_BK_TREE table and update it.
            rs.FindFirst "[ID]=" & Mid(nodDragged.Key, 2)
            rs.Edit
            rs![FedBy] = Mid(oTree.DropHighlight.Key, 2)
            rs.Update
        End If
    End If

    'Deselect the node
    Set nodDragged = Nothing

    'Unhighlight the nodes.
    Set oTree.DropHighlight = Nothing

ExitxTree_OLEDragDrop:
    Exit Sub

ErrxTree_OLEDragDrop:
    'If you create a circular branch.
    If Err.Number = 35614 Then
        MsgBox "A supervisor cannot report to a subordinate.", _
            vbCritical, "Move Cancelled"
    Else
        MsgBox "An error occurred while trying to move the node.  " & _
            "Please try again." & vbCrLf & Error
    End If
    Resume ExitxTree_OLEDragDrop
End Sub

I tried changing the index of the dragged node to the highlightednode.index + 1 and that didnt work, index is read only.

I had thought about useing a keypress event, like while shift+ctrl held down just move the node. But I first wanted to try just getting the tree to just move the nodes around with out editing the table since the parent node wont change.
I wouldnt neccessarily be against using some sort of button to move the selected node up or down also, if anybody would now how to do that.
Thanks
LVL 10
norrin_raddAsked:
Who is Participating?
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.

Sayad Aziz AhmadAccounts ManagerCommented:
<But now I'm to the point that I need to re-arrange the nodes within there parent branch>

By rearranging do u mean reversing the drag-n-drop event to its previous state for example:

Parent Node1
    P1C1
    P1C2
Parent Node2
   P2C1
   P2C2


If the user drop P1C1 into Parent Node2 ... then ur Fedby field is updated with :
rs![FedBy] = Mid(oTree.DropHighlight.Key, 2)

do u want to cancel the update operation ? if yes, then no coding is required since user can again drag to the previous stage or u can flash a msgbox before updating like :

Dim Response as boolean,Title as String,Msg as String,Style as String

Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Droping Confirmation"
Msg = "Are You Sure To Drop Item Here"
Response = MsgBox(Msg, Style, Title )

If Response = True Then

            rs.FindFirst "[ID]=" & Mid(nodDragged.Key, 2)
            rs.Edit
            rs![FedBy] = Mid(oTree.DropHighlight.Key, 2)
            rs.Update
End If


You also need to change ur relevant portion of existing code to :

ElseIf nodDragged.index <> oTree.DropHighlight.index Then
           
            Set nodDragged.Parent = oTree.DropHighlight
           
           
            'Locate the record in the OUTAGE_BK_TREE table and update it.
            rs.FindFirst "[EmployeeID]=" & Mid(oTree.DropHighlight.Key, 2)
            rs.Edit
            rs![FedBy] = Mid(nodDragged.Parent.Key, 2)
            rs.Update
         
End If

Aziz
Sayad Aziz AhmadAccounts ManagerCommented:
Sorry no replacement required for this code :

ElseIf nodDragged.index <> oTree.DropHighlight.index Then
            'Set the drop target as the selected node's parent.
            Set nodDragged.Parent = oTree.DropHighlight
           
           
            'Locate the record in the OUTAGE_BK_TREE table and update it.
            rs.FindFirst "[ID]=" & Mid(nodDragged.Key, 2)
            rs.Edit
            rs![ReportsTo] = Mid(oTree.DropHighlight.Key, 2)
            rs.Update
         
End If

Aziz
norrin_raddAuthor Commented:
actually what i want to do is this:

take

parent1
    child1
    child2
    child3

and make it

parent1
    child1
    child3
    child2

using a drag and drop method prefferably, but if I can get it to work with other means I would.
thanks
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Sayad Aziz AhmadAccounts ManagerCommented:
using a drag and drop method if u drag child3 to the place of child2, it will show in the treeview accordingly but when you close and reopen the treeview form it will again sow the same ... since the drag method uses Hit(x,y) method to drop node and the same is not changed in table wherefrom you are retrieving the nodes .... if you want to change the place in table as well i can write code for you.

Aziz
Sayad Aziz AhmadAccounts ManagerCommented:
Hereunder is my code :

Prerequisites :

1) If you have Data Type Autonumber for your ID field then you need to change the data type to integer otherwise Access will not allow change into autonumber field.

ElseIf nodDragged.index <> oTree.DropHighlight.index And Err.Number <> 35614 Then
            'Set the drop target as the selected node's parent.
            Dim ChngId As Integer, OldId As Integer, NewId As Integer
            Set nodDragged.Parent = oTree.DropHighlight
           
            rs.MoveLast
            ChngId = rs!ID + 1
           
            rs.FindFirst "FedBy =" & Mid(nodDragged.Parent.Key, 2)
            OldId = rs!ID
            rs.Edit
            rs!ID = ChngId
         
            rs.Update
         
            'Locate the record in the OUTAGE_BK_TREE table and update it.
            rs.FindFirst "[ID]=" & Mid(nodDragged.Key, 2)
            rs.Edit
            NewId = rs!ID
            rs!Employeeid = OldId
            rs![FedBy] = Mid(oTree.DropHighlight.Key, 2)
            rs.Update
           
            rs.FindFirst "[ID]=" & ChngId
            rs.Edit
            rs!ID = NewId
            rs.Update
           
         
        End If

some fields name might not match to your fields so u need to carefully change all the fields viz. (ReportsTo - Fedby  / EmployeeId - Id) . However i have tried my best to use ur given field names.


Aziz

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
Sayad Aziz AhmadAccounts ManagerCommented:
i forget to tell that u need to change only the relevent code and rest code will be the same .......

In    Private Sub xTree_OLEDragDrop(Data As Object, Effect As Long, _
        Button As Integer, Shift As Integer, x As Single, y As Single)

Subroutine u need to replace code under

ElseIf nodDragged.index <> oTree.DropHighlight.index And Err.Number <> 35614 Then

maybe you don't have And Err.Number <> 35614    in ur code
                                ~~~~~~~~~~~~~~~~~

Aziz
norrin_raddAuthor Commented:
hey aziz,
thanks for the code, I'm still working on, got pulled off on something else for a little bit though. I did figure out that in my sql query that if I sorted by a different ID field (not the keyid)  that it would sort better in the treeview. I had set up  a field that would keep the records in the order I needed them in another older listview that I didnt think about.

The first time I ran the code you gave me it did give me an error, cant remember what it was now. I'll get back to it next week, just didnt want to keep you hanging.

thanks
radd
norrin_raddAuthor Commented:
hey aziz,
I'm going to have to go ahead and close this and give you the points. I think what you have will work, but I have been redirected off that project for an unknown time and I dont want to just keep this open. Once I get bact to it I will post something back here and let you know how its going.
Thanks again,
radd
Sayad Aziz AhmadAccounts ManagerCommented:
welcome you are at any time.

Aziz
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.