LEARN MS-ACCESS TIPS AND TRICKS: ImageList Control

Showing posts with label ImageList Control. Show all posts

Assigning Images To ListView Items Tutorial-03

a.p.r. pillaiDecember 20, 2021ImageList Control, ListView Control 3 comments

The ImageList ActiveX Control.

To assign images to the ListView control, we need the support of another ActiveX control: the ImageList control. You may have noticed how Windows Explorer displays icons—such as folder icons in the closed state, open when the folder is selected, and displaying files with different icons based on the file type. While the ListView control does not provide that level of flexibility, it does allow us to display icon images in Column Headers, List Items, and ListSubItems when populating their contents.

The Sample Demo Images.

Sample Preview

The following image shows an example of the Northwind Trading Employees listing, where each employee’s details are displayed in the ListView control along with their small photo icons. These images are assigned through the ImageList control and linked to the ListView items, making the data both informative and visually appealing.

Right-click to open the Large Image in a New Window.

Note: Using larger image sizes will automatically increase the row height of the ListView records, so you can display bigger photos if required.

The following sample image shows the ListView control (displayed in the right-side panel) used together with the TreeView control. The TreeView ActiveX control was already introduced in an earlier tutorial series. For quick reference, you can find the list of TreeView Control Tutorial Series links at the bottom of this page.

Right-click to open the Large Image in a New Window.

In the above image, icons have been applied to all data columns and the column header labels to demonstrate how images can be displayed in the TreeView Control.

On the left panel, the TreeView control nodes show the familiar folder icons in open and closed states. These behave differently from the ListView items: when a TreeView node is clicked, the open-folder image is displayed; clicking the same node again reverts it to the closed-folder image.

The ListView Control Programming Tutorial Series.

I hope you have already gone through the ListView Control Tutorial Sessions 1 and 2 and are now ready to continue with this new episode on using the ImageList Control along with the ListView Control.

For your convenience, the links to the earlier tutorials are provided below. I encourage you to review them before proceeding, as they cover basic concepts of the ListView Control along with supporting VBA code. This background will put you in a better position to follow along and clearly understand the new features we are adding in this session.

Source Data and Demo Form.

Let’s begin by creating a new Form and preparing the Employees Table for our ListView Control demo project.

Import the Employees Table from the NorthWind.accdb sample database.
Create a new SELECT Query using the SQL statement provided below.
Save this query with the name EmployeesQ.

SELECT [TitleOfCourtesy] & " " & [FirstName] & " " & [LastName] AS [Employee Name], 
Employees.EmployeeID, 
Employees.Title, 
Employees.HireDate, 
Employees.Address, 
Employees.City, 
Employees.Region, 
Employees.PostalCode, 
Employees.Country, 
Employees.HomePhone, 
Employees.Extension, 
Employees.Notes
FROM Employees;

If your Employees Table structure is different, don’t worry. For the first column value, I have combined three fields together to form [Employee Name]. For the remaining columns, you can use whatever fields you have—include all of them or just a few, and in any order you prefer.
Steps to Set Up the Form
1. Create a new Form and open it in Design View.
2. From the ActiveX Controls List, insert a Microsoft ListView Control.
3. From the same list, insert a Microsoft ImageList Control.
4. Resize the ListView Control as shown in the sample image of the form (provided earlier).
5. Move the ImageList Control to the top-right corner of the ListView Control, as shown in the demo image. You can also place it in any convenient location on the form.
Note: The ImageList Control will not be visible when the form is opened in Normal View; it appears only in Design View for configuration.
Right-click to open the Large Image in a New Window.
Select the ListView Control and open its Property Sheet.
- Change the Name property to ListView1.
Select the ImageList Control, open its Property Sheet, and
- Change the Name property to ImageList0.
Important: Both the ListView and ImageList controls come with their own dedicated Property Sheets. Some of their property names and values may also appear in the Access Property Sheet, but updates made there may not always reflect correctly in the controls themselves. For reliable results, always make changes in each control’s own Property Sheet.

ListView Control Property Sheet.
Right-click on the ListView Control, point to the ListViewCtrl Object option from the shortcut menu, and then select Properties.
The General tab of the ListView Control Property Sheet will appear, as shown in the image below.

Right-click to open the Large Image in a New Window.
Change the property values on the General tab as shown in the image above.

Let us begin by loading the Employees data into the ListView Control.
The Form Module VBA Code

Copy and Paste the following VBA Code into the Form's Class Module:

Option Compare Database
Option Explicit

Dim lvwList As MSComctlLib.ListView
Dim lvwItem As MSComctlLib.ListItem
Dim ObjImgList As MSComctlLib.ImageList
Dim db As DAO.Database
Dim rst As DAO.Recordset


Private Sub cmdClose_Click()
   DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
 Call LoadListView("EmployeesQ")
End Sub

Private Sub LoadListView(ByVal tblName As String)
    Dim strFldName As String
    Dim intCounter As Integer
    Dim j As Integer
    Dim strLabel As String

'Assign ListView Control on Form to lvwList Object
Set lvwList = Me.ListView1.Object
'Set ObjImgList = Me.ImageList0.Object
    
'Assign Form Header labels Caption Text
 strLabel = UCase(tblName) & " " & "IN LISTVIEW CONTROL - TUTORIAL-03"
 Me.Label8.caption = strLabel
 Me.Label9.caption = strLabel
 
 With lvwList
    '.Icons = ObjImgList
    '.SmallIcons = ObjImgList
    '.ColumnHeaderIcons = ObjImgList
    .Font = "Verdana"
    .Font.Size = 10
    .Font.Bold = True
 End With
 
 Set db = CurrentDb
 Set rst = db.OpenRecordset(tblName, dbOpenSnapshot)
 
 'Create Column Headers for ListView
 With lvwList
    .ColumnHeaders.Clear 'initialize header area
    For j = 0 To rst.Fields.Count - 1
        strFldName = rst.Fields(j).Name
   'Syntax:
   '.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
        .ColumnHeaders.Add , , strFldName, iif(j=0,3200,2000)
    Next
 End With
 
 'Initialize ListView Control
  While lvwList.ListItems.Count > 0
        lvwList.ListItems.Remove (1)
  Wend

 With lvwList
 Do While Not rst.EOF And Not rst.BOF

   'Syntax  .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
        Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value, "")))
        
   'Add next columns of data as sub-items of ListItem
        With lvwItem
   'Syntax     .Add Index,Key,Text,Report Icon,TooltipText
         For j = 1 To rst.Fields.Count - 1
            .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, ""))
         Next

       End With
       rst.MoveNext
Loop
rst.Close
    'reset lvwItem object
    Set lvwItem = Nothing
End With

Set rst = Nothing
Set db = Nothing
End Sub

Note: The VBA lines in red in the ListView Control are commented out, and we will enable them shortly.

Save your Form with the Name frmEmployees.
Open the Form in Normal View.
The EmployeesQ Query Records Listing will look like the following Image:

Right-click to open the Large Image in a New Window.

Review of the VBA Code

We have already reviewed most of the above VBA code in ListView Control Tutorial Sessions 01 and 02, with only a few additions specific to the ImageList Control. These include its declaration, initialization, and a few lines for setting the Font Name, Font Size, and Font Style.

Another important change is in the LoadListView() procedure. In this version, the procedure accepts a Table or Query Name as a parameter. All query types—except Action Queries—as well as Access Tables and Linked Tables, are valid inputs. The specified Table or Query name is supplied when the program is called from the Form_Load() Event Procedure.

All field names from the given Table or Query are used as Column Header Labels (the third parameter) in the ColumnHeaders.Add() method. The Index and Key parameters (first and second) are not used here. The system automatically assigns Index values in sequence.

The fourth parameter specifies the column width in pixels. In our example, the first column is set to 3200 pixels to accommodate the Employee Name, while all other columns are set to 2000 pixels.
Alignment and Icon parameters for column headers are not used. By default, column headers are left-aligned. The available alignment options are:
- 0 - lvwColumnLeft
- 1 - lvwColumnRight
- 2 - lvwColumnCenter

You can view these options on the Column Headers Tab of the ListView Control Property Sheet:

Click the Insert Column button and enter a temporary column name.
Open the Alignment property to view the available options.
Click Remove Column to delete the temporary column.

Note: If you prefer to add Column Header Labels manually, instead of loading field names through VBA code, you can type them directly here. They will then appear as column headers when the data is displayed.

A sample view of the Icon image, on the left side of the header column names, can be seen in the second demo image in the right-side panel at the top of this page.

For the data itself:

The first column (Employee Name from the EmployeesQ query) is assigned to the ListItems.Text property using the .Add method. Here too, the Index and Key parameters are omitted—the system automatically inserts Index numbers as serial values.
From the second field onward, column values are added through the ListSubItems.Add() method of the ListView Control.

Note: All values are stored as Text in ListItems.Text and ListSubItems.Text, regardless of their original data type in the source Table/Query. To prevent errors, the code checks for Null values and converts them to text using the CStr() function.

The ImageList control.

In the main program, the ImageList Control initialization statements have been temporarily commented out. These lines are highlighted in red in the code segment below. We will revisit, explain, and enable them once we are ready to proceed with uploading images into the ImageList Control.

'Assign ListView Control on Form to lvwList Object
Set lvwList = Me.ListView1.Object
'Set ObjImgList = Me.ImageList0.Object
    
'Assign Form Header labels Caption Text
 strLabel = UCase(tblName) & " " & "IN LISTVIEW CONTROL - TUTORIAL-03"
 Me.Label8.caption = strLabel
 Me.Label9.caption = strLabel
 
 With lvwList
    '.Icons = ObjImgList
    '.SmallIcons = ObjImgList
    '.ColumnHeaderIcons = ObjImgList
    .Font = "Verdana"
    .Font.Size = 10
    .Font.Bold = True
 End With

The first red-highlighted statement initializes the ObjImgList object with the ImageList0 control placed on the frmEmployees form. Before making changes to the code, let’s first explore the available options for uploading images into the ImageList Control.

About Uploading Images.

The next step is to upload some sample images into the ImageList Control. This can be done in one of two ways.

Before starting, prepare at least two small images in any popular format—such as JPG, JPEG, BMP, or PNG—BMP is the preferred type. The ImageList Control supports the following standard image sizes (available on the General tab of its Property Sheet): 16×16, 32×32, 48×48 pixels, or a Custom size.

To configure this:

Right-click on the ImageList Control,
Highlight the ImageListCtrl Object,
Select Properties,
Then, on the General tab, choose your desired image size before uploading any images.

If you have larger images and want to keep their original size, select the Custom option. Otherwise, choosing a predefined size will automatically scale the images, which may reduce their quality. Keep in mind that using very large images will increase the row height when displayed in the ListView Control.

For best results, icon-style images are ideal. However, you should experiment with different sizes—large, small, and very small—along with the available options to determine what works best for your project.

You can upload images into the ImageList Control in one of the following two ways:

1. Upload Images from disk through VBA Procedure.

The sample VBA Procedure will look like the Code Segment given below, taken from the TreeView Control Tutorial:

  
  Set objImgList = Me.ImageList0.Object
  objImgList.ListImages.Clear
  
strFolder = "D:\Access\TreeView\"
With objImgList
    With .ListImages
         .Add Index:=1, Key:="FolderClose", Picture:=LoadPicture(strFolder & "folderclose2.bmp")
         .Add Index:=2, Key:="FolderOpen", Picture:=LoadPicture(strFolder & "folderopen2.bmp")
         .Add Index:=3, Key:="ArrowHead", Picture:=LoadPicture(strFolder & "arrowhead.bmp")
    End With
End With

With tvw 'TreeView Control
    .ImageList = objImgList 'assign imagelist Object to TreeView Imagelist Property
End With

The first statement initializes the objImgList object with the ImageList0 control on the form.

The next statement clears any existing images in the ImageList control, ensuring it is ready for new uploads from disk. For this method to work consistently, the required image files must always be available on the disk.

The method objImgList.ListImages.Add() is then used to upload images from disk. When using named parameters, the parameter values can be provided in any order. For example:

Index := 1 can appear at the end of the line,
Key := "FolderClose" can be listed first,

and so on.

However, if parameter names are omitted, the parameters must be supplied in the following order:

         .Add 1, "FolderClose", LoadPicture(strFolder & "folderclose2.bmp")

To display an image in the ListView control, you can reference it either by using the Image Index Number (e.g., 1) or by specifying the Key value (e.g., "FolderClose") as the Icon or SmallIcon parameter in the ListItems.Add() method.

We used this same approach earlier in the TreeView Control Tutorial. You may refer to that page and download the demo database for reference.

This method loads the images into the ImageList object instance in memory, without altering the physical ImageList control on the form. However, it is important to note that the source images on disk must always be available every time frmEmployees is opened.

2. Uploading Images from disk manually.

This is a one-time setup task: locating the images on disk and uploading them into the ImageList Control.

The key advantage of this method is that once images are uploaded into the ImageList Control, they remain embedded in the control. You won’t need to reload them from disk each time the form is opened. Moreover, the ImageList control—with the images included—can be copied and reused in other projects, or even shared with colleagues, eliminating the need for duplicate image setup.

For this demonstration, let’s use the manual upload method, which is the more reliable approach. Prepare two sample .bmp images with a resolution of 50 x 50 pixels (e.g., image1.bmp, image2.bmp) and keep them ready in a folder, such as D:\Access\, for reference.

Now, follow these steps:

Open frmEmployees in Design View.
Right-click on the ImageList Control, highlight ImageListCtrl Object, and select Properties.
On the General tab, select the Custom option to retain the original resolution of the uploaded images.

At this point, the General tab of the ImageList Control will appear as shown in the image below.

Right-click to open the Large Image in a New Window.

The Images tab View of the ImageList Control

Right-click to open the Large Image in a New Window.

Note: After testing uploaded images in the ListView control, if you wish to try a different size option (48×48, 32×32, or 16×16), you must first remove all existing images. Then, return to the General tab, select the new size option, and upload the images again. The uploaded images will automatically be resized to match the selected option.

In the sample below, two images have been uploaded using the Insert Picture command button. The first image is currently selected, shown in a slightly raised position. The Index control displays the value:1, while the Key textbox shows the text First. The Index value is generated automatically, but the Key value must be entered manually. Use a meaningful Key name that is easy to remember and logically relates to the data.

Both the Index number and the Key text can be used in the Icon or SmallIcon parameters of the ListItems.Add() method.

If you plan to rely on Index numbers, make sure that the image upload sequence matches the data sequence in the ListView (for example, each employee’s name aligns correctly with their photo). However, a more practical approach is to use Key text values—such as an employee’s first name—since they are easier to associate directly with records. For generic icons, descriptive Key names (e.g., FolderClosed, FolderOpen) provide clarity about their purpose.

Steps to upload images:

Open the Images tab of the ImageList Control.
Click Insert Picture, browse the file 'D:\Access\Image1.bmp' to select it, and click Open to upload the image.
In the Key textbox, type a unique Key value (e.g., First).
Repeat steps 2–3 for the second image (e.g., D:\Access\Image2.bmp), assigning it another unique Key value.

Your ImageList Control is now configured with sample images and ready to display them in the ListView Control.

Assigning ImageList Object to ListView Object Properties.

To display images in the ListView Control, the following ListView Object properties must be linked to the ImageList Object:

ListView.ColumnHeaderIcons
ListView.Icons
ListView.SmallIcons

The next step is to assign the ImageList Object to the ListView Control through these properties in VBA code:

lvwList.ColumnHeaderIcons
lvwList.Icons
lvwList.SmallIcons

This must be done before you can use image references (Index or Key values) in the following methods:

ColumnHeaders.Add()
ListItems.Add()
ListSubItems.Add()

We have already added the necessary VBA statements in the LoadListView() procedure of the main program, but they are currently commented out. To activate them:

Open the LoadListView() procedure.
Locate the four lines of code (highlighted earlier in red).
Remove the comment symbol (') at the beginning of each line to enable them.
Update the code to include the appropriate Icon Index values in the method parameters.

For example, modify the following statements (originally shown in red in the main program) to use Icon Index numbers 1 and 2 for the Icon SmallIcon parameters:


' Example modification in LoadListView()
Set lvwList.ColumnHeaderIcons = objImgList
Set lvwList.Icons = objImgList
Set lvwList.SmallIcons = objImgList

lvwList.ListItems.Add , , "Employee 1", , 1   ' Icon Index = 1
lvwList.ListItems.Add , , "Employee 2", , 2   ' SmallIcon Index = 2

This ensures that images stored in the ImageList Control are correctly displayed alongside the ListView items.

 With lvwList
 Do While Not rst.EOF And Not rst.BOF

   'Syntax  .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
       ' Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value,"")))
       'Change to 
         Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value,"")), 1, 2)
        
   'Add next columns of data as sub-items of ListItem
        With lvwItem
   'Syntax     .Add Index,Key,Text,Report Icon,TooltipText
         For j = 1 To rst.Fields.Count - 1
           ' .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, ""))
           'Change to           
             .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, "")),,"Click"
         Next

       End With
       rst.MoveNext
Loop
rst.Close

Since we have only two images available, we will use the first image (Index = 1) as the Icon parameter and the second image (Index = 2) as the SmallIcon parameter.

The Icon image is displayed only when the ListView display option is set to 0 - lvwIcon.
The SmallIcon image is displayed in all other ListView display options.

In the ListSubItems.Add() method, we have not assigned any image reference. Instead, we used the next parameter to specify a Tooltip text "Click". This text will appear as a tooltip when the mouse pointer hovers over any column from the second column onward.

After making these code changes:

Save the form frmEmployees.
Open the form in Normal View.
You should now see the ListView display, similar to the sample image shown at the top of this page.

The SmallIcon will remain visible in all ListView display modes, except for 'lvwIcon', which uses the larger Icon image.

Check the following sample ListView screenshots of Employee data for reference.

0 - lvwIcon View

Right-click to open the Large Image in a New Window.

2 - lvwList View

Right-click to open the Large Image in a New Window.

The first sample image at the top of this page shows the 03 - lvwReport view.
This is the only view that displays all column values in a datasheet-like format.

To explore other views:

Open the form frmEmployees in Design View.
Select the ListView control and open its Property Sheet.
Locate the View property.
Change the setting to try out each option (0 - lvwIcon, 1 - lvwSmallIcon, 2 - lvwList, 3 - lvwReport).
Save the form and open it in Normal View to see how the data is displayed in each case.

This hands-on test helps you understand how the same data looks in different ListView display modes and which one best fits your application.

Download the Demo Database.

Download ListView03.zip

TreeView Control with Subforms

a.p.r. pillaiJanuary 19, 2021ImageList Control, ListView Control, TreeView Control No comments

TreeView Control and Subforms.

In this session of the TreeView Control Tutorial, we will work with a main form (frmTreeViewtab) that hosts a TreeView control and two Subforms. The ImageList control, preloaded with images imported from an earlier demo project, is also included.

We will continue using the same tables from our previous projects: lvCategory and lvProducts.

The lvCategory table provides the category information. Its primary key field (CID) and description field (Category) are used as the Key and Text parameters of the TreeView node’s Add() method.
The lvProducts table stores detailed product information, including product code, description, stock quantity, and list price. It also contains a ParentID field that links each product to a category by storing the corresponding CID. This establishes a master–child relationship between the two tables.

On the form, product records are managed through two subforms placed on a Tab Control:

First Page (Data View Subform): Displays all products that belong to the category currently selected in the TreeView. Users can view records here and select one for editing.
Second Page (Edit Subform): Provides an editable view of the record selected on the first page. Key fields (highlighted in gray) are locked to prevent modification.

This setup allows users to browse products by category via the TreeView, view product details in the first subform, and switch to the second subform to edit the selected record while maintaining data integrity.

TreeView with Subforms Design View.

The Design View of the form frmTreeViewTab is given below:

Right-click to open the Large Image in a New Window.

On the main form, the first two unbound text boxes are updated whenever the user selects a Category item from the TreeView control.

The third unbound text box (p_ID) is used to track the current product. By default, it is initialized with the PID value of the first product record. If the user selects a different record in the first subform, the text box is updated with that record’s PID instead.

This ensures that the record currently highlighted in the data view subform is made available in the edit subform, allowing the user to make modifications seamlessly.

Links to Earlier Tutorial Sessions.

The earlier Tutorial Session Links are given below for ready reference:

The CatID unbound text box on the main form is assigned to the [Link Master Fields] property of the first subform.

Similarly, the p_ID unbound text box (the product code) is linked in the [Link Master Fields] property of the second subform on the Edit tab page.

The value of p_ID is automatically updated whenever the first subform is refreshed or when the user selects a specific record. This ensures that the corresponding product is always available for editing on the second subform.

Normal View of the Screen.

The normal view of the frmTreeViewTab form is given below:

Right-click to open the Large Image in a New Window.

On the second subform, the key fields of the product record are displayed in gray text and are locked to prevent modifications.

**The form frmTreeViewTab Class Module VBA Code:**

Option Compare Database
Option Explicit

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

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

'Initialize TreeView Nodes
    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
'Initialixe ImageList Object
    Set imgList = Me.ImageList3.Object
    
'Modify TreeView Font Properties
With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With
    
   LoadTreeView 'Create TreeView Nodes

End Sub

Private Sub LoadTreeView()
    Dim Nod As MSComctlLib.Node
    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
    
    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)

    ' Populate all Records as Rootlevel Nodes
    Do While Not rst.BOF And Not rst.EOF
        If rst.AbsolutePosition = 1 Then
           Me![CatID] = rst![CID]
        End If
            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
    

    TreeView0_NodeClick tv.Nodes.Item(1)
    
End Sub

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

'Initialize hidden unbound textbox 'Link Master Field' values
Cat_ID = Node.Tag
Me!CatID = Cat_ID
Me![xCategory] = Node.Text

End Sub

Private Sub cmdExit_Click()
    DoCmd.Close
End Sub

Since the usage and functionality of the TreeView and ImageList controls were thoroughly explained in earlier sessions, only a few of those previously introduced VBA subroutines are included in this form’s module.

So far, we have designed several screens using TreeView, ListView, ImageList, and ImageCombo controls in MS Access. I hope you will find these examples a valuable reference for designing the interface of your own projects.

MS Office Version Issues for TreeView Control.

If you encounter any issues running the demo database in your version of Microsoft Access, you may refer to the following link for corrective steps that could help resolve the issue.

In earlier versions, these controls did not function properly on 64-bit systems. However, in September 2017, Microsoft released an updated version of the MSCOMCTL.OCX library. For your reference, an extract from Microsoft’s documentation is provided below.

Click on the document image above to view the full text of the 2017 Update: 1707 release. For additional guidance, you may also refer to the following link, which provides some useful troubleshooting tips.

SOLVED MSCOMCTL.OCX DOWNLOAD, REGISTER for 64-BIT WINDOWS

By leveraging the TreeView control and related objects, we can design user interfaces that are both more visually appealing and more efficient for our future projects.

Download the Demo Database.

Download TreeViewSubforms.zip

ListView Control Drag Drop Events Handling

a.p.r. pillaiDecember 31, 2020ImageList Control, ListView Control, TreeView Control No comments

ListView Control Drag-Drop Handling.

We are familiar with the drag-and-drop operations of the TreeView control and rearranging Nodes in MS Access. All base records for TreeView nodes come from a single Access table. When a node is moved, we simply update the ParentID field of the source record with the Target Node ID. This will change the position of the node in TreeView, without physically moving the record.

In this project, we extend the idea by introducing the ListView control to the right side of the TreeView. Here, we work with two different Access tables:

'lvCategory' – Stores category codes and descriptions.
'lvProducts' – Stores products under each category.

This approach helps to visualize the relationship between the two tables and to understand what needs to be changed when a product item (ListView entry) is moved from one category to another in the TreeView.

The lvCategory table contains 20 records representing the TreeView nodes, while the lvProducts table has 45 product records for the ListView. Each product record is linked to its category, the Category ID (CID), stored in the ParentID field of the product table. When a product is reassigned to a new category, this link updates immediately, and the ListView reflects the change.

The demo data used here was adapted from Microsoft Access’s sample Northwind database and split into two related tables.

Based on the ParentID field of the lvProducts table, we can filter and display all related products in the ListView whenever a category node is selected in the TreeView.

Topics Covered So Far.

The main topics we have explored on the TreeView, ImageList, ImageCombo, and ListView controls in MS Access are listed below:

The ListView Drag-Drop Task.

When it comes to the ListView’s drag-and-drop operation, the process is much simpler compared to performing the same action entirely within the TreeView control. Since this action involves both the TreeView and the ListView controls, we can handle it using the TreeView0_OLEDragDrop() event with a small amount of VBA code.

Here’s how it works:

The ListView displays product items that belong to the currently selected category from TreeView.
When a Product item needs to be placed under another category, the user just drags it from the ListView Control and drops it into the target category Node in the TreeView.
When this happens, the product record’s ParentID field is updated with the Category ID (CID) of the TreeView Node.
The product item is then automatically displayed under the new TreeView products category in the ListView.

This operation is designed as a one-way action—product items move from the ListView under a different TreeView Category Node. They are not dragged back in the reverse direction.

The screenshot below shows a trial run of this feature in the demo form frmListViewDrag:

Right-click to open the Large Image in a New Window.

The above screenshot shows that the Beverages Category Item in the TreeView has been selected. The Products belonging to the Beverages category are listed in the ListView Control.

Right-click to open the Large Image in a New Window.

The ListView Control In Design View.

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

TreeView Control: TreeView0
ListView Control: ListView0
ImageList Control: ImageList3
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 TreeView, ListView, and ImageList controls. During this process, the column headings of the ListView control are created, followed by populating its list items. Once these steps are completed, the LoadTreeView() subroutine is executed.

The LoadTreeView() subroutine loads the product category nodes into the TreeView control using records from the lvCategory table. This is done in two stages rather than in a single pass. The reason for this two-step approach was explained earlier (see the 7th link in the list above), so it is not repeated here.

After the TreeView has been populated, the LoadListView() subroutine is called with the first category record’s CID value (1) as its parameter.

This call filters the product records whose ParentID field equals 1 and displays them in the ListView control. The detailed procedure for this step was covered in last week’s post (8th link in the list 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:

TreeView0_OLEStartDrag()
TreeView0_OLEDragOver()
TreeView0_OLEDragDrop()
TreeView0_OLECompleteDrag()

The first subroutine initializes the nodes involved in the operation, while the last one resets their status once the process is complete.

The second subroutine, OLEDragOver(), functions similarly to the MouseMove event. It tracks the mouse movement during the drag-and-drop operation, highlights the node text when the pointer hovers over a node, and follows the cursor’s path until the left mouse button is released.

The code for the TreeView0_OLEDragDrop() procedure is shown 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 and Drop Action Step by Step.

The TreeView0_OLEDragDrop() procedure is triggered as soon as the left mouse button is released to complete the drop action. At the start, references to the active (source) node and the target node are stored in the object variables tv_nodSource and tv_nodTarget, respectively.

The code first checks whether the ListItem was dropped on a valid TreeView node. If it was dropped on the same source category node or on an empty area of the TreeView, the action is invalid. In the case of an empty drop area, tv_nodTarget will contain the value Nothing, prompting a message to be displayed before the procedure exits.

Next, the key values of the source and target nodes are stored in string variables. If both keys are identical, it means the ListItem was dropped onto its own parent (category) node, and the procedure terminates without changes.

If the keys differ, the product record’s ParentID field is updated with the CID value of the target category node, and the ListView items are refreshed. To do this:

The selected ListItem’s key value (PID) is saved in the variable strListItemKey.
The target category’s actual CID value is extracted from the target node by removing the prefix character 'X' and is saved in the variable vCatID. This value is used to update the product record’s ParentID field, effectively reassigning it to the new category.
The product’s PID is also stored in the variable lngPID, which serves as the filter criterion that locates the specific product record for updating.

An UPDATE SQL statement is then created, using lngPID as the filter and updating the ParentID field with the vCatID value. The change is executed with the CurrentDb.Execute method.

Finally:

The node highlight is reset to the source node.
The TreeView0_NodeClick() subroutine is called with tv_nodSource as its parameter, refreshing the ListView control to reflect the updated data.
The Close button exits the form when clicked.