Introduction.
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:
- Microsoft TreeView Control Tutorial
- Creating Access Menu with TreeView Control
- Assigning Images to TreeView Control
- Assigning Images to TreeView Control-2
- TreeView Control Check-Mark Add Delete Nodes
- TreeView ImageCombo Drop-Down Access Menu
- Re-arrange TreeView Nodes by Drag and Drop
- ListView Control with MS-Access TreeView
- ListView Control Drag Drop Events
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:
With the use of the above TreeView control objects, we can build better-looking and better performing User-Interfaces for our new Projects.
Download TreeViewSubforms.zip
