Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

  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
  5. TreeView Control Check-Mark Add Delete Nodes
  6. TreeView ImageCombo Drop-Down Access Menu
  7. Re-arrange TreeView Nodes by Drag and Drop
  8. ListView Control with MS-Access TreeView
  9. 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:

Click on the above Document image for the full text of the 2017 Update:1707 Document.  The following link suggests some helpful hints.

With the use of the above TreeView control objects, we can build better-looking and better performing User-Interfaces for our new Projects. 

Download the Demo Database.


Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code