TreeView Control with Subforms ~ LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, January 19, 2021

TreeView Control with Subforms

Introduction.

In this Session of the TreeView Control Tutorial, we will use two Subforms along with the TreeView control on the main form frmTreeViewtab. We have imported the ImageList control with the preloaded Images from our earlier demo Project.
We will use the same lvCategory and lvProducts Tables we have used for our earlier Projects.
The lvCategory Table's Primary Key field (CID) and Description field (Category) values are the TreeView Node's Add() method Key and Text parameter values, respectively.

The lvProducts Table has detailed information on each product like product code, description stock-unit-quantity, and list price.

Besides that, the lvProducts table has the ParentID field that is updated with the Category code (CID) value. All the product items that belong to a particular category are updated with the (Category ID) CID field value on the ParentID field. This way both the Tables have the master-child relationship.

The Product records have two subforms on the Tab Control Pages. The first Page has the data View subform and the second Page has the Edit subform. The first tab control Page displays all the Product records that belong to a particular category of the TreeView Control. The first subform records are displayed for viewing and for selecting a particular record for editing on the second subform.

The current record or user-selected record on the first sub-form is available for editing on the second sub-form, on the second page of the Tab control. The Key fields highlighted with gray color are locked and cannot be edited.

TreeView with Subforms Design View.

The Design View of the form frmTreeViewTab is given below:

The first two unbound text boxes on the main form are updated when the user selects a Category item from the TreeView control.

The third unbound textbox (name p_ID) is initially updated with the first product record’s unique ID (PID) value otherwise the user-selected record’s value gets updated. The selected product record on the first subform is available on the second subform for modifications.

Links to Earlier Tutorial Sessions.

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

The CatId unbound textbox is the [Link Master Fields] Property value of the first subform.

The Product Code in the third unbound textbox (p_ID) is linked to the [Link Master Fields] property value of the second subform on the View Tab Page.

The p_ID unbound textbox value gets updated when the first subform contents are refreshed or when an item is selected by the user.

Normal View of the Screen.

The normal view of the frmTreeViewTab form is given below:

The Key fields on the Product record on the second subform, with gray forecolor, are locked and not allowed to modify the contents.

**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 TreeView Control and ImageList Control usage and their functioning were all explained in detail in the earlier sessions, you will find only a few of those earlier VBA subroutines appear in the above form Module.

We have designed several Screens with MS-Access TreeView, ListView, ImageList, and ImageCombo Control so far and I hope you will find them as a good reference point for your own Project Interface design.

MS-Office Version Issues for TreeView Control.

If you had any issues in running the Demo Database in your version of Microsoft Access then you may refer to the following link for some corrective actions, which may be helpful to solve your issue:

Earlier, the above Controls were not running under 64 Bit Systems. But, in September 2017 Microsoft brought out an update of MSCOMCTL.OCX Library and the following extract of the Microsoft Document is given below for your information: