Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

ListView Control Drag Drop Events Handling

Introduction.

We are familiar with the drag-and-drop operations on TreeView Control that rearrange Nodes in Ms-Access.  All the base records for the Treeview Control Nodes come from a single Access Table.  We always update the Source Node’s ParentID field value, with the Target Node’s ID Value on the same Table record, to change the position in the TreeView Control.  The records are not moved physically anywhere.

Here, with the addition of ListView Control along with TreeView Control, we plan to work with two different Access Tables.

  1. lvCategory – Category Code and Description.
  2. lvProducts – Categorywise Products.

This way it is easier to understand the relationship between both Tables and what changes to make and where when one Product Item (ListView item) moves from one Category to the other on the TreeView Control. 

The lvCategory Access Table has 20 records for the TreeView Nodes and the lvProducts Table has 45  for the ListView Control.  One or more records in the Products table are directly related to a product category on the Category Table.  The relationship between them has been updated with the Category ID (CID) Field value on the Product Table’s ParentID field so that the change of category of the product reflects immediately on the ListView Control. 

The demo data table was taken from the Microsoft Access Sample Database Northwind and split into two parts.

Based on the ParentID Field Value, of lvProduct records, we could filter and list all the related product items in the ListView Control, when a Category Node gets selected in the TreeView Control.

The Topics which we have covered so far.

The main topics on the TreeView, ImageList, ImageCombo, and ListView Controls, we have covered so far, in MS Access, are given below.

  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. Tree View Control Check-Mark Add, Delete Nodes
  6. Tree View ImageCombo Drop-Down Access Menu
  7. Re-arrange TreeView Nodes by Drag and Drop
  8. List View Control with MS-Access TreeView

The ListView Drag-Drop Task.

As far as ListView’s Drag and Drop operation is concerned, it is a simple exercise comparing the same method within the TreeView Control alone.  Since the Drag Drop action involves both TreeView and ListView Controls, we use the same TreeView0_OLEDragDrop() Event Procedure with some simple VBA Code.

The Product items listed in the ListView Control belong to the current Category item selected in the TreeView Control.

The User selects a particular product item from the ListView Control, if he/she thinks it belongs to a different Category Item,  then drag and drop it on the target Category item on the TreeViewCcontrol. 

The moved ListView Product Item will be added to the list of items that belong to the changed Category. The product record’s ParentID field value gets updated with the target Category record ID (CID value). 

It is only a one-way action, always move the ListView item from one category and drop it on a different Category Node on the TreeView Control. 

The ListView drag-drop demo Access Form frmListViewDrag’s trial run Screen image is given below:

In the above Image, the Beverages Category on the TreeView has been selected.  The products belonging to the Beverages category have been listed in the ListView Control.

The ListView Control In Design View.

The List of Control names on the Form is given below:

  1. TreeView Control: TreeView0
  2. ListView Control: ListView0
  3. ImageList Control: ImageList3
  4. Command Button: cmdClose

The VBA Code on the frmListViewDrag’s Class Module:

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim lvList As MSComctlLib.ListView
Dim imgList As MSComctlLib.ImageList
Const Prfx As String = "X"

Private Sub Form_Load()
Dim db As DAO.Database
Dim tbldef As TableDef

    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
    
    Set imgList = Me.ImageList3.Object
    
With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With
    
    Set lvList = Me.ListView0.Object
    lvList.ColumnHeaders.Clear
    lvList.ListItems.Clear
    lvList.Icons = imgList
    
    Set db = CurrentDb
    Set tbldef = db.TableDefs("lvProducts")
    
    'Initialize ListView & Column Headers Property Values
     With lvList
        .ColumnHeaderIcons = imgList
        .Font.Size = 9
        .Font.Name = "Verdana"
        .Font.Bold = False
        
        'ColumnHeaders.Add() Syntax:
        'lvList.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
        'Alignment: 0 - Left, 1 - Right, 2 - Center
        .ColumnHeaders.Add 1, , tbldef.Fields(1).Name, 2600, 0, 5
        .ColumnHeaders.Add 2, , tbldef.Fields(3).Name, 2600, 0, 5
        .ColumnHeaders.Add 3, , tbldef.Fields(4).Name, 1440, 1, 5
    End With
    
    Set db = Nothing
    Set tbldef = Nothing

    
   LoadTreeView 'Create TreeView Nodes

End Sub

Private Sub LoadTreeView()
    Dim Nod As MSComctlLib.Node
    Dim firstCatID As Long
    Dim strCategory As String
    Dim strCatKey As String
    Dim strBelongsTo As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    'Initialize treeview nodes
     tv.Nodes.Clear
     
    'Initialize Listview nodes
    While lvList.ListItems.Count > 0
          lvList.ListItems.Remove (1)
    Wend
    
    strSQL = "SELECT lvCategory.CID, lvCategory.Category, "
    strSQL = strSQL & "lvcategory.BelongsTo FROM lvCategory ORDER BY lvCategory.CID;"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If Not rst.BOF And Not rst.EOF Then
        rst.MoveFirst
        firstCatID = rst!CID
    Else
        Exit Sub
    End If
    ' Populate all Records as Rootlevel Nodes
    Do While Not rst.BOF And Not rst.EOF
            strCatKey = Prfx & CStr(rst!CID)
            strCategory = rst!Category
            
            Set Nod = tv.Nodes.Add(, , strCatKey, strCategory, 1, 2)
            Nod.Tag = rst!CID
        rst.MoveNext
    Loop
    
    'In the second pass of the the same set of records
    'Move Child Nodes under their Parent Nodes
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
        strBelongsTo = Nz(rst!BelongsTo, "")
        If Len(strBelongsTo) > 0 Then
            strCatKey = Prfx & CStr(rst!CID)
            strBelongsTo = Prfx & strBelongsTo
            strCategory = rst!Category
            
            Set tv.Nodes.Item(strCatKey).Parent = tv.Nodes.Item(strBelongsTo)
        End If
        rst.MoveNext
    Loop
    rst.Close
    
    ' Populate ListView Control with Product details
    ' of the first Category Item
    LoadListView firstCatID
    
End Sub


Private Sub LoadListView(ByVal CatID)
    Dim strProduct As String
    Dim strPKey As String
    Dim intcount As Integer
    Dim tmpLItem As MSComctlLib.ListItem
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    ' Initialize ListView Control
    While lvList.ListItems.Count > 0
        lvList.ListItems.Remove (1)
    Wend
   
     strSQL = "SELECT lvProducts.* FROM lvProducts "
     strSQL = strSQL & "WHERE (lvProducts.ParentID = " & CatID & ") "
     strSQL = strSQL & "ORDER BY lvProducts.[Product Name];"
    
    'Open filtered Products List for selected category
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Do While Not rst.BOF And Not rst.EOF
        intcount = intcount + 1
        strProduct = rst![Product Name]
        strPKey = Prfx & CStr(rst!PID)
        
        'List Item Add() Syntax:
        'lvList.ListItems.Add Index,Key,Text,Icon,SmallIcon
        Set tmpLItem = lvList.ListItems.Add(, strPKey, strProduct, , 3) 'first column
            lvList.ForeColor = vbBlue
            
            'List second column sub-item Syntax:
            'tmpLItem.ListSubItems.Add Column - Index, Key, Text, ReportIcon, ToolTipText
            tmpLItem.ListSubItems.Add 1, strPKey & CStr(intcount), Nz(rst![Quantity Per Unit], ""), 6
            
            'List third column sub-item
            tmpLItem.ListSubItems.Add 2, strPKey & CStr(intcount + 1), Format(rst![list Price], "0.00"), 6, "In Local Currency."
        rst.MoveNext
    Loop
    
    Set db = Nothing
    Set rst = Nothing
    
    If intcount > 0 Then lvList.ListItems(1).Selected = True
    
End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim Cat_ID As String
Cat_ID = Node.Tag

LoadListView Cat_ID

End Sub

Private Sub TreeView0_OLEStartDrag(Data As Object, AllowedEffects As Long)
    Set tv.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)
On Error GoTo TreeView0_OLEDragOver_Err

    Dim nodSelected As MSComctlLib.Node
    Dim nodOver As MSComctlLib.Node
    
    If tv.SelectedItem Is Nothing Then
        'Select a node if one is not selected
        Set nodSelected = tv.HitTest(X, Y)
        If Not nodSelected Is Nothing Then
            nodSelected.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
    
TreeView0_OLEDragOver_Exit:
Exit Sub

TreeView0_OLEDragOver_Err:
MsgBox Err & " : " & Err.Description, vbInformation, "TreeView0_OLEDragOver()"
Resume TreeView0_OLEDragOver_Exit
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 tv_nodSource As Node
    Dim tv_nodTarget As Node
    
    Dim strtv_ParentKey As String
    Dim strtv_TargetKey As String
    Dim strListItemKey As String
    Dim strSQL As String
    
    Dim vCatID As Long
    Dim lngPID As Long
    
    On Error GoTo TreeView0_OLEDragDrop_Err
    
    'Get the source/destination Nodes
    Set tv_nodSource = tv.SelectedItem
    Set tv_nodTarget = tv.HitTest(X, Y)
    
        If Not tv_nodTarget Is Nothing Then
            strtv_ParentKey = tv_nodSource.Key
            strtv_TargetKey = tv_nodTarget.Key
                
            If strtv_ParentKey = strtv_TargetKey Then Exit Sub

            'Extract ListItem Key
            strListItemKey = lvList.SelectedItem.Key
                
            'extract Category Record CID Value
            'and ListItem Product ID Key
            vCatID = Val(Mid(tv_nodTarget.Key, 2))
            lngPID = Val(Mid(strListItemKey, 2))
    
            'UPDATE lvProducts Table
            strSQL = "UPDATE lvProducts SET ParentID = " & vCatID & _
            " WHERE PID = " & lngPID
             
            CurrentDb.Execute strSQL, dbFailOnError
                
            Set tv.DropHighlight = Nothing
            tv_nodSource.Selected = True
                
            'Rebuild ListView Nodes
            TreeView0_NodeClick tv_nodSource
                
        Else ' Invalid Target location
            MsgBox "The destination is invalid!", vbInformation
        End If
    
TreeView0_OLEDragDrop_Exit:
Exit Sub

TreeView0_OLEDragDrop_Err:
MsgBox Err & " : " & Err.Description, vbInformation, "TreeView0_OLEDragDrop()"
Resume TreeView0_OLEDragDrop_Exit
End Sub

Private Sub TreeView0_OLECompleteDrag(Effect As Long)
    Set tv.DropHighlight = Nothing
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

The familiar VBA Code Segments.

In the Form_Load() Event Procedure, we initialize the TreeVew, ListView, and ImageList Controls.  It creates the ColumnHeadings of the ListView Control, before populating the List items in the Listview control.  At the end of this process, the LoadTreeView() subroutine is executed.

The LoadTreeView() subroutine populates the products’ Category Nodes on the TreeView Control, with the records from the lvCategory Table.  Loading Nodes on the TreeView Control is a two-step process.  Why it is so, rather than doing it in one go?  This aspect has been explained in detail on an earlier Page, the 7th link on the list of links given above if you would like to go through it.  Repeating all of them here may not be appropriate.

At the end of the above subroutine, the LoadListView() subroutine has been called with the first Category record’s CID Value 1 as the parameter.

The Product Records with ParentID field value 1  have been filtered and listed on the ListView Control. This procedure was explained in detail in last week’s post, the 8th item, among the List of Links given above.

The Drag-Drop Action Subroutines.

The following Subroutines associated with the Drag and Drop action will be executed automatically in the order they are presented below:

  1. TreeView0_OLEStartDrag()
  2. TreeView0_OLEDragOver()
  3. TreeView0_OLEDragDrop()
  4. TreeView0_OLECompleteDrag()

The first and last Subroutines initialize the Nodes involved and reset their status at the end respectively. 

The second one, the OLEDragOver() subroutine works like the MouseMove Event Procedure and tracks the movement of the mouse during the drag-drop operation.  It highlights the NodeText when the mouse is over a Node and tracks its trajectory till the left mouse button gets released.

The TreeView0_OLEDragDrop() procedure code alone is listed below.

Private Sub TreeView0_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim tv_nodSource As Node
    Dim tv_nodTarget As Node
    
    Dim strtv_ParentKey As String
    Dim strtv_TargetKey As String
    Dim strListItemKey As String
    Dim strSQL As String
    
    Dim vCatID As Long
    Dim lngPID As Long
    
    On Error GoTo TreeView0_OLEDragDrop_Err
    
    'Get the source/destination Nodes
    Set tv_nodSource = tv.SelectedItem
    Set tv_nodTarget = tv.HitTest(X, Y)
    
        If Not tv_nodTarget Is Nothing Then
            strtv_ParentKey = tv_nodSource.Key
            strtv_TargetKey = tv_nodTarget.Key
                
            If strtv_ParentKey = strtv_TargetKey Then Exit Sub

            'Extract ListItem Key
            strListItemKey = lvList.SelectedItem.Key
                
            'extract Category Record CID Value
            'and ListItem Product ID Key
            vCatID = Val(Mid(tv_nodTarget.Key, 2))
            lngPID = Val(Mid(strListItemKey, 2))
    
            'UPDATE lvProducts Table
            strSQL = "UPDATE lvProducts SET ParentID = " & vCatID & _
            " WHERE PID = " & lngPID
             
            CurrentDb.Execute strSQL, dbFailOnError
                
            Set tv.DropHighlight = Nothing
            tv_nodSource.Selected = True
                
            'Rebuild ListView Nodes
            TreeView0_NodeClick tv_nodSource
                
        Else ' Invalid Target location
            MsgBox "The destination is invalid!", vbInformation
        End If
    
TreeView0_OLEDragDrop_Exit:
Exit Sub

TreeView0_OLEDragDrop_Err:
MsgBox Err & " : " & Err.Description, vbInformation, "TreeView0_OLEDragDrop()"
Resume TreeView0_OLEDragDrop_Exit
End Sub

The Drag Drop Action Step by Step.

The TreeView0_OLEDragDrop() Procedure executes immediately after the left mouse button has been released to complete the Drop Action.  At the beginning of the code, the active and the Target TreeView Node’s references have been saved in tv_nodSource and tv_nodTarget object Variables respectively. 

Next, we perform a check on, whether the ListItem has been dropped on a valid TreeView Node or not. If it is dropped on the same source Category Node or dropped on an empty area on the TreeView Control then these moves are not valid.  If it has been dropped in an empty area of the TreeView Control then the tv_nodTarget object variable will contain the value Nothing.  In that case, it displays a message and exits from the Program.

Next, the TreeView Source and Target Node Key Values are saved in two String Variables.   If both keys are the same then the ListItem is dragged and dropped on its own Parent Node (Category Node) on the TreeView Control, then the program is aborted.

If both Keys are different then it is time to update the change on the Product Record’s ParentID field, with the Target Category Record’s CID Code, and refresh the ListView Items.

The selected ListItem’s Key value (PID field value) has been saved in the strListItemKey  String Variable.

The Category record’s actual CID field value has been extracted from the Target Node, by stripping the prefix character value 'X' and saved in the variable vCatID.  This is the value that we will be updating on the Product Record’s ParentID field. When updated the Product ListItem becomes the subitem of the new Category.

Similarly, the selected List Item’s Product’s Key PID value is extracted and saved in Variable lngPID.  This has been used as criteria to filter and pick that particular Product record for updating the ParentID field with vCatID.

An UPDATE Query SQL has been created to filter the record, using the lngPID Code as Criteria, to filter the Product record, and to update the vCatID Value in the ParentID field.

The Execute method of the Currentdb has been called with the SQL and updates the change.

The Highlight of the Node has been reset to the Source Node.

Next, the TreeView0_NodeClick() subroutine has been called with the tv_nodSource as a parameter to reflect the change on the ListView Control.

The Close Button Click will close the Form.

Download Demo Database.

You may download the Demo database, do trial runs, and study the VBA Code.


WISH YOU A VERY HAPPY NEW YEAR.

MS-ACCESS EVENT HANDLING

  1. Withevents MS- Access Class Module
  2. Withevents and Defining Your Own Events
  3. Withevents Combo List Textbox Tab
  4. Access Form Control Arrays And Event
  5. Access Form Control Arrays And Event-2
  6. Access Form Control Arrays And Event-3
  7. Withevents in Class Module for Sub-Form
  8. Withevents in Class Module and Data
  9. Withevents and Access Report Event Sink
  10. Withevents and Report Line Hiding
  11. Withevents and Report-line Highlighting
  12. Withevents Texbox and Command Button
  13. Withevents Textbox Command Button
  14. Withevents and All Form Control Types


Share:

ListView Control with Ms-Access TreeView

Introduction.

Microsoft Access ListView Control Project has been designed and demonstrated here, in this episode of the TreeView Control Tutorial.  The main task is, first populate the TreeView Control with Product Category Items.  Then on the selection of a Category item on the TreeView Control, the related Product Records have been filtered from the Products Table and listed in the ListView Control.  Each record’s field values are displayed in different Columns.

We are familiar with TreeView, ImageList, and ImageCombo Controls and worked with them in earlier Episodes. 

The ListView Control will display the data from three different Fields in three different Columns.   The Columns will have Column Headers and other Column Properties, like the ReportIcons, and Tooltip Text.

The Product Record has several fields and all of them cannot be displayed in the ListView Control, with several columns in a small area.  Instead, when an item has been selected in the ListView Control another Form will open up with full details of the selected Product record.  Briefly, this is what we plan to do here, by using the TreeView, ListView, and ImageList Controls.

The ListView Control properties have similar characteristics to Microsoft Access ListBox Control.  It has columns to display related values next to each other, under each column header.  The Column Width property value, for each column, ensures that the field value has enough space to display the contents properly.  The ListView item responds to clicks so that we could perform other actions, like opening Form, Report, Macro, or call other functions, if required.

Links to the earlier Tutorial 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, creating ImageList Control shareable with other Projects.
  5. TreeView Control Check-Mark Add, Delete Nodes.
  6. TreeView ImageCombo Drop-Down Access Menu.
  7. Re-arrange TreeView Nodes by Drag and Drop.

Articles on Access ListBox Control.

In Microsoft Access, we could use ListBox Control to display information and use them in various ways.  For building custom-made Form Wizard and custom-made Report Wizard.  For creating Hyperlinks in ListBox, creating List Items from source data of another Listbox, bringing in external files list into the List Box, or assigning Filter() Function output into the ListBox.  These were some of the methods we tried earlier with Access ListBox.   The links are given below for reference.

  1. Custom-made Form-Wizard.

  2. Custom-made Report- Wizard.

  3. Open Forms with Hyperlinks in Listbox.

  4. Create List from Another ListBox.

  5. Dynamic ListBox ComboBox Contents.

  6. Filter Function Output in ListBox.

  7. Filter Function Output in ListBox-2

  8. List-Box and Date Part-one.

  9. List-Box and Date Part-two.

The ListView with TreeView Control Demo Image.

The Demo Image View of Form, with TreeView and ListView Controls:

The ListView Demo Form in Design View.

Designing the Form frmListView.

  1. Create a new form, with the name frmListView, and Insert the following three Controls in the Detail Section of the Form with the Names specified for each control as given below.

  2. Insert a TreeView Control on the Form, from the ActiveX Controls List, on the left side, and change the Name to TreeView0. Display its normal Access Property Sheet and change the Width Property Value to 6.614 cm and Height to 9.709 cm.

  3. Insert a ListView Control from the ActiveX Controls, at the right side of the TreeView Control, and change its name to ListView0.  The ListView control is about 12.462 cm wide and 9.709 cm in Height, enough space for displaying three columns of Data from the Products Table. Place both controls close to each other and the top edges Aligned.

  4. Insert an ImageList Control from the ActiveX Controls List, place it somewhere on the right side of the ListView Control, and change its Name Property Value to ImageList3.

    Note: If you are new to this topic and could not find the above Controls among the ActiveX Controls list, then you must attach the  MSCOMLIB.OCX (Microsoft Windows Common Controls) in the VBA Editing Window’s Tools - - > References Files List. 

    I suggest, you may go through the first Link, given at the top of this page,  the Microsoft TreeView Control Tutorial Page for guidance, on where to look for the related files, and how to add the Microsoft Windows MSCOMLIB.OCX file into the Access Reference Library.  After that, you will be able to find these files on the ActiveX Control’s List.

If you have gone through the earlier Tutorial Sessions and downloaded the Demo Database from the 4th Link given at the top, then open that database.

Open the Form with the ImageList Control in Design View.  Make a copy of the ImageList Control from the Form and place it on the Clipboard.  Close that database, open the current Project database and open the Form frmListView in Design View.  Paste the ImageList Control from the Clipboard on the Form frmListView.  Change the ImageList Control’s name to ImageList3. Those who have the ImageList Control with preloaded Images, skip the following six steps and continue from Step 7.

Uploading Images into ImageList Control.

Alternatively, if you have inserted the ImageList Control from the ActiveX Controls you can upload a few images (4 or 5) from your computer itself. Do the following:

  1. Right-click on the ImageList control, highlight the ImageListCtrl_Object and select Properties.

  2. Select the option 16 x 16 image size option on the General Tab. 

    Caution: After uploading images you cannot change the Image Size value.  If you think you need images of a different size, when displayed, then you need to remove all the existing Images and then select the image size you want on the General Tab and then upload all images again.,

  3. Next, select the Images Tab.

  4. Click on the Insert Picture Command Button and select the Image from your Disk (most of the image types: bmp, png, JPG, JPEG, and tiff are valid).  The selected image will appear in Image control.  The selected image size will be reduced to 16 x 16 pixels and may affect the quality of the Image if you have selected a big image.

  5. The Index Value of the Image is entered by the system. Enter a suitable unique name in the Key control.  The name is Case sensitive if you plan to use the Key-Name of the TreeView Node image-parameter or on ListView Item, rather than the index number of the image. 

  6. You may upload the required number of images this way.   You can copy and paste this ImageList Control for your other Projects.

  7. When you are finished uploading the required images close the Dialog Box.

  8. Insert a Command Button at the bottom of the Controls on the Form with the Name Property Value: cmdExit and with the Caption Exit.

  9. You may save the Form frmListView now with the changes.

We need two tables for Demo purposes to try out the ListView Control with TreeView.  I have created two tables from the Products Table, taken from the sample Database Northwind.  A numeric type field name BelongsTo is added to the Table lvCategory. 

Added four new records in the Table.  The first two new records have their field BelongsTo with value 4, indicating that these records are the child nodes of Record ID number 4.  Similarly, the last two records have the value of 10 in the BelongsTo field. 

In the records with ID numbers 4 and 10, the Product Name field Description shows that they have multiple groups of items.  The Category Group Names are being split into two different categories to place them as Child-Node records under the main group Item. The Category new group record ID Values have been updated in the ParentID field of the Product items in the lvProducts Table, which belongs to the new Category.  The first record with two different categories of items is left untouched, due to some other preference considerations.

The lvCategory Table image is given below.  This will be used for TreeView Nodes, as Parent Nodes to the ListView Control Items too.

The lvProducts Table has about 45 records. The Table image, with a few sample records, is given below for reference:

Working with Two Tables.

Before proceeding further we must take a closer look at both the tables and see how both of them are related to each other in TreeView and ListView Controls.  So far we have worked with only a single Table, with Node Key, Description, and ParentID Fields in the same Table. 

I hope those of you who have followed the earlier Tutorial Episodes so far have a clear understanding of the relationship between the same Field Values in the same Table.  Now, we will see how both these tables (lvCategory and lvProducts) are related to each other for two different Controls.

First, check the lvCategory Table.  It has a BelongsTo field with values in the last four records.  Those four records are the Child Nodes of record IDs 4 and 10 of lvCategory Nodes. These four Nodes, go into the TreeView Control as Child Nodes, to the Parent Nodes: Canned Fruit & Vegetables and Dried Fruits & Nuts in the TreeView Control itself.

Now, take the lvProducts Table and it has ParentID Field Values.  For each Category Item in the lvCategory Table, there are one or more related Product Items on the lvProducts Table.  The number in the ParentID field of the lvProducts Table belongs to the Parent Record in the lvCategory Table.  To put it differently, all the Product items listed in the ListView Control, with the same ParentID value, belong to a particular record in the lvCategory table with the same CID Value.

Note: Creating both these Tables from the Products Table may be kind of time-consuming.  If you have followed this tutorial so far, then you may download the Demo Database from the download link given at the end of this Page. Import lvCategory and lvProducts Tables from the database into your current database with this Project.  When you complete this Project with the Current Database if you face some issues with it you can use the Demo Database as a reference point to correct your work.

The Form Module VBA Code.

  1. Open the frmListView form in Design View.

  2. Display the Class Module of the Form.

  3. Copy and Paste the following full VBA Code into the Module and press Ctrl+S to save the Code.

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim lvList As MSComctlLib.ListView
Dim imgList As MSComctlLib.ImageList
Const Prfx As String = "X"

Private Sub Form_Load()
Dim db As DAO.Database
Dim tbldef As TableDef

    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
    
    Set imgList = Me.ImageList3.Object
    
With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With

    Set db = CurrentDb
    Set tbldef = db.TableDefs("lvProducts")
    
    Set lvList = Me.ListView0.Object
    lvList.ColumnHeaders.Clear
    lvList.ListItems.Clear
    lvList.Icons = imgList
    
    'Initialize ListView & Column Headers Property Values
     With lvList
        .ColumnHeaderIcons = imgList
        .Font.Size = 9
        .Font.Name = "Verdana"
        .Font.Bold = False
        .View = lvwReport
        .GridLines = True
     
        'ColumnHeaders.Add() Syntax:
        'lvList.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
        'Alignment: 0 - Left, 1 - Right, 2 - Center
        .ColumnHeaders.Add 1, , tbldef.Fields(1).Name, 2600, 0, 5
        .ColumnHeaders.Add 2, , tbldef.Fields(3).Name, 2600, 0, 5
        .ColumnHeaders.Add 3, , tbldef.Fields(4).Name, 1440, 1, 5
    End With
    
    Set db = Nothing
    Set tbldef = Nothing

    
   LoadTreeView 'Create TreeView Nodes

End Sub

Private Sub LoadTreeView()
    Dim Nod As MSComctlLib.Node
    Dim firstCatID As Long
    Dim strCategory As String
    Dim strCatKey As String
    Dim strProduct As String
    Dim strPKey As String
    Dim strBelongsTo As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    'Initialize treeview nodes
     tv.Nodes.Clear
     
    'Initialize Listview nodes
    While lvList.ListItems.Count > 0
          lvList.ListItems.Remove (1)
    Wend
    
    strSQL = "SELECT lvCategory.CID, lvCategory.Category, "
    strSQL = strSQL & "lvcategory.BelongsTo FROM lvCategory ORDER BY lvCategory.CID;"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If Not rst.BOF And Not rst.EOF Then
        rst.MoveFirst
        firstCatID = rst!CID
    Else
        Exit Sub
    End If
    ' Populate all Records as Rootlevel Nodes
    Do While Not rst.BOF And Not rst.EOF
            strCatKey = Prfx & CStr(rst!CID)
            strCategory = rst!Category
            
            Set Nod = tv.Nodes.Add(, , strCatKey, strCategory, 1, 2)
            Nod.Tag = rst!CID
        rst.MoveNext
    Loop
    
    'In the second pass of the the same set of records
    'Move Child Nodes under their Parent Nodes
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
        strBelongsTo = Nz(rst!BelongsTo, "")
        If Len(strBelongsTo) > 0 Then
            strCatKey = Prfx & CStr(rst!CID)
            strBelongsTo = Prfx & strBelongsTo
            strCategory = rst!Category
            
            Set tv.Nodes.Item(strCatKey).Parent = tv.Nodes.Item(strBelongsTo)
        End If
        rst.MoveNext
   Loop
    rst.Close
    
    ' Populate ListView Control with Product details
    ' of the first Category Item
    LoadListView firstCatID
    
End Sub


Private Sub LoadListView(ByVal CatID)
    Dim strProduct As String
    Dim strPKey As String
    Dim intCount As Integer
    Dim tmpLItem As MSComctlLib.ListItem
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    ' Initialize ListView Control
    While lvList.ListItems.Count > 0
        lvList.ListItems.Remove (1)
    Wend
   
     strSQL = "SELECT lvProducts.* FROM lvProducts "
     strSQL = strSQL & "WHERE (lvProducts.ParentID = " & CatID & ") "
     strSQL = strSQL & "ORDER BY lvProducts.[Product Name];"
    
    'Open filtered Products List for selected category
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Do While Not rst.BOF And Not rst.EOF
        intCount = intCount + 1
        strProduct = rst![Product Name]
        strPKey = Prfx & CStr(rst!PID)
        
        'List Item Add() Syntax:
        'lvList.ListItems.Add Index,Key,Text,Icon,SmallIcon
        Set tmpLItem = lvList.ListItems.Add(, strPKey, strProduct, , 3) 'first column
            lvList.ForeColor = vbBlue
            
            'List second column sub-item Syntax:
            'tmpLItem.ListSubItems.Add Column - Index, Key, Text, ReportIcon, ToolTipText
            tmpLItem.ListSubItems.Add 1, strPKey & CStr(intCount), Nz(rst![Quantity Per Unit], ""), 6
            
            'List third column sub-item
            tmpLItem.ListSubItems.Add 2, strPKey & CStr(intCount + 1), Format(rst![list Price], "0.00"), 6, "In Local Currency."
        rst.MoveNext
    Loop
    
    Set db = Nothing
    Set rst = Nothing
    
    If intCount > 0 Then lvList.ListItems(1).Selected = True
    
End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim Cat_ID As String
Cat_ID = Node.Tag

LoadListView Cat_ID

End Sub

Private Sub ListView0_Click()
Dim lvKey As String, lvLong As Long
Dim Criterion As String

lvKey = lvList.SelectedItem.Key
lvLong = Val(Mid(lvKey, 2))

DoCmd.OpenForm "ProductDetails", , , , , , lvLong

End Sub
Private Sub cmdExit_Click()
    DoCmd.Close
End Sub

Let us review the Code and try to understand what they do.

In the global declaration area, the TreeView Object (tv), the ListView (lvList) and ImageList (imgList) Object Variables are declared.  The Constant Prfx variable has been declared with the Value “X” and used as the Node Key prefix Value.

When the frmListView is open, the Form_Load() Event Procedure runs.  The database object db and TableDef Object Variable tbldef have been declared.

TreeView0 Control on the Form is assigned to the object Variable tv. The statement tv.Nodes.Clear initializes the TreeView Control Object instance, in memory.

Next, the imgList object variable has been initialized with the ImageList3 Control on the Form.

TreeView Control Display Font and ImageList Properties.

The following statements set the TreeView Control's Font Name, Font-Size and its ImageList Property have been loaded with the imgList object, so that we can use the Image Key Names or Image Index Numbers for TreeView Node Images.

With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With

The ListView Control Property Settings and Column Headers.

After that, the following segment of the Code Initializes the ListView Control and assigns its Property values.

    Set db = CurrentDb
    Set tbldef = db.TableDefs("lvProducts")
    
    Set lvList = Me.ListView0.Object
    lvList.ColumnHeaders.Clear
    lvList.ListItems.Clear
    lvList.Icons = imgList
    
    'Initialize ListView & Column Headers Property Values
     With lvList
        .ColumnHeaderIcons = imgList
        .Font.Size = 9
        .Font.Name = "Verdana"
        .Font.Bold = False
        .View = lvwReport
        .GridLines = True
        
        'ColumnHeaders.Add() Syntax:
        'lvList.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
        'Alignment: 0 - Left, 1 - Right, 2 - Center
        .ColumnHeaders.Add , , tbldef.Fields(1).Name, 2600, 0, 5
        .ColumnHeaders.Add , , tbldef.Fields(3).Name, 2600, 0, 5
        .ColumnHeaders.Add , , tbldef.Fields(4).Name, 1440, 1, 5
    End With
    
    Set db = Nothing
    Set tbldef = Nothing

The Database object tbldef variable assigns with the lvProducts Table Definition. We need data field names to use as the ListView Column Headers, for their related data values, on the top. The Header Text parameter value can be taken from the table field name.

An image also can be displayed in the column header. Therefore, we have assigned the imgList Object to the lvList.ColumnHeaderIcons Property. 

Next, the required Font, Font-size, and Style values have been assigned to their respective Properties of the ListView Control, if the default values are not found suitable. 

The ListView can be viewed differently by changing the View Property Value.  We have selected lvwReport  (value 3 with maximum features).  You may change and experiment with other values 0,1 or 2.  The View Property Value 3 (lvwReport) displays values in columns with column headers, Image Icons, and grid lines.

We have taken three fields of data (Product Name, Quantity Per Unit,  and List Price)  from the lvProducts table to display in three different columns in the ListView Control. 

The column Width value is in Pixels. The Alignment Property Value ranges are from 0 to 2 and the meaning of each value is as given below:

0 – Left Align

1 – Right Align

2 – Align Center.

To create the Column Header information the lvList.ColumnHeaders.Add() method has been called with parameters.

The Add() method Syntax:

Object.ColumnHeaders.Add Index, Key, Text, Column Width, Alignment, Icon

With the above three Statements in the Code segment, we have added all the three ColumnHeaders with the Field Names: Product Name, Quantity Per Unit,  and List Price  Columns.

We have taken this step before adding any item to the ListView Control and that also in the Form_Load() Event Procedure, rather than during populating ListView Items. 

Next, calls the LoadTreeView() Sub-routine to create the TreeView Control Nodes.  As we have discussed in earlier episodes, we have divided this task into a two-step process.

After the variable declarations, the TreeView and ListView controls have been initialized.

Immediately after opening the lvCategory record set, the first record’s Key field (category ID: CID) value has been saved in the firstCatID Variable for later use.

Next, all the Records from the lvCategory Table are initially added to the TreeView Control as Root-Level Nodes.

In the second pass of the same set of records, it checks the BelongsTo field value, if it is empty then those Nodes are retained as Root-level Node, otherwise, the ParentID field value is the Root-Level Node’s ID. Using the ParentID value the current Node has been moved under the Parent Node, as its child Node.

I have explained, this aspect of the two-step procedure of populating Nodes in the TreeView Control in an earlier Episode and proved it by experiments in detail in the last Tutorial Session.

After closing the recordset-object the LoadListView(ByVal CatID) Subroutine has been called.  The first Category record’s CID field value saved in the firstcatID variable has been passed as a parameter to the function.

The Category unique CID Field Value passed in variable firstCatID as a parameter has been used as criteria in the strSQL String to filter the related Product Records. 

We have taken only four Fields of Data from the filtered records to display on the ListView Control.  Key-field: PID, Product Name, Quantity Per Unit, and List Price.  PID field value has been used as a Key and will not appear in the display.

The ColumnHeaders.Add() method has been called three times, to add all three Column Headers. 

Note: Check the statement we have not provided any Key parameter value, but the Index value will be inserted by the system.  We have not planned to work with the Column Headers once they have been loaded.  Even if we do, we can address the Column with the index value.  But, we must load the Product record field values in the same order as the Column Headers sequence.

Populating ListView Control with Product Values in Columns.

For displaying the data listing on the ListView Control, we need two sets of Add() methods, with three different Key values, to add all three columns of Values to the ListView Control.

The first column value will be added with the lvList.ListItems  Add() method.  All other Column Values can be added with the lvList.ListSubItems Add() method.

We have created one Counter Variable: intCount, and increments its value at every Do While . . . Loop cycle and its current value will be added to the Key (PID) value to make the Key-Value unique in the lvList.ListSubItems.Add() method.

The first column’s ListItems.Add() Method Syntax is as given below:

‘lvList.ListItems.Add Index,Key,Text,Icon,SmallIcon

Set tmpLItem = lvList.ListItems.Add(, strPKey, strProduct, , 3)

The above statement is similar to the TreeView Node’s Add method.  The tmpLItem is declared as a ListItem Object and holds the added ListItem’s reference so that it can be used for adding its ListSubItems.

The lvList.ListSubItems.Add()  Method Syntax is slightly different as given below. The Syntax shown is for the second .ListSubItems.Add() method with the second parameter value strPKey & Cstr(intCount + 1).  The first ListSubItems.Add() method will have the Key Value as strPKey & Cstr(intCount).

‘tmpLItem.ListSubItems.Add Index, Key, Text, ReportIcon, ToolTipText

tmpLItem.ListSubItems.Add 2, strPKey & CStr(intCount + 1), Format(rst![List Price], "0.00"), 6, "In Local Currency."

The ListSubItem’s Add method has ReportIcon and TooltipText as the last two parameters.  We have used the Tooltip-Text parameter value for the last column only.

NB: The values loaded into Columns and all other settings related to that will work only when you select the View Option – 3 (lvwReport) except in the first column.  Otherwise, they are all ignored.

The Tooltip text is displayed when the mouse pointer hovers over the last column.

In all three columns (ListItems & ListSubItems) Add() method uses the same record PID Value as Key.  Since, different columns of the same record require a unique ID Value as Key the intCount Variable value has been incremented by one, for ListSubItems and converted into a string then added with the PID field value.

This way the selected Category item-related Product Records are all listed in the ListView Control.

Separate Form to Display full Product Record Details.

Since the lvProducts Table has more fields and values, than what we could display on the ListView Control, the ListView item Click Event will open a separate Form ProductDetails and displays the full record details.

The image of the Form with all details of the selected product record is given below:

The ListView0_Click() Event Procedure.

Private Sub ListView0_Click()
Dim lvKey As String, lvLong As Long
Dim Criterion As String

lvKey = lvList.SelectedItem.Key
lvLong = Val(Mid(lvKey, 2))

DoCmd.OpenForm "ProductDetails", , , , , , lvLong

End Sub

The ListView0_Click() Event extracts the Product’s Key-Value and passes it to the Form as an Open Argument (OpenArgs).

The ProductDetails Form.

In the Form_Open() Event Procedure, the OpenArgs value has been used to create a Filter Criteria to filter the source records and display them on the Form.

The Product Details Form Module Code listing:

Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim lngID As Long
lngID = Nz(Me.OpenArgs, 0)
If lngID > 0 Then
    Me.Filter = "id = " & lngID
    Me.FilterOn = True
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
With Me
    .Filter = ""
    .FilterOn = False
End With
End Sub

The ProductDetails Form's source data comes from the filtered records, based on the PID (Product ID) Code passed to the Form through the OpenArgs.  The Original Products Table was downloaded from the NorthWind.accdb database and renamed as ProductsNew.

Hope you enjoyed doing the ListView Control Project.  More to come with the ListView Control.

Download the Demo Database from the Link, given below, and happy List Viewing.

Demo Database Download.


  1. ROUNDDOWN Function of Excel
  2. Call Function From MouseMove Event
  3. Date2Text and Text2Date Function
  4. WithEvents Ms-Access Class Module Tutorial
  5. WithEvents and Defining Your Own Events
  6. withevents Button Combo List TextBox Tab
Share:

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:

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