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.

Download TreeViewListViewDemo.zip

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