Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Rearrange TreeView Nodes by Drag and Drop

Introduction.

I hope you found, last week’s tutorial on ImageCombo Control useful in your Microsoft Access Projects.  With the TreeView ImageCombo Control, we could create a beautiful Drop-down Menu, with several options, and accommodate it in a small space on the form. 

Likewise, in an earlier session, we learned how to Add new Nodes in a specific location in the node hierarchy or Delete a Node and add a new one to relocate a Node on the Tree View Control. 

This method requests the creation of a new record in the source table for the new node. Or delete an existing record and create a new one to move an existing node to make them permanent. In a way, with the use of Add/Delete Functions, we could add new Nodes or re-arrange the existing Nodes on the TreeView Control. As far as re-arranging Nodes are concerned, we have a better way to do it,  rather than deleting Nodes and re-creating them. Drag the Node from its current location and Drop it where we want it to be on the TreeView Control.  This is what we are going to learn in this episode

This simple approach needs only updating the change of the ParentID field value of related records, to make the change permanent.

The Topics Covered so far in Earlier Sessions.

  1. Microsoft TreeView Control Tutorial
  2. Creating Access Menu with TreeView Control
  3. Assigning Images to TreeView Control
  4. Assigning Images to TreeView Control-2
  5. TreeView Control Check-Mark Add, Delete Nodes
  6. TreeView ImageCombo Drop-down Access Menu

But, we may face some challenges while using this method and we will come to that little later in this Session.

Demo Data Table and Form.

We need a Table and a Form.  We already have a suitable table with the name Sample created in an earlier tutorial Session. If you have already downloaded the Demo Database, from the second link Page given above earlier, then you may use that Database for this Session also.  We will use the following objects from that Database for our Drag-Drop experiments:

  • Table: Sample
  • Form: frmSample

The TreeView Control Image on frmSample with demo data is given below for reference:

You may download the Demo Database (ProjectMenu.zip) from the Second Link Page given above and extract the ProjectMenu.accdb database.

New Form for Drag Drop Trial Runs.

  1. Open the ProjectMenu.accdb database.

  2. Make a copy of the table Sample and name it Sample_bk, to keep the original data safe for later use.  When we experiment with the drag-drop method, it is necessary to update the ParentId field value on the Sample demo Table.   But, we need the original data later, without these changes.

  3. Create a new Form with the name frmDragDrop.

  4. The frmDragDrop Form Design will look like the image given below when you finish with it.

  5. Insert the TreeView Control from the ActiveX Controls’ List and place it on the form, leaving enough space above the control, so that we can create two Command Buttons and a Heading Label above it.  Drag the sizing handle at the bottom right corner to make it large enough to display all the Nodes, without scrolling.

  6. Change the Name Property value of TreeView Control to TreeView0.

  7. Insert a Command Button above and left edge of the TreeView Control.  Change its Name Property Value to cmdExpand and Caption value to Expand All.

  8. Insert a second Command Button above and to the right edge of the TreeView Control.  Change its Name Property Value to cmdCollapse and the Caption Property Value to Collapse All.
  9. Insert a Label Control above the Command Buttons, wide enough to write the Heading as shown above, and change its Font Size 14.

  10. Ignore the ImageList Control, for the time being, I have commented out the Code Lines that modify the Node ImageList index numbers.  Later on, you can Import the ImageList Control with manually uploaded Images, from our earlier Tutorial Demo Database (from the 4th Link Page given above) and use it to display Node-Images on Nodes.  When Node positions are changed during Drag Drop actions we need to change Node Images also depending on the Node’s position (Root-level Node or Child-Node) on the TreeView Control.

    Drag-Drop Form Module Code.

  11. Display the VBA Code Module of the Form frmDragDrop, Copy and Paste the following VBA Code (this is only the first half of the Form Module Code)  into the frmDragDrop Form’s Class Module and Save the Form:

    Option Compare Database
    Option Explicit
    
    Dim tv As MSComctlLib.TreeView
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim imgListObj As MSComctlLib.ImageList
    Const KeyPrfx As String = "X"
    
    Private Sub Form_Open(Cancel As Integer)
    Set tv = Me.TreeView0.Object
    
    'Set imgListObj = Me.ImageList1.Object
    'tv.ImageList = imgListObj
    
    LoadTreeView
    
    End Sub
    
    Sub LoadTreeView()
    Dim strKey As String
    Dim strPKey As String
    Dim strText As String
    Dim strsQL As String
    
    strsQL = "SELECT * FROM Sample ORDER BY ID"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strsQL, dbOpenDynaset)
        
    tv.Nodes.Clear
    
    'Add all Items are added as Root Nodes
    Do While Not rst.BOF And Not rst.EOF
        strKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        tv.Nodes.Add , , strKey, strText
        
        'With tv.Nodes.Item(strKey)
        '    .Image = 1
        '    .SelectedImage = 4
        'End With
    
        rst.MoveNext
    Loop
    
    'Prepare to update the Parent-Key of Nodes
    'wherever applicable to move and position the Child Nodes
    strPKey = ""
    rst.MoveFirst
    Do While Not rst.EOF
        strPKey = Nz(rst!parentid, "")
        
        If Len(strPKey) > 0 Then
            strPKey = KeyPrfx & strPKey
            strKey = KeyPrfx & CStr(rst!ID)
            strText = rst!desc
            
            'Move the Child Node under it's Parent-Node
            Set tv.Nodes.Item(strKey).Parent = tv.Nodes.Item(strPKey)
            
    'Update Image and SelectedImage Properties
    'with ImageList Index numbers
            'With tv.Nodes.Item(strKey)
            '    .Image = 2
            '    .SelectedImage = 3
            'End With
    
        End If
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
    End Sub
    
    
    Private Sub TreeView0_NodeClick(ByVal Node As Object)
    Dim SelectionNode As MSComctlLib.Node
        
    'Ensure that the clicked node equals the selected node in the tree
    If Not Node Is Nothing Then
        Set SelectionNode = Node
           If SelectionNode.Expanded = True Then
                SelectionNode.Expanded = False
            Else
                SelectionNode.Expanded = True
            End If
    End If
    End Sub
    
    Private Sub cmdCollapse_Click()
    Dim tmpnod As MSComctlLib.Node
    For Each tmpnod In tv.Nodes
        If tmpnod.Expanded = True Then
            tmpnod.Expanded = False
        End If
    Next
    
    End Sub
    
    Private Sub cmdExpand_Click()
    Dim tmpnod As MSComctlLib.Node
    For Each tmpnod In tv.Nodes
        If tmpnod.Expanded = False Then
            tmpnod.Expanded = True
        End If
    Next
    
    End Sub

    I know you are familiar with the above Code, if you have already gone through the earlier Episodes, except for the LoadTreeView() subroutine with some changes. Here, populating the TreeView Nodes has been divided into a two-step process. 

    Briefly, this is what happens inside this Subroutine.

    • All the records on the Sample Table have been loaded as Root-level Nodes of TreeView Control, with an ID field value as Key, in the first step.

    • Again, these records have been read a second time and checked for a value in the ParentId field, if empty then the Node will be retained as Root-level Node.

    • If the ParentID field has a Value then identify the Node with the ParentID value as Node-Key and move the current Node as its Child Node, or its [Relative]   Parameter (of Add () Methodvalue gets updated.

    • Even though it appears that the two-step Node populating procedure is an unnecessary exercise, there is a good reason why we have to follow this method. We will come back to this little later and you will know it without much explaining.

    • On the Design of the Form, I have given an ImageList Control. You may insert the ImageList ActiveX Control and upload some images in it manually from the disk, or copy and paste this control with images from earlier Demo Database Downloads. In either case, please ensure that the name of the ImageList control is ImageList1. Otherwise, you need to change the name in the Code.

    • After that, enable the commented-out lines in Form_Open() Event Procedure.  Enable the following lines, by removing the comment symbol from the beginning of the line:

      'Set imgListObj = Me.ImageList1.Object
      'tv.ImageList = imgListObj
      

    • The TreeView0_OLEDragDrop() Subroutine (in the 2nd part of the VBA Code) enables the Image Index Parameters of Nodes, by removing the Comment symbols from those lines too.  With these changes, the Node Images will appear on the TreeView Control.  If you have an ImageList Control of your own with uploaded Images then change the Index Numbers based on what Image you would like to insert on the Nodes.

      The TreeView0_NodeClick() Event Procedure Expands the current node, if the Child-Nodes are in a collapsed state, otherwise, child Nodes will be Collapsed. Normally this action is controlled (without Code) by clicking on the +/- Symbol on the Tree-line of the Node with Child Nodes.

      The Subroutines cmdExpand_Click() and cmdCollapse_Click() Events Expands all Nodes and collapse all Nodes respectively.

      When the above Code Runs the display looks like the Form View Image given below:

    • You may save the frmDragDrop Form and open it in Normal View.  If everything went on well then you will see the above screen.  Try out the Expand All and Collapse All Command Buttons and check whether they are working too.  If not, then re-check whether the following settings are correct or not:

    • i)  The TreeView Control’s Name is: TreeView0

    • ii)  Display the Property Sheet of Exampand All Command Button and select [Event Procedure] in the On Click Event Property.

    • iii) Ensure that the same setting is intact for Collapse All Command Button also. 

    • iv) Click on a Node, having Child Nodes, to see whether they collapse or expand on repeated clicks.

    •  v) If ImageList Control is placed on the Form then its name must be ImageList1.

      Let us proceed with the second part of the VBA Code that implements the Drag-Drop Events.

    Second Half of the VBA Code.

  12. Copy the following Second Part of the VBA Code, on the frmDragDrop Form Module, that implements the Drag-Drop action, and Paste it below the existing Code:

    Private Sub TreeView0_OLEStartDrag(Data As Object, AllowedEffects As Long)
        Set Me.TreeView0.SelectedItem = Nothing
    End Sub
    
    
    Private Sub TreeView0_OLEDragOver(Data As Object, _
                                    Effect As Long, _
                                    Button As Integer, _
                                    Shift As Integer, _
                                    x As Single, _
                                    y As Single, _
                                    State As Integer)
        
        Dim SelectedNode As MSComctlLib.Node
        Dim nodOver As MSComctlLib.Node
        
        If tv.SelectedItem Is Nothing Then
            'Select a node if one is not selected
            Set SelectedNode = tv.HitTest(x, y)
            If Not SelectedNode Is Nothing Then
                SelectedNode.Selected = True
            End If
        Else
            If tv.HitTest(x, y) Is Nothing Then
            'do nothing
            Else
                'Highlight the node the mouse is over
                Set nodOver = tv.HitTest(x, y)
                Set tv.DropHighlight = nodOver
            End If
        End If
    
    End Sub
    
    
    Private Sub TreeView0_OLEDragDrop(Data As Object, _
                                        Effect As Long, _
                                        Button As Integer, _
                                        Shift As Integer, _
                                        x As Single, _
                                        y As Single)
    
        Dim sourceNode As MSComctlLib.Node
        Dim SourceParentNode As MSComctlLib.Node
        Dim targetNode As MSComctlLib.Node
        
        Dim tmpRootNode As MSComctlLib.Node
        Dim strtmpNodKey As String
        Dim ChildNode As MSComctlLib.Node
        
        Dim strSPKey As String
        Dim strTargetKey As String
        
        Dim strsQL As String
        Dim intKey As Integer
        Dim intPKey As Integer
        
        On Error Resume Next
        
        Select Case Screen.ActiveControl.Name
                
               Case TreeView0.Name
                    Set sourceNode = tv.SelectedItem
                
        End Select
        
        'Get Source Parent Node & Target Node Reference
        Set SourceParentNode = sourceNode.Parent
        Set targetNode = tv.HitTest(x, y)
                
        'If any errors then exit
        If Err <> 0 Then
            MsgBox Err & " : " & Err.Description, vbInformation + vbCritical, "OLEDragDrop()"
            Err.Clear
            Exit Sub
        Else
            On Error GoTo 0
        End If
        
    
        'Get/define Source parent Node Key to compare it with Target Node Key
        If SourceParentNode Is Nothing Then
            strSPKey = "Empty"
        Else
            strSPKey = SourceParentNode.Key
        End If
        
        'Check the Target Node/Location and define the Key
         Select Case True
            Case targetNode Is Nothing
                strTargetKey = "Empty"
            
            Case targetNode.Key = ""
                strTargetKey = "Empty"
                Set targetNode = Nothing
            Case Else
                strTargetKey = targetNode.Key
         End Select
        
        'Make sure the Target Node is not the source Node's own parent
        If strTargetKey = strSPKey Then Exit Sub
        
        'Track User's Node move action, check for error.
        On Error Resume Next
        
        If targetNode Is Nothing Then
            
            'If target Node is Nothing (the Node dropped in the empty area),
            'then the Node must be moved to the Root-level
            'save the original sourceNode.Key
            strtmpNodKey = sourceNode.Key
            
            'Modify the source Node Key, with addition of some text, say 'Empty', like 'X5Empty'
            'So that a temporary Node can be created with the original source Node key.
            'Note: Two Nodes with the same Key cannot remain in memory at the same time.
            'The Source Node with key 'X5Empty' deleted later,
            'temporary Node takes it's droped location.
            sourceNode.Key = sourceNode.Key & strTargetKey
    
            'Create the temporary Root Node, with original sourceNode Key
            Set tmpRootNode = tv.Nodes.Add(, , strtmpNodKey, sourceNode.Text)
            
            'define the Root Node image indexes
            'With tmpRootNode
            '    .Image = 1
            '    .SelectedImage = 4
            'End With
            
            'Move all child Nodes from SourceNode,if any,
            'as tmpRootNode's Children
            Do Until sourceNode.Children = 0
                Set sourceNode.Child.Parent = tmpRootNode
                
                'modify Node image indexes
                'With sourceNode
                '    .Image = 2
                '    .SelectedImage = 3
                'End With
            Loop
    
            'Delete the Source Node with modified Key from TreeView
            tv.Nodes.Remove sourceNode.Index
            
            'Move the tmpRootNode with original Key
            'to the dropped location on TreeView
            Set sourceNode = tmpRootNode
        Else
            'Move the sourceNode under targetNode as child
            Set sourceNode.Parent = targetNode
            
            'modify Node image indexes
            'With sourceNode
            '    .Image = 2
            '    .SelectedImage = 3
            'End With
        End If
        
        'Notify, if there was an Error then Exit, else Update PrentID of related Record.
        If Err <> 0 Then
            MsgBox Err & " : " & "Unable to move:" & vbCrLf & Err.Description, vbInformation + vbCritical, "DragDrop2()"
            Exit Sub
        Else
            'Build and execute the SQL statement to update the record
            If targetNode Is Nothing Then
                intKey = Val(Mid(sourceNode.Key, 2))
                strsQL = "UPDATE Sample SET ParentID = Null" & _
                         " WHERE ID = " & intKey
            Else
                intKey = Val(Mid(sourceNode.Key, 2))
                intPKey = Val(Mid(targetNode.Key, 2))
                
                strsQL = "UPDATE sample SET ParentID = " & intPKey & _
                         " WHERE ID = " & intKey
            End If
            
            'Modify the table records
            CurrentDb.Execute strsQL, dbFailOnError
            
            'If an error raised then refresh TreeView and exit
            If Err <> 0 Then
                MsgBox Err & " : " & Err.Description
                LoadTreeView 'Refresh/display TreeView without changes
            Else
                'Sort Nodes
                If sourceNode.Parent Is Nothing Then
                    sourceNode.Root.Sorted = True
                Else
                    sourceNode.Parent.Sorted = True
                End If
                
                tv.Nodes(sourceNode.Key).Selected = True
            End If
        End If
        On Error GoTo 0
    
    End Sub
    
    Private Sub TreeView0_OLECompleteDrag(Effect As Long)
    
        'Turn off the drophighlight
        Set tv.DropHighlight = Nothing
    
    End Sub
    
    Private Sub Form_Close()
    
    Set tv = Nothing
    End Sub

For the Drag-Drop action, there are four subroutines, they have executed automatically when you Drag the Node(s), highlights the Node when moved over other Nodes, and finally, drop it on a different Node or on the Root-level empty area.

The Main Subroutines of the Code.

  • TreeView0_OLEStartDrag()  - Initializes the selected item and sets the Node to Nothing
  • TreeView0_OLEDragOver()  - Works like the Mouse Move Event, highlights the Node, when drags a Node above it, on the way to the Target Node.
  • TreeView0_OLEDragDrop() – Performs check and controls, position the Node(s) in the Dropped Location, and updates the record on Base Table.
  • TreeView0_OLECompleteDrag()  - The DropHighlight Property is set to Nothing.

We can do the Drag and Drop works with the TreeView0_OLEDragDrop() Subroutine alone.  In that case, there will not be any Node-highlight, when the Source Node moves over other Nodes, from one location to the other, except that the Mouse Pointer changes to dragging a second arrow behind it, like in the sample image given below:

So, we will pay attention to this subroutine and check the Code in detail from the beginning.  At the beginning of the subroutine, we have declared the necessary Nodes String Variables, and others. 

Instead of repeating line-by-line analyses here, each line/section of code carries a comment line explaining what it does, so that you will understand what is going on when you go through the Code.  You may go through them.

The Sequence of Drap Drop Events

Let us understand the sequence of Events, the User Selects a Node, Drags over other Nodes on the way to its final destination, and Drops it on the Target Node.  Or Drop it on the empty area on the TreeView Control, to make it a Root-level Node. 

When you drag a Node over another Node-Text the Node-Text gets highlighted, saying that your current position is on this Node on the way, wherever you are going from here. When moved out of the Node-text the highlight disappears.  This happens all through to the Target Node.  The TreeView0_OLEDragOver() Subroutine takes care of this highlighting action.

When you Drop a Node somewhere the TreeView0_OLEDragDrop() Subroutine takes overcharge. Here, we have to analyze the User’s intentions and take appropriate action.  The following information must be saved and analyzed to move the Node to the correct location.

The Important Information to Keep Track Of.

  1. The Source Node Reference, Node Key and ParentID Values, Node Children, if any.

  2. The Target Node or location Reference, Node Key.

  3. If the Target is not a Node but the empty area of the TreeView Control, then the Source Node is to be moved into the root-level position.

  4. The Source Node when Dropped on another Node, Target Node becomes the new Parent of the Source Node.

  5. If Source Node has its own children, then they also must be moved with their Parents.

  6. ** When the Node is dragged and dropped on its own Parent-Node then Ignore this action.

    ** For example, check the above image.  If we drag the TextBox Node and drop it to its parent-node Controls, or drag the Controls Node and drop it on its Parent Node Form then those moves will be ignored.

  7. ** If Root-level Node is dragged and dropped into the empty area, then no action will be taken because it is already a Root-level Node.

For all valid moves of Node, we need to update the ParentID field value of the related record on the Sample Table.

Node Drop in the Root-level Empty Area.

In the case of item number 3 above, we have to create a Root level Node, with the same ID Number of the Source Node, which is not allowed.  Duplicate Key Value is not allowed in the TreeView hierarchy. This is the only area of the Code, where you will find little confusion in the procedure followed there.

The procedure is as given below:

  1. Modify the existing TreeView Node Key with the addition of some extra text, (say Key X5 change to X5Empty), to avoid Key conflicts, while creating a temporary Node with the original Key.

  2. Create a temporary Node with the Original Key: X5.

  3. Move all Children Nodes from the Source Node, if any, as Child Nodes to the Temporary Node.

  4. Delete the TreeView Source Node with the modified Key: X5Empty from the TreeView Control, but the related record in the Sample table is not touched.

  5. Move the temporary Node with the Original Key X5 with its children to the  Root-level position of the TreeView Control.

  6. Update the ParentID field of the related record with a zero-length string (“”)  to mark it as a Root-level Node.

Self Experiments of Drag Drop.

You may try out some Drag and Drop experiments yourself and see how it works.  Select a Node, click and hold the left mouse button, drag the Node, and drop it on another Node, or drop it in an empty area of the TreeView Control. When you drag the Node over other Node-Text it is highlighted and when you are out of the Node the highlight goes out. The dragged Node will appear in the new location where you have dropped it.  You may repeat this drag-drop experiment by selecting a single Node or Node with Children. 

Based on this movement of Nodes the related record’s ParentID field value will be updated with the Key value (ID) of the Target Node related record.

Why Two-Step Node Populating Procedure?

Now,  we will go back to the LoadTreeView() Subroutine, to take a second look at the two-step process we have adopted for populating all Nodes to the TreeView Control. 

  • All records in the Sample Table are initially added as Root-level Nodes, using the ID field value as Node-Key.

  • In the second pass of the records, if the ParentID field value is empty, then that Node will remain as Root-level Node, without change.

  • All other Nodes-related records with ParentID Value are correctly moved under its parent Node.

Naturally, the question comes up,  why do we have to do it this way?

We will do a simple experiment to make the answer clear without explaining it in too many words.  You might have already done some trial runs of drag and drop trial runs yourself and rearranged Nodes, in the process updated those record’s ParentID values with the change. So, we need to reset the record values to their original state in the Sample Table, before we start a new demo.

We have already created a copy of our Table Sample earlier, with the name Sample_bk as a backup.  Delete the Sample Table and make a copy from Sample_bk with the original name: Sample.

Open the Table and view the records and their ParentID field Values.  The sample Image of the Table is given below:

The ID Field Values are Auto Numbers and they are all in sequential order and all Id values are unique.  The following simple Rule governs the addition of a Child Node to the TreeView Control. 

The Simple Child Node Rule: The ParentID field Value (Parent-Key) in a record expects that a Parent Node already exists in the TreeView Control, with the same value as Node-Key (the ID). 

Check the third record from the top, in the above Table Image.  The ParentID field value is 2 and the ID of the current record is 3.  In this case, the record with ID 2 will be added to the TreeView Control before we attempt to add the third record to the Node.  Both records aren't necessarily next to each other. Check the record with ID number 21, its ParentID field value is 12, less than the current record  ID value of 21. 

In either case, when the program encounters the ParentID Value in a record it assumes that the record with the ID value equal to the ParentID was already added as a Node in the TreeView Control in the earlier cycle of populating the Nodes.

Justifying the two Step Procedure.

Let us try some Drag-Drop trial runs.  But, before that, we have a Form with the name frmSample, which we used in the first Tutorial Session, and in that, we loaded all the TreeView Nodes in one go.  Yes, we have followed the same method so far and we need some change from now on.  But, before that let us open the old form and see how the Nodes appear on the Form.

  1. Open the Form frmSample to see what the TreeView display looks like, with the Sample table records, loaded using the old rule.

  2. If you are through viewing the TreeView Nodes then close the Form.

  3. Now, Open the frmDragDrop Form.  We are preparing to drag and drop a Node.

  4. Select the Node with the Node-Text-Table, Click and hold the left mouse button, and drag and drop it on the Node, with the Node-Text Form.

  5. The Table Node with its immediate Child-Node Fields and its Children Nodes are moved as Child Nodes under the Form Node.

  6. Close the Form frmDragDrop and open it again. The Nodes will appear correctly, where you have dropped them, like in the image given below.

  7. Now, close the Form frmDragDrop.

  8. Open the Form frmSample to see how this change appears on this Form. You will be greeted with an Error message, Element Not Found with Error Number: 35601.

  9. Select the Debug Command Button to go to the highlighted Code line, where the Error occurred.

  10. Point the mouse on the nodKey Param of Add() method, it shows X3, point the mouse on the ParentKey parameter and it shows X7. 

    Looking at these two parameter values, we can assume that we are on the record with the ID value 3 and trying to designate this Node as a child Node, to another Node not yet populated into the TreeView Control, with ID value 7.

  11. Press F5 Key to bring up the same dialog box again and Click the End Command Button to stop the Program and bring up the Form on the Database Window. Close the frmSample Form.

  12. Open the Sample Table to view the arrangement of the  ParentID Numbers, after our drag and drop action.  The Records will look like the image given below and I have highlighted the record that triggered the Error with ParentID value and showing its Parent record position.

Following the earlier Node populating normal procedure, we are at the third record position.  Since, the records ParentID value 7,  the Nod with ID value 7 must be present in the TreeView Control.  The Node with ID Value 7 is not yet populated into the TreeView Control, but we are trying to reference the non-existent Node and this triggers an Error.

Even if you Sort the records in the ParentID field order the records' new arrangement will look like the image given below:

Now, another record’s Parent Node is not in the expected position. 

Hence, in these circumstances, our two-step TreeView Nodes loading approach works for both normal and after-drag-drop actions as well.

In the first step populate all records as Root-level Nodes in the TreeView Control using the ID field value as Node-Key.

Now, all the Nodes of all records are available in the TreeView Control. It will be easy to move them around wherever we want.  It doesn’t say any of the required Node doesn’t exist in the TreeView.

In the second pass on the same set of records, the records with empty ParentID field values are untouched and allowed to remain as Root-level Nodes.   In other cases moves the Node, as Child-Node under its Parent Node, by updating the [Relative] Parameter of the Node with the following Statement:

Set tv.Nodes.Item(strKey).Parent = tv.Nodes.Item(strPKey)

This is what we do through the second pass on the same set of records.  You may do it by resetting the Record Pointer to the first record, by executing rst.MoveFirst before the Do . . . Loop, EOF conditions, and rst.MoveNext to access each record as we normally do.

Second Step in Reverse Order.

Or you may do it in reverse order.  After populating all records as Root-level Nodes the Record Pointer will be beyond the last record and on the EOF position.  You may reset the record pointer to the last record, by executing rst.MoveLast before the Do . . . Loop BOF check, and execute rst.MovePrevious to access each record and move the Nodes correctly under its parent Node.  But, the Nodes may load slightly differently in the placement order Nodes.

You may try this out yourself with the above-suggested change of Code and see the result.

Download Demo Database.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation

Share:

TreeView ImageCombo Drop-Down Access Menu

Introduction.

In this Session of the TreeView Control Tutorial, we will learn to program the ImageComboBox Control.  We will create an MS-Access Project Drop-down Menu, with ImageCombo Control.  There is a second ImagecomboBox Control for displaying the Images and their Key Values from the ImageList Control.  For both of these ImageComboBox Controls, the list of Images has been taken from a common ImageList Control.  The Images were manually uploaded into the ImageList Control from the Computer, in an earlier session of this Tutorial Series.

Following are the Tree View Control Tutorial Sessions we have covered so far:

The TreeView Control Tutorial Session Links.

  1. Microsoft TreeView Control Tutorial
  2. Creating Access Menu with TreeView Control
  3. Assigning Images to TreeView Control
  4. Assigning Images to TreeView Control-2
  5. TreeView Control Check-Mark Add, Delete Nodes

The Demo View of Both ImageComboBoxes Expanded.

The completed MS-Access Project Drop-Down Menu Image is given below:

The Design View Image of the above Form is given below:

The Drop-Down ImageComboBox Source Data.

The new MS-Access Project Drop-Down Menu's Source Data has been taken from our earlier Access Menu Project.

Download the Demo database now, if you have not done it earlier, from the 4th item Link given above.  If you do, then you have all the required data Objects to continue with the current Session.

There are three Tables: Categories, Products, and the Menu table.  There are two forms to display the Categories and Product data and a data filter parameter Form for reports.

We have two more Forms: frmMenu and frmMenu2 which we have used in our earlier Tutorial Sessions.

You will find two Reports to Preview the Categories and Products Data Items.

Two Macros for displaying some simple Messages.  The macros can be used for sequencing Action-Queries for Data Processing for complicated Reports.  These actions have been performed by selecting Options from the TreeView Control Project Menu in the earlier Tutorial Session.

We need all these objects here also because we will create a new Drop-Down Menu using the ImageComboBox Control. We must be able to open all these objects by selecting the options in the New Drop-Down Control Menu, exactly the same way as we did on frmMenu2 Form, using the TreeView Control, in this same Database

The Menu table image is given below for your reference.

Preparing for the Design of the Drop-Down Menu Form.

Check the Design View of the Menu Form above and there are two Menu-related Controls.   One ImageList Control and one ImageComboBox Control.  One more ImageComboBox Control has been placed on the right side of the Form to display the ImageList’s Images.

  1. Make a Copy of the ImageList Control from frmMenu2 Form and Paste it on a New Form, say frmMenu3Combo

    The Imagelist control has Images uploaded manually from the Computer in our earlier Tutorial Session.  You may open its Property Sheet and check the Images and their Key Names. Right-Click on the ImageList Control, highlight ImageListCtl Object and select Properties Option.

  2. The ImageComboBox Control placed on the left side of the Form is for Drop-Down Menu, with the name: imgCombo1.  Find Microsoft ImageComboBox Control from the ActiveX Controls group and place it on the Form.  Change the Name of the control to imgCombo1.

  3. Create another ImageComboBox  Control to the right side, with the name imgCombo2. The second ImageComboBox we will use for displaying the Images and their Key Names, from the ImageList0 Control, as a drop-down list.

  4. Add a Label control above the second ImageComboBox control and change its Caption to Image List.

The Images Listing in Image-Combo-Box Control.

First, we will work with the second imgCombo2 Control and display the List of Images from the ImageList Control.  Once you are familiar with the Code you will understand the Drop-down Menu Creation procedure very easily.

We have divided the frmMenu3Combo Form Module VBA Code into two parts.  Let us take the first part and see what we have in there.

Briefly, in the global declaration area, the main object variables have been declared. The Form_Load() event procedure initializes the ImageList control on the Form to its object variable objimgList and calls the cboImageList() subroutine to add images from the ImageList control to the second ImageComboBox control.  Let us take a closer look at the code.

The first-part vba code, with the Form_Load() and cboImageList() subroutines listed below:

Dim imgcombo1 As MSComctlLib.ImageCombo
Dim imgCombo2 As MSComctlLib.ImageCombo
Dim objimgList As MSComctlLib.ImageList
Const KeyPrfx As String = "X"

Private Sub Form_Load()

Set objimgList = Me.ImageList0.Object

cboImageList 'load imagelist-combo

'CreateMenu 'Create Drop-Down Menu
 
End Sub

Private Sub cboImageList()
Dim j As Integer
Dim strText As String

Set imgCombo2 = Me.ImageCombo2.Object
imgCombo2.ImageList = objimgList

For j = 1 To objimgList.ListImages.Count
    strText = objimgList.ListImages(j).Key
    imgCombo2.ComboItems.Add , , strText,j,,j
Next
    imgCombo2.ComboItems(1).Selected = True
End Sub

VBA Code Review.

On the global declaration area, we have declared two ImageComboBox controls, imgCombo1 for the project menu and imgCombo2 for displaying images from the ImageList control.  The objimgList variable is declared for the ImageList control on the form.  The Keyprfx variable with the character X is declared as a constant.

Within the Form_Load() event procedure the objimgList is initialized with the ImageList control on the Form, with the statement: Set objimgList = Me.ImageList0.Object.  Now, all the pre-loaded images in the ImageList control are available to access through the objimgList object.

The next statement calls the sub-routine cboImageList() to add all the Images to the ImgCombo1 control.

The CreateMenu() subroutine call has been commented out for the time being.

In the cboImageList()  subroutine two variables have been declared. 

Next,  the statement Set imgCombo2 = Me.ImageCombo2.Object  assigns the second ImagecomboBox on the form to the object variable imgCombo2. 

Like the TreeView control the imgCombo2 has an ImageList property, to pass the ImageList control’s reference to the ImageComboBox control, in order to access the ImageList’s properties. The next statement: imgCombo2.ImageList = objimgList does that.

Next, the For . . . Next Loop runs for the number of Images in the ImageList Control. 

The first ImageList item’s Key-Value ('form_close') has been saved in the strText variable.  Here, we have taken the Key value of Image List control as Text or as a description of the ImageCombo image, because that is the only Text available.  The Tag property is empty and we have other uses with this property when we work with the drop-down menu.

The next statement is the important one that we have to look at closely, the Add method of ImageComboBox control.  The syntax of the statement is as given below:

imgCombo2.ComboItems.Add [Index],[Key],[Text],[Image],[SelImage],[Indentation]

All the parameters of the Add() method are optional.  For our first test run of this control, we will use the values for [Text], [Image], and [Indentation] onlyAfter viewing the result of the first test run of the image list view, we will not use the [Indentation] parameter value on this ImageCombo control.

Note: But, keep in mind that we will need the Indentation Property value for the Drop-Down Menu to make the menu items look like Root-Node and Child-Node on the TreeView Control.  We will use the [Key] Parameter also  (for both Key and Text parameters) to access a specific menu item’s Tag Property value.  

Image List with incrementing Indentation Param Setting.

The first test runs the image list in ImageCombo2 will look like the image given below, after applying incremental values for indentation.:

The effect of the Indentation is clear from the above trial run image.  Each item has been moved to the right, one space greater than the previous one.  We can make use of this feature to position our project menu item Nodes, to look like Root-Level and Child Nodes.

After the strText value (‘form_close’) the first variable j refers to the ImageList’s index number, the [SelImage] parameter we have skipped and the next value in j  has been used for indentation of each list item when placed in the ComboBox list.  After the first test run and after viewing the result you may remove all parameters after the image index value.

The next statement: imgCombo2.ComboItems(1).Selected = True selects the first item in the ComboBox.  When you select an item from the ImageCombo control through code the Change() event fires, but it is not when you select an item directly on the form. The Update() event ignores the manual updating Event and attempts to invoke through code.

Save the form frmMenu3Combo and open it in Normal View.  Expand the second ImageList ComboBox control and view the result.  Remove the commas and the variable j at the end, after the first variable j, retained for the ImageList index number.

The VBA Code of Project Drop-Down Menu.

Now, we will proceed with the second part of the form module vba code to learn how to create the Access Drop-Down Menu and see how to open Access Forms, Reports and Macros by selecting the ComboBox control item.

The second part of the VBA code, which consists of the CreateMenu() subroutine and ImageCombo1_Click() event procedure, has been listed below:

Private Sub CreateMenu()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strKey As String
Dim strText As String
Dim typ As Integer

Set imgcombo1 = Me.ImageCombo1.Object
imgcombo1.ImageList = objimgList

strSQL = "SELECT ID, Desc, PID, Type,Macro,Form,Report FROM Menu;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rst.EOF And Not rst.BOF
    If Len(Trim(Nz(rst!PID, ""))) = 0 Then
        strKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        imgcombo1.ComboItems.Add , strKey, strText, 1, 2, 1 ' image index 1,2([image],[selectedimage])
        'imgcombo1.ComboItems.Add , strKey, strText, "folder_close", "folder_open", 1
    Else
        strKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        imgcombo1.ComboItems.Add , strKey, strText, 4, 5, 4 'last param is spacing
        'imgcombo1.ComboItems.Add , strKey, strText, "left_arrow", "right_arrow", 4
     
        'Check for the presense of Type Code
        If Nz(rst!Type, 0) > 0 Then
                typ = rst!Type
                With imgcombo1.ComboItems
            Select Case typ
                'save type Code & Form/Report/Macro Name in Tag Property
                Case 1
                    .Item(strKey).Tag = typ & rst!Form
                Case 2
                    .Item(strKey).Tag = typ & rst!Report
                Case 3
                    .Item(strKey).Tag = typ & rst!Macro
            End Select
                End With
        End If
        
    End If
    rst.MoveNext
Loop
rst.Close
imgcombo1.ComboItems.Item(1).Selected = True
End Sub

Private Sub ImageCombo1_Click()
Dim strObject As String
Dim strTag As String
Dim typ As Integer

strTag = ImageCombo1.SelectedItem.Tag
typ = Val(strTag)
strObject = Mid(strTag, 2)

Select Case typ
    Case 1
        DoCmd.OpenForm strObject, acNormal
    Case 2
        DoCmd.OpenReport strObject, acViewPreview
    Case 3
        DoCmd.RunMacro strObject
End Select

End Sub

I think, before starting with the vba code you should take a look at the Table Image  (third image from the top of this page) if you have not done it in the earlier session on Access Project Menu creation. 

The ID field has a unique ID value and is an AutoNumber field. 

The Second field Desc has object type group names (Forms, Reports, and Macros) and actual object names of the form, Report, and Macro.

The PID (Parent ID) field value is empty for object group names: Forms, Reports, and Macros.  These empty PID value items will position at the left position in the ImageComboBox Drop-Down Menu, with one character space indentation, and other items will be moved forward with four character spaces.  With these positionings of items, they will look like the Root-Level and Child-Level Nodes in a TreeView control, but we will miss the connecting Tree Lines.

Check the image in the ImageComboBox selected item control, the image is positioned at the leftmost position.  The Group Item in the next line is positioned after one character space on the left side and other Group items are also appearing in the same position.  The Child member items under each group have been positioned after four character spaces.

The PID field value has been checked and if found empty, then we assume that it is a group name, otherwise, it is an Access Object Name that we need to open, when the user clicks on it, and position them as child members of the group. The actual Key value in the PID field is not important here.  In either case, we need it here.  But, we can use the type field value for this purpose instead.

Next, the Type field contains the object type Code: 1 – Form Name, 2 – Report Name, and 3 – Macro Name.  The next three fields: Form, Report, and Macro have the actual Object Names based on their respective codes in the Type field.  I have used three different fields for clarity, they all can be placed in one column as well.

The Type Code and Object Name pair (say 2rptCategories) will be saved in the ImageComboBox’s Tag Property.

The CreateMenu() Subroutine.

Now, on to the vba code of the CreateMenu() Subroutine.

The Database and other variables have been declared at the beginning.

The imgcombo1 object variable has been initialized with The Me.ImageCombo1. The object on the form.

Next, the imgCombo1.ImageList property has been loaded with the ImageList object objimgList Reference, so that we will be able to access the ImageList’s Index Number and Key values directly.

Next, the Menu Table record set is open with the SQL String. 

The PID field value has been checked, if it is empty then it is an object group name, the ID value is prefixed with Constant X and saved in the strKey variable.  The field Desc value has been saved in strText Variable.

The next statement calls the Add() method of ImageComboBox control and the first item has been added to the Drop-Down Menu.

imgcombo1.ComboItems.Add , strKey, strText, 1, 2, 1

The first parameter value Index number has been omitted, but it will be created automatically.  The strKey variable consists of ID Field value 1 with prefix constant X (X1) as the Key parameter.  The strText contains the Desc field value.  The next value 1 is the ImageList’s first image’s (Key-value folder_close) Index value.  If you prefer the 'folder_close' Key-value within quotes you may do so. Next value 2 is the second Image’s (‘folder_open’) Index value or you may use 'folder_open' in quotes and the last parameter 1 is for indentation.

Note: To confirm the Index order of Images in the ImageList Control check the Image Listing Order in the ImageCombo2 Display we created earlier.  We could have prefixed the Index value to the Key value with one space to add that number also to the Key value to look like [image]  1 form_close. [image] 2 form_open and so on . . . This I will leave to you as an exercise for yourself.

Next, if the PID field value is non-zero, then the actual Menu Option is to be added under the Else ClauseHere, we have added another ImageCombo item as we did earlier.  For [Image] and [SelImage] parameters we have taken the ImageList Item Index values 4 and 5. The indentation parameter value is 4 character spaces.

In the ImageCombo Item’s Add() Method, under the Else Clause, we need to save the Access Object Name (frmData Entry) along with the Type Code 1 in the Tag (ImageCombo1.ComboItems.Item(strKey).Tag) Property.  When the user selects this item from the Drop-Down Combo Box Menu, the Click() event fires, the Tag Property value has been extracted, the Type code has been checked, if the type code is 1 (Form) then the form name in the Tag Property (frmData Entry) is open with DoCmd.OpenForm Command.

This way all the Menu Table records have been added to the Image ComboBox control.

The statement imgcombo1.ComboItems.Item(1).Selected = True selects the first item as the default item in the Image ComboBox Control.  On this code execution time the Change() event fires, but not when an item is selected by clicking on it on the Form View.

NB: Before opening the form for the Drop-down Menu Trial Run, please remove the Comment Symbol from the ‘CreateMenu call line in Form_Load() Event Procedure.  We have commented out this line temporarily, during trial runs of Image display from ImageList Control in ImageCombo2 Control.

The ImageCombo1_Click() Event fires when the user selects an item from the Image ComboBox Control.  The selected item’s Tag Property value has been checked for the Type Code & Object Name and opens it with Docmd.Objecttype ObjectName.

The Demo Database ProjectMenuV221.accdb in .zip format is given below for Downloading.

DICTIONARY OBJECT

  1. Dictionary Objects Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code