Activex ListView Control Tutorial-01 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

In MS-Access we have ListBox control and mostly it will have only a few columns of data, to find the item(s) quickly. The source data for this control are either typed directly into the Row-Source Property as a Values-list or loaded from the source Table or Query. The Combo Box control keeps its data hidden and needs a click to reveal the list to select. These Objects are already built-in as Access Controls.

But, there is another List Control that we always use in our database, can you guess what it is? Yes, the Data Sheet View Control. The records are displayed from Table, Query. In all these cases we see data in Datasheet View as a big list.

But there are other groups of Controls too in Microsoft Access, the ActiveX Controls. We are already familiar with one of these controls - the Common Dialog Control or File Browser control.

Here, the topic is Windows ListView Control. You can visualize it as an Object similar to Windows Explorer, where you can display Items with Image Icons, Small image Icons, as a List, or like the Explorer's Detail View Pane. You can load your Table / Query data into this control to display them in Datasheet View, re-arrange the Columns or Rows, Sort the rows, display Images next to items, and so on. Other Programming Languages, like VB6, VB.NET, C#, etc., use Windows ListView Control. We are going to see how we can use it in Microsoft Access Database.

A simple ListView Demo screen, with some quick sample data, is given below:

We will make the above image-like display the starting point of the ListView Control Tutorial. We have uploaded ten rows of data into the ListView control, with a few lines of the VBA Code. The ListView ActiveX Control you may not find in the existing list of ActiveX Controls in Access. We have to add this Control's Library file MSCOMCTL.OCX from C:\Windows\System32 folder into the Access Reference Library. Once it is added you can find this control with the name Microsoft ListView Control, Version 6.0 among other ActiveX controls.

So, let us add the MSCOMCTL.OCX Library File to our database. This is the source library of ActiveX Controls like ListView, TreeView, and ImageList. If you have already gone through our earlier TreeView control tutorial Pages, then you are already introduced to this control.

Windows Common Controls Library File.

Do the following to attach the MSCOMCTL.OCX File:

Open your Database and open the VBA Editing Window (Alt+F11).
Select References… from the Tools Menu.
Click on Browse Button to find MSCOMCTL.OCX File (Microsoft Windows Common Controls.)
Look for the above file in C:\Windows\System32\ Folder, if you have a 32 Bit System or you have Windows 11 Operating System.
If you could not find it there, then look for the Folder C:\Windows\sysWOW64\ (64 Bit System), and there you will find this file.
Select the file MSCOMCTL.OCX and Click Open Command Button to attach the file to your Database.
Press Alt+F11 again to come back to the Database Window.

Let us design a sample Form to match the above Image given at the top of this page.

Create a new Blank Form.
Select the ActiveX Control Button from the Controls Group of options.
Find and select the Microsoft ListView Control from the displayed list and click the OK button to insert a ListView control on the Form’s Detail Section.
Click and hold on to the control’s resizing handle, at the right bottom corner, and drag to the right and down to make it large enough like the sample image given above.
Drag the ListView control itself to the right and down to give some margin to the left and leave enough space above to create a Heading Label.
Click on the ListView Control to select it, if it is not in a selected state.
Display the Property Sheet and change the ListView Control’s name to ListView1.
Create a Label control above and change the Caption property value to ListView Control Tutorial. You may format the label Caption with font size, color, etc., the way you like it.
Create a Command Button below the LlistView control and change its Name Property Value to cmdClose and its Caption Property Value to Close. The completed design will look like the following when your design is complete:

Now, Save the Form with the name: ListViewTutorial and keep the Form in the design view.
Press Alt+F11 to go back to the Form’s Class Module Window.
The VBA Code.

Copy and Paste the following Code into the Form's VBA Module, replacing existing lines of code if any:

Option Compare Database
Option Explicit

Dim lvwList As MSComctlLib.ListView
Dim lvwItem As MSComctlLib.ListItem
Dim ObjImgList As MSComctlLib.ImageList
Const prfx As String = "X"

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

Private Sub Form_Load()
    Call LoadListView
End Sub

Private Function LoadListView()
    Dim intCounter As Integer
    Dim strKey As String

'Assign ListView Control on Form to lvwList Object
 Set lvwList = Me.ListView1.Object
 
 'Create Column Headers for ListView
 With lvwList
    .ColumnHeaders.Clear 'initialize header area
   'Parameter List:
'Syntax: .ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
    .ColumnHeaders.Add , , "Name", 2500
    .ColumnHeaders.Add , , "Age", 1200
    .ColumnHeaders.Add , , "Height", 1200
    .ColumnHeaders.Add , , "weight", 1200
    .ColumnHeaders.Add , , "Class", 1200
 End With
 
 'Initialize ListView Control
  While lvwList.ListItems.Count > 0
        lvwList.ListItems.Remove (1)
  Wend
    
 With lvwList
    For intCounter = 1 To 10
        strKey = prfx & CStr(intCounter) '
   'Syntax: .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
        Set lvwItem = .ListItems.Add(, strKey, "Student " & intCounter)
        'Add next columns of data as sub-items of ListItem
        With lvwItem
      'Parameters =      .Add Index,Key,Text,Report Icon,TooltipText
            .ListSubItems.Add , strKey & CStr(intCounter), CStr(5 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 1), CStr(135 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 2), CStr(40 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 3), ("Class:" & intCounter)

       End With
    Next
    'reset lvwItem object
    Set lvwItem = Nothing
End With
lvwList.Refresh

End Function

Save the Form with the name ListView Control Tutorial-01.
Demo View of the Form.
Open the Form in Normal View to have a look at our creation.
If you find your form with the following image-like display then you are on the right track.
We have to make some changes in the Listview control's Property settings. We have changed the ListView control's name to ListView1, in the Access's Property Sheet. But, ListView control has its own Property Sheet. We will use the ListView control's own property sheet to make changes to the control. Some of the Property Values are appearing on the Access Property Sheet also.
Right-Click on the ListView control and highlight the ListViewCtrl Object option from the displayed options list and select Properties from the displayed shortcut menu.
The Property sheet Image is given below:

On the Property Sheet on the top, there are Tabs with groups of other options. We are on the General tab by default. On the General tab, there are options on the left side of the control and checkboxes on the right. We will make changes on only two Properties, the ListView control on the form is in a disabled state by default, we must enable it.

The ListView control display can be changed to different modes like List items with big image Icons, with small Image Icons, in ListView, or in Report View - like it appears in the first Image above.
Enable the ListView Control by putting a checkmark in the Enabled Property, on the right side.
Select the lvwReport option from the View drop-down list on the left side.
Click on the Apply Button on the Control to save the change.
Click the OK button to close the Property Sheet.
Save the Form with the changes and then open it in normal View.
You will find the same result looks like the Image given on top of this page, except for the form background color and other form properties.

The Program's Functional Diagram.

Before going to the VBA Code it will be interesting to know how the data items are loaded into the ListView Control. The data arrangement for a ListBox control is straightforward. But, the ListView control's data loading procedure is entirely different. It is not in the logical order that we normally perceive. Once you know the flow of data from the source to a single row in the form of a diagram, or let us call it a flow chart, it will not be difficult to understand the VBA Code and what it does.

The Data Flow Diagram.

The left top corner box represents the ListView control.
As the first step of preparing the List is to create the list's heading labels or Column Headings. You can see this in the diagram, column headings in red color. This you can compare with the field headings in Table's Datasheet View. Each Column Heading is loaded into the ListView control, in the ColumnHeaders Object member. The ListView control's ColumnHeaders.Add() method is called five times for assigning each column label, one after the other, into the ListView control.
The action needed to execute the next five steps are important to note. They represent a single record with five Data Fields. But they are loaded into the ListView control in two different sets of steps, or let us say that they are being loaded into two different Object members (ListItems and ListSubItems) of the ListView control.
- The first field value (Column Value) is loaded into the ListView control's ListItems Object's Add method. If you look at the image on the top, the first record's first column value Student1 is loaded in the ListItems Object (ListView.ListItems.Add method) of the ListView control.
- From the 2nd column onwards all other column values are loaded into the ListSubItems Object of the ListItems Object, one after the other. The ListSubItems.Add Method (ListView.ListItems.Item(x).ListSubItems.Add) is called four times to insert the Values into Age, Height, Weight, and Class columns individually.
These two-level steps of action are required to load a complete row of values into the ListView Control. The diagram is drawn with two rows of data in the ListView control.

With the above picture in mind, I am sure you will not have any difficulty understanding what the above VBA Code does in the Program.

Let us go to the VBA Code Segment-wise.

At the Global declaration area of the Module, we have declared the ListView Object, the ListItem Object, the ImageList Object, and a Constant Variable with the String Value LV.

Dim lvwList As MSComctlLib.ListView
Dim lvwItem As MSComctlLib.ListItem
Dim ObjImgList As MSComctlLib.ImageList
Const prfx As String = "X"

The lvwList Variable is declared as a ListView Object, lvwItem is declared as a ListItem Object of ListView Control, and ObjImgList is declared as an ImageList Object. ImageList Object is another ActiveX control that can be loaded with Image Icons for use in the TreeView control, and ListView controls. We will keep the ImageList control aside for the time being and will take it up later. The Constant Prfx is used in ListItems.Add method's Key-Value prefix, one of the optional Parameters. The Key-value must be of String Type.

The LoadListView() Function is the main program.

Our ListView control's name on the Form is ListView1. The first statement in the program:

Set lvwList = Me.ListView1.Object

Assigns the ListView1 control on the Form into the Object variable lvwList declared in the Global declarations area.

Next, we will get prepared to load the Column Header information. First, we initialize the ColumnHeader object to ensure that it is empty. When we repeatedly run the program the control has a tendency to retain the earlier loaded values in the control. When you open and close this form more than once, after disabling the ColumnHeaders.Clear statement, you will know the difference. The same set of headings is added to the control every time and will appear in the control with empty rows below.

This you can check and confirm manually. Do the following:

Open the Demo Form once then close the form,
Open the Form in Design View.
Right-click on the ListView Control, highlight the ListViewCtrl Object Option, and select Properties from the displayed list.
Select the Tab with the Label Column Headers.
There you can find the first Column Heading Name in a Text Control and above the Text control the Index Value 1.
Point the Mouse Pointer to the right side of the Index number box, There a control will appear with arrows pointing to left, and right directions.
Click on the right arrow to display other Column labels one by one in the Text control, with the change of index numbers.
If you open and close the Form one more time the above Tab will have two sets of the same Column Heading Labels.

The ColumnHeaders.Add method syntax is as follows:

lvwList.ColumnHeaders.Add(Index, Key, Text, Width, Alignment, Icon)

All parameters are optional.

With lvwList
    .ColumnHeaders.Clear 'initialize header area
'Parameter List:
'Syntax: .ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
    .ColumnHeaders.Add , , "Name", 2500
    .ColumnHeaders.Add , , "Age", 1200
    .ColumnHeaders.Add , , "Height", 1200
    .ColumnHeaders.Add , , "weight", 1200
    .ColumnHeaders.Add , , "Class", 1200
 End With

The Index value is automatically assigned as 1, 2, and 3 as running serial numbers.

The Key value is of String data type, but not used for Column Headers, if needed it can be used.

Text Value is displayed on the control as Column Labels.

Based on the data width size required to display below the column headings we can assign an approximate width value in Pixels.

If the Text alignment value is omitted then the Left-alignment (0 - lvwAlignmentLeft) value is taken as default. It can be Right Aligned (1 - lvwAlignmentRight) or Center Aligned (2 - lvwAlignmentCenter).

After loading the column heading labels the next step is to load the first-row first Column value of the first record. Before that, we must initialize the ListItems Object with the following code segment:

'Initialize ListView Control
  While lvwList.ListItems.Count > 0
        lvwList.ListItems.Remove (1)
  Wend

The next Code block loads the record list items one row at a time and a total of ten rows of some constant values with few changes for demo purposes. This process we have put within the For...Next loop runs ten times, creating ten rows of data.

With lvwList
    For intCounter = 1 To 10
        strKey = prfx & CStr(intCounter) '
  'Syntax: .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
        Set lvwItem = .ListItems.Add(, strKey, "Student " & intCounter)
        
  'Add next columns of data as sub-items of ListItem
        With lvwItem
  ' Syntax: .ListSubItems.Add Index,Key,Text,Report Icon,TooltipText
            .ListSubItems.Add , strKey & CStr(intCounter), CStr(5 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 1), CStr(135 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 2), CStr(40 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 3), ("Class:" & intCounter)

       End With
    Next
    'reset lvwItem object
    Set lvwItem = Nothing
End With

The first statement within the For...Next loop strKey = prfx & Cstr(intcounter) prepares the unique Key value for the first ListItem (first Column).

All the parameters of ListItems.Add method is optional and the first three parameters Index, Key, and Text are assigned in the same order as Column Headers and the other two parameters are an icon and a small icon image reference.

When the row value of the first column is assigned to the ListItem (lvwList.ListItems) this object reference is saved in the lvwItem object for easily calling the next level sub-object (ListSubItems object) to avoid writing a lengthy object reference:

lvwList.ListItems.Item(index).ListSubItems.Add()

Expressed in the short form with lvwItem.ListSubItems.Add()

The ListSubItems.Add() method's first three Parameters and passing order is the same as ListItem after that comes the Icon image reference followed by the Tooltip Text parameter.

To the Key value of each column, I have added the For...Next Loop's control variable's current running value + some value to make it unique on all columns. The Key parameter value can be omitted, but it is a good idea to get used to it.

The ListSubItems.Add() method is called four times to add the second column onwards into the ListView control.

These steps are repeated nine more times to load all ten sample records into the ListView Control.

The above ListView Control Demo database is attached for instant running and learning.

In the next session of our tutorial, we will learn how to search and find value from the list view control and how to rearrange the Columns as we do in Datasheet View.

DataSavingForm.zip