January 2021 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

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 with preloaded 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, which links each product to a category by storing the corresponding CID value. 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 then 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:

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 (holding the product code) is linked through 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:

On the second subform, the key fields of the product record are displayed in gray text and are locked to prevent any 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 to be 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 problem.

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.