October 2020 ~ LEARN MS-ACCESS TIPS AND TRICKS

Tree View Control Check-Mark Add Delete Nodes

Introduction.

In this Episode of the Tree View Control Tutorial, we will learn how to Add or Delete Nodes. The position of the Add/Delete candidate item will be check marked, where we want to Add() the new Node or to Remove() the marked Node. The addition of the Node can be on the same level as the marked Node or as a Child Node.

The TreeView Control Tutorial Sessions so far.

The Normal View of the Demo Tree View Control on MS-Access Form, with other controls, is given below.

The Data Table for TreeView Control’s Demo.

The Table with the name Sample, which we have used in the first Tutorial Session, we will put to use here too. It is a small table with records of Access Database, table, Form and Report control's structure, arranged in hierarchical order, and easy to understand.

The ID column (Tree View Key) is an AutoNumber field.

New Visitors: Please get updated for this Session.

If you have not gone through the earlier episodes, then you may download the Demo Database from the second Session-Page: Creating Access Menu with Tree View Control. There is a Table in that Database with the name: Sample and Form frmSample. You may Import them to your current database. There is another form with the name frmMenu in there and you may Copy the Command Buttons Expand All, Collapse All, and their Command Button Click Event Procedures from frmMenu to the frmSample Code Module for continuing with this session.

As I have mentioned earlier, we can share the ImageList Control, with the uploaded Images to other Projects, we will make a copy of the ImageList Control of the last Tutorial Session and bring it here, with all the uploaded images, and use it on the Tree View Control Nodes on frmSample.

Importing ImageList Control with Images.

You can bring in the ImageList Control you already have, with uploaded Images in another database, in the following ways:

Import the Form with the ImageList Control into the active database, from the Database where you have the ImageList control with manually uploaded Images. Copy and Paste the ImageList Control to the Form, where you want it.
Or, Copy the ImageList Control into the Clipboard, from the Database, where you have it, first. If you have downloaded the Demo Database from the earlier Tutorial Session then you already have the ImageList Control with uploaded images. Close the Database after copying the ImageList control to the Clipboard.
Open the target database and open the Form where you want the ImageList Control and Paste it on the Form.
With the following code lines in the CreateTreeView() Sub-Routine you can pass the ImageList Object Reference to the Tree View Control.
```
Dim tv As MSComctlLib.TreeView
Dim ImgList As MSComctlLib.ImageList

Set tv = Me.TreeView0.Object
tv.Nodes.Clear

Set ImgList = Me.ImageList0.Object
tv.ImageList = ImgList 
```

Change the highlighted Object Names in the Code, if they are different on the Form.

After that, you can add the Image Key Names to the TreeView Nodes.Add() method’s last two parameters.

These exercises we have already done in the earlier sessions. To remind you we are using the Table and Tree View Control we created in the first Tutorial Session and implemented with the above-explained changes.

Displaying the Check-Marked Nodes Property Values.

The selected Node Key, Parent Key, and Text Properties are displayed in the TextBoxes on the right side of the Tree View Control. The Check-Box with the Caption: Child Node, the Delete Node, and Add Node Command Buttons are new additions to the Form. Their functions will be explained in a short while.

Displaying CheckBoxes on the TreeView Control.

Normally, the Check-Box is not displayed in the TreeView Control, unless we enable a Property in the Tree View Control’s Property Sheet. It has only some limited functionality here. Whatever we are going to do here can be done without the checkbox too.

A better approach for the usage of CheckBoxes can be, as the preparation of a Report on selected branch locations of the Company’s Businesses, based on user-selectable random choices of branches, from the Access Project Menu.

Here, the aim is to give an awareness of this feature and run a demo on its usage.

Open the Form with the TreeView Control in Design View.
Right-Click on the TreeView Control, highlight the TreeCtrl Object and select the Properties option to display the Property Sheet.
Put Check-Mark on the Check Box option on the Properties Control as shown in the Image given below.

The Demo TreeView Image is given at the top, Details.

Let us see what we have on the Demo Form frmSample presented at the top of this page. The Tree View Control and the top two Command Buttons, Expand All and Collapse All, on the Click Expands or Collapses the Nodes and we have seen their functions in the last episode.

On the Right-side there are three TextBoxes, below the heading Label: Property Values, for displaying the check-marked Node’s Key, ParentKey, and Text Values.

The Node Delete Command Button removes the check-marked Node or the Node and its Child Nodes.

Before Selecting the Add Node Command Button, the Text Property Value must be edited to replace it with the new Text Value for the new Node.

Above the Add Node Command Button, there is a Check Box with the label Child Node. The Add Node and Child Node Check-Box work together to set the Rule, as to where the new Node should appear.

Understanding the Add Node Action.

Assume that you want to add a new Node for Hyperlink field under the Fields group (or Parent Node) data type List. Look at the Demo Image given on top of this page, where check marked the Date Field Node, among other Child-Nodes. The Right side Property sheets show its Key: X15, ParentKey: X4 & Text: Date Field Description.

Change the Text: Date Field to Hyperlink on the Property display Text Box and Click on Add Node Command Button. The output will be as shown below:

If you Check-Mark the Field's parent Node item and put check-mark in the Child-Node option, above the Add Node Command Button, you will get the same result.

Instead, if you keep the Node check-mark on the Date Field and set the check-mark on the Child-Node option above the Add Node Command Button, you will get the result as shown below.

The Child Node will be created under the Check-Marked Node.

The Add Node actions need to create a new record in the underlying table first.

A new record is created in the Sample Table, with the new Text: HyperLink and ParentID Values. The AutoNumber Field generates a new Number and we retrieve it and use it as Key-Value for the Node.

The Add Node sub-Routine VBA Code is given below:

Private Sub cmdAdd_Click()
Dim strKey As String
Dim lngKey As Long
Dim strParentKey As String
Dim lngParentkey As Long
Dim strText As String
Dim lngID As Long
Dim strIDKey As String

Dim childflag As Integer
Dim db As DAO.Database
Dim strSql As String
Dim intflag As Integer
Dim tmpnode As MSComctlLib.Node

Dim i As Integer
i = 0
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
       tmpnode.Selected = True
        i = i + 1
    End If
Next
If i > 1 Then
      MsgBox "Selected Nodes: " & i & vbCr & "Select only One Node to mark Addition.", vbCritical, "cmdAdd()"
    Exit Sub
End If

'Read Property Values from Form
strKey = Trim(Me![TxtKey])
lngKey = Val(Mid(strKey, 2))

strParentKey = Trim(Me![TxtParent])
lngParentkey = IIf(Len(strParentKey) > 0, Val(Mid(strParentKey, 2)), 0)

strText = Trim(Me![Text])

'Read child Node Option setting
childflag = Nz(Me.ChkChild.Value, 0)

intflag = 0

strSql = "INSERT INTO Sample ([Desc], [ParentID] ) "
If lngParentkey = 0 And childflag = 0 Then
    'Add Root-level Node, ParentKey is Blank
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & " "
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 1
ElseIf (lngParentkey >= 0) And (childflag = True) Then

    'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngKey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 2
ElseIf (lngParentkey >= 0) And (childflag = False) Then
    'Inserts Node at the check-marked level, Add item under the same ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngParentkey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 3
End If

Set db = CurrentDb
db.Execute strSql

'Get newly created autonumber to use as Key
lngID = DMax("ID", "Sample")
strIDKey = KeyPrfx & CStr(lngID)

On Error GoTo IdxOutofBound

Select Case intflag
    Case 1
        'Add Root-level Node, ParentKey is Blank
        tv.Nodes.Add , , strIDKey, strText, "folder_close", "folder_open"
    Case 2
        'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
        tv.Nodes.Add strKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
    Case 3
        'Inserts Node at the check-marked level, Add item under the same ParentKey
        tv.Nodes.Add strParentKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
End Select
tv.Refresh

    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With

Set db = Nothing
cmdExpand_Click
 
cmdAdd_Click_Exit:
Exit Sub

IdxOutofBound:
    CreateTreeView
Resume cmdAdd_Click_Exit
End Sub

Let us examine the VBA Code. After the local Variable Declarations, the TreeView Nodes are scanned, for checkmarks and take a Count of check-marked items. If the check-marked Nodes are more than one, then it shows a message and aborts the Program.

Note: Instead of Check-Marking we can directly Click on a Node to select it. In both cases, the Checked/Clicked Node is passed as a parameter to the Event Procedure,. Check-marking is good when you need to select more than one item from a Project Menu, for example: for the selection of a different set of Data for a particular report, etc.

The checked Node’s Property Values are read from the Form controls into strKey, strParentKey, and strText. The ID, ParentID Numeric Values are extracted and saved in lngKey and lngParentKey Variables for use in SQL String.

Next, the Child-Node Check-Box value is saved in ChildFlag Variable.

Three different sets of SQL Strings are created based on the selected Node and Child-Node Check Box option above the Add Node command Button.

If ParentID Property Value on the Form is empty and the Child Node checkbox is not checked, then a Root-Level new Record will be created because the user Check-Marked a Root-level Node.
If ParentID Property Value >= 0 and Child Node check-box is selected (checked) then a new record is created as a Child Node to the Check-Marked Node. The check-marked Node’s Key (ID) is used as ParentID, on the new Record for the new Node.
If ParentID Value >= 0 and the Child Node check-box option is not selected (not checked) then the New Record is created for the new Node, at the same level as the check-marked Node.

The intFlag Variable is assigned one of the three values: 1,2 or 3, depending on the execution of SQL, as an indication of the type of Node to create in the Tree View Control.

Next, based on the selection of options the SQL is executed to create a New Record on the Sample Table, with a new AutoNumber ID Field Value.

Next, the DMax() Function returns the Unique Record ID as Key-Value for the new Node.

Based on the Node type option (1,2 or 3) the Node is created in the Tree View Control.

The Property display Text Box contents are cleared.

Deleting Node or Node with Children.

The Delete Node Option is much easier than the earlier exercise. Simply Deletes the Check-Marked Node and its Children, if present, from the Tree View Control. The related records are Deleted from the Table.

The VBA Code for Node Removal is given below:

Private Sub cmdDelete_Click()
Dim nodId As Long, nodParent As Long
Dim strSql As String
Dim db As DAO.Database
Dim j As Integer
Dim tmpnode As MSComctlLib.Node
Dim strKey As String
Dim strMsg As String

j = 0 ' Get check-marked Nodes count
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
        tmpnode.Selected = True
        strKey = tmpnode.Key
        j = j + 1
    End If
Next

   If j > 1 Then
      MsgBox "Selected Nodes: " & j & vbCr & "Select Only One Node to Delete.", vbCritical, "cmdDelete()"
      Exit Sub
   End If

Set tmpnode = tv.Nodes.Item(strKey)
tmpnode.Selected = True
Set db = CurrentDb

'check the presense of Child Node(s) of marked Node
If tmpnode.Children > 0 Then
'Warnings:
'       Deleting Nodes at Random will leave orphaned Nodes
'       in the Table and end up with errors, during next Tree View loading process
    strMsg = "The Marked Node have " & tmpnode.Children & " Children. " & vbCr & "Delete the Child Nodes also?"
    If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
       'Double check and get confirmation.
       strMsg = "Delete Only the deepest set of Child Nodes" & vbCr
       strMsg = strMsg & "and their Parent Node at one time." & vbCr & vbCr
       strMsg = strMsg & "Are you sure to Proceed..?"
       If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
            Do Until tmpnode.Children = 0
                nodId = Val(Mid(tmpnode.Child.Key, 2))
        'Delete Child Node
                tv.Nodes.Remove tmpnode.Child.Index
        'Delete the related record
                strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
                db.Execute strSql
            Loop
        Else
            Exit Sub
        End If
    Else
        Exit Sub
    End If
End If

        nodId = Val(Mid(tmpnode.Key, 2))
    'Delete Parent
       tv.Nodes.Remove tmpnode.Key
       tv.Refresh
    'Delete Marked Record
        strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
        db.Execute strSql
       
      
    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With
    Set db = Nothing
    
End Sub

After the local Variable Declarations the For Each . . . Next Loop takes a count of Nodes with checkmarks. If there is more than one check-marked item found, then a message is displayed and the program is aborted.

If there is only one item check-marked then the second step Validation check looks for the presence of Child Node(s) of the selected Node. If child nodes are found, then a message is displayed to that effect. The User needs to reconfirm his intention to proceed to delete the child Nodes first and then the check-marked parent Node.

Note: Users are advised to delete the deepest level of Child Node(s) first, or all the deepest level child Nodes with their immediate parent Node, by marking the parent Node only, not marking the grandparent Node. Limiting the deletion rule at this level will keep the Code simple and easy to understand. Violating this rule may leave some Nodes orphaned and end up with errors when the Tree View opens next time.

The Child Nodes are removed one by one and the corresponding records in the table are also deleted, one after the other. Then deletes the marked Parent Record.

If the marked Node has no Child Node(s) then it is deleted immediately after the validation checks and the corresponding table record is also deleted.

The Property display Text Box contents of the form are cleared.

The Form frmSample’s Complete Class Module VBA Code.

Following is the complete VBA Code in frmSample's Class Module, with other small sub-routines for expanding collapsing Nodes, TreeView0_NodeCheck Event Procedure, cmdExit Command Button Click Event, Form_Load() Procedures, and CreateTreeView() Subroutine:

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim ImgList As MSComctlLib.ImageList
Const KeyPrfx As String = "X"

Private Sub cmdAdd_Click()
Dim strKey As String
Dim lngKey As Long
Dim strParentKey As String
Dim lngParentkey As Long
Dim strText As String
Dim lngID As Long
Dim strIDKey As String

Dim childflag As Integer
Dim db As DAO.Database
Dim strSql As String
Dim intflag As Integer
Dim tmpnode As MSComctlLib.Node

Dim i As Integer
i = 0
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
       tmpnode.Selected = True
        i = i + 1
    End If
Next
If i > 1 Then
      MsgBox "Selected Nodes: " & i & vbCr & "Select only One Node to mark Addition.", vbCritical, "cmdAdd()"
    Exit Sub
End If

'Read Property Values from Form
strKey = Trim(Me![TxtKey])
lngKey = Val(Mid(strKey, 2))

strParentKey = Trim(Me![TxtParent])
lngParentkey = IIf(Len(strParentKey) > 0, Val(Mid(strParentKey, 2)), 0)

strText = Trim(Me![Text])

'Read child Node Option setting
childflag = Nz(Me.ChkChild.Value, 0)

intflag = 0

strSql = "INSERT INTO Sample ([Desc], [ParentID] ) "
If lngParentkey = 0 And childflag = 0 Then
    'Add Root-level Node, ParentKey is Blank
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & " "
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 1
ElseIf (lngParentkey >= 0) And (childflag = True) Then

    'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngKey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 2
ElseIf (lngParentkey >= 0) And (childflag = False) Then
    'Inserts Node at the check-marked level, Add item under the same ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngParentkey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 3
End If

Set db = CurrentDb
db.Execute strSql

'Get newly created autonumber to use as Key
lngID = DMax("ID", "Sample")
strIDKey = KeyPrfx & CStr(lngID)

On Error GoTo IdxOutofBound

Select Case intflag
    Case 1
        'Add Root-level Node, ParentKey is Blank
        tv.Nodes.Add , , strIDKey, strText, "folder_close", "folder_open"
    Case 2
        'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
        tv.Nodes.Add strKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
    Case 3
        'Inserts Node at the check-marked level, Add item under the same ParentKey
        tv.Nodes.Add strParentKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
End Select
tv.Refresh

    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With

Set db = Nothing
cmdExpand_Click
 
cmdAdd_Click_Exit:
Exit Sub

IdxOutofBound:
    CreateTreeView
Resume cmdAdd_Click_Exit
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub cmdDelete_Click()
Dim nodId As Long, nodParent As Long
Dim strSql As String
Dim db As DAO.Database
Dim j As Integer
Dim tmpnode As MSComctlLib.Node
Dim strKey As String
Dim strMsg As String

j = 0 ' Get check-marked Nodes count
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
        tmpnode.Selected = True
        strKey = tmpnode.Key
        j = j + 1
    End If
Next

   If j > 1 Then
      MsgBox "Selected Nodes: " & j & vbCr & "Select Only One Node to Delete.", vbCritical, "cmdDelete()"
      Exit Sub
   End If

Set tmpnode = tv.Nodes.Item(strKey)
tmpnode.Selected = True
Set db = CurrentDb

'check the presense of Child Node(s) of marked Node
If tmpnode.Children > 0 Then
'Warnings:
'       Deleting Nodes at Random will leave orphaned Nodes
'       in the Table and end up with errors, during next Tree View loading process
    strMsg = "The Marked Node have " & tmpnode.Children & " Children. " & vbCr & "Delete the Child Nodes also?"
    If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
       'Double check and get confirmation.
       strMsg = "Delete Only the deepest set of Child Nodes" & vbCr
       strMsg = strMsg & "and their Parent Node at one time." & vbCr & vbCr
       strMsg = strMsg & "Are you sure to Proceed..?"
       If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
            Do Until tmpnode.Children = 0
                nodId = Val(Mid(tmpnode.Child.Key, 2))
        'Delete Child Node
                tv.Nodes.Remove tmpnode.Child.Index
        'Delete the related record
                strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
                db.Execute strSql
            Loop
        Else
            Exit Sub
        End If
    Else
        Exit Sub
    End If
End If

        nodId = Val(Mid(tmpnode.Key, 2))
    'Delete Parent
       tv.Nodes.Remove tmpnode.Key
       tv.Refresh
    'Delete Marked Record
        strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
        db.Execute strSql
       
      
    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With
    Set db = Nothing
    
End Sub

Private Sub cmdExpand_Click()
Dim nodExp As MSComctlLib.Node

        For Each nodExp In tv.Nodes
            nodExp.Expanded = True
        Next

End Sub

Private Sub cmdCollapse_Click()
Dim nodExp As MSComctlLib.Node

        For Each nodExp In tv.Nodes
            nodExp.Expanded = False
        Next
End Sub

Private Sub Form_Load()
    CreateTreeView
    cmdExpand_Click
End Sub

Private Sub CreateTreeView()
Dim db As Database
Dim rst As Recordset
Dim nodKey As String
Dim ParentKey As String
Dim strText As String
Dim strSql As String

Set tv = Me.TreeView0.Object
tv.Nodes.Clear

'Pass ImageList control reference to TreeView's ImageList Property.
Set ImgList = Me.ImageList0.Object
tv.ImageList = ImgList

strSql = "SELECT ID, Desc, ParentID FROM Sample;"

Set db = CurrentDb
Set rst = db.OpenRecordset("sample", dbOpenTable)
Do While Not rst.EOF And Not rst.BOF
    If Nz(rst!ParentID, "") = "" Then
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        tv.Nodes.Add , , nodKey, strText, "folder_close", "folder_open"
    Else
        ParentKey = KeyPrfx & CStr(rst!ParentID)
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        tv.Nodes.Add ParentKey, tvwChild, nodKey, strText, "left_arrow", "right_arrow"
    End If
rst.MoveNext
Loop

rst.Close
On Error GoTo 0
Set rst = Nothing
Set db = Nothing

End Sub

Private Sub TreeView0_NodeCheck(ByVal Node As Object)
Dim xnode As MSComctlLib.Node

Set xnode = Node
  If xnode.Checked Then
    xnode.Selected = True

    With Me
        .TxtKey = xnode.Key
      If xnode.Text = xnode.FullPath Then
        .TxtParent = ""
      Else
        .TxtParent = xnode.Parent.Key
      End If
        .Text = xnode.Text
    End With
  Else
    xnode.Selected = False
    With Me
      .TxtKey = ""
      .TxtParent = ""
      .Text = ""
    End With
End If
End Sub

The Design View of the frmSample Form is given below:

Your Observations, comments, and suggestions are welcome.

The Demo Database is attached for Download.

Download AddDelete.zip

DICTIONARY OBJECT

Assigning Images to Tree View Nodes-2

a.p.r. pillaiOctober 09, 2020ImageList Control, Key, ListView Control, TreeView Control No comments

Introduction.

I hope, you found the last few week's Tutorial Sessions, of Microsoft TreeView and ImageList Control interesting and, are ready to take on the next episode. If you have not yet gone through the earlier Articles then the Links are given below.

This post is the continuation of last week’s episode.

Last week we created a few images and uploaded them to the ImageList Control using VBA Code, for Microsoft Tree View Control. The ImageList control reference has been passed to the Tree View Control’s ImageList Property. After these steps, we could directly use the Image’s Key-Names or Index Numbers as Node [Image] and [SelectedImage] Parameters in the Nodes.Add() Method of Microsoft Tree View Object. With the aid of both these Controls, we could create a beautiful Microsoft Access Project Menu, having menu items in a hierarchical structure with Tree-lines and visually pleasing menu item Images.

Last Week’s Trial Run Form with Node Images.

Last week’s Access Project Menu image is given below, with Node Images and Tree-lines.

VBA Code for Uploading Images to ImageList Control.

The Following is the VBA Code, that we have used to upload images to the ImageList Object Nodes:

Dim tvw As MSComctlLib.TreeView
Const KeyPrfx As String = "X"
Dim objimgList As MSComctlLib.ImageList


Private Sub CreateImageList()
Dim strPath As String

'Initialize Tree View Object
Set tvw = Me.TreeView0.Object
'Clear the Tree View Nodes, if any.
tvw.Nodes.Clear

'Initialize ImageList Object
Set objimgList = Me.ImageList0.Object
’the images must be saved in database path
strPath = CurrentProject.Path & “\”

With objimgList.ListImages
'Key Names are Case sensitive.
    .Add , "FolderClose", LoadPicture(strPath & "folderclose2.jpg")
    .Add , "FolderOpen", LoadPicture(strPath & "folderopen2.jpg")
    .Add , "ArrowHead", LoadPicture(strPath & "arrowhead.bmp")
    .Add , "LeftArrow", LoadPicture(strPath & "LeftArrow.bmp")
    .Add , "RightArrow", LoadPicture(strPath & "RightArrow2.bmp")
End With

With tvw
    .ImageList = objimgList
End With

End Sub

While presenting the above Code I have mentioned that there is an easier way to upload the images to the ImageList Control, without VBA Code. Besides that, I have promised to share the Images, which I have used in the above demo Access Menu Nodes. We will come to that in a short while.

We were not simply creating some images and using them. But, we have created some meaningful images for our Tree View-based Access Project Menu.

Take the case of the root-level folder images. The FolderClose image style is used for the Root-level Node’s normal view while hiding all its Child Nodes from view. When the user clicks on the Root-level Node the FolderOpen image appears and exposes its child nodes. A second click on the Node will not change the Image to normal view, while it has the focus, but the child nodes may disappear from view, depending on the TreeView0_NodeClick() Event Procedure Code.

Similarly, the child nodes have the left-facing LeftArrow image in normal view and a slightly bigger image RightArrow, pointing to the right, when Clicked. The highlighted words are Key Names used in the ImageList control. The Click action opens the Form, Report, or Macro, depending on the child Node selected.

The ImageList control on the Form.

The ImageList Control on the frmMenu Form highlighted in the Design View is given below.

The ImageList Control Property Sheet.

The ImageList’s Property Sheet Image is given below for reference:

Review of Last Week’s Exercise and Preparations.

Last week we selected the Image-size 16 x 16 pixels and uploaded the required Images into the above ImageList Control using VBA Code. After uploading all the images, we passed the ImageList Object reference to the Tree View Control’s ImageList Property.

After the above steps, we could use the Index Number or Key-Value of Images in the Add() method parameters, of Tree View Nodes.

We have not specified the first parameter of ImageList’s Add() method. But, the Add() Method itself inserts the Index Number for each image, into the ImageList Control. Remember, when you enter the Key value into the Nodes.Add() Method’s [Image], [SelectedImage] Parameter it is Case Sensitive. It is better, when you enter Key values, into the ImageList Control’s Key Text Box, with small-case letters.

Now, as I have promised to show the easy way of loading Images to the ImageList Control. It is as simple as selecting all the required Images, one by one, from your disk manually and adding them to the ImageList Control, without any VBA Code.

Besides that, you can share the ImageList Control with other Projects, by simply Copying and Pasting the ImageList Control to another Project Form, with all the images intact. Then all you have to do is to use the Key-Value or Index Number in the Add() method of TreeView Control Nodes.

If you have already downloaded the Demo Database from last Week’s Post then open ProjectMenuV21.accdb. We have saved a copy of the Form frmMenu with the new Name frmMenu2.

Adding ImageList Control on frmMenu2 Form.

Open the form frmMenu2 in Design View.
Insert Microsoft ImageList Control from the Activex Controls List, somewhere on the empty space on the frmMenu2 Form.
Change its Name Property value to ImageList0.
Right-Click on the Image List Control, highlight ImageListCtrl Object Option, on the displayed menu, and select Properties.
Select the preset Image Size 16 x 16 Pixels Option, on the Properties General Tab. It is important that you select one of these options on the General Tab first, before adding any Image to the Images Tab.
Select the Images Tab. The Properties Images Tab looks like the following Image:
Click on the Insert Picture Command Button, find the folder_closed sample image (if you have one or select the image you prepared earlier), you created for a trial run last week, select it and click on Open Command Button.
The ImageList control will look like the image given below, after inserting the selected image.
Three Text Box controls: Index, Key, and Tag are now enabled. The Index control has the Index Value 1 inserted into it automatically.
Enter the Text folder_close or whatever Key-value you prefer to use in the Add() Method’s parameters of Tree View control, in the Key Text Box control. Its data Type should be of String Type and unique among all the image Key Values.
The Tag property can be used to record some useful information, like the Path Name of the Image.
Add all the required Images from your disk, one after the other, and enter appropriate Key Values in the Key Text control, for all Images you upload. Use simple, meaningful, and easy to memorize Key values.
If you would like to remove some image from the control then select that Image and click Remove Picture.
When finished loading Images click on Apply Command Button and then Click OK to close the ImageList Control.
Note: Remember, you have added all the Images, after selecting the Image Size 16 x 16 pixels on the General Tab. After uploading images you cannot change the Image Size on the General Tab. If you would prefer a different Image Size option, after uploading images then you must remove all the Images first. Then only you will be able to select a different Image size option and then repeat the upload process again.
The ImageList Control with more Images:
In case you are not sure what Key-Value you have entered for a particular Image then Click on the Image to display the Key-Value in the Key Text Box.
After uploading all the required images into the control they remain within the ImageList Control. If you need the same Images in some other Project you can make a copy of the ImageList Control anywhere you want or share the Form/Database with others, with the images. You can add more images this way from a new location.
After uploading all Images we need to pass the ImageList control Reference to the Tree view Control’s ImageList Property like earlier we did after uploading Images through VBA Code.

The following sample code will pass the ImageList’s Reference to the Tree View Control’s ImageList Property in new Projects.

Dim objimgList As MSComctlLib.ImageList

'Initialize ImageList Object
Set objimgList = Me.ImageList0.Object

With tvw
    .ImageList = objimgList
End With

Expanding/Collapsing Nodes with One Command Button

Open frmMenu2 Form in Design View.
Select the Collapse All Command Button, open its Click Event Procedure, and remove the Code.
Remove that Command Button itself from the Form.
Select Expand All Command Button, and open its Click Event Procedure.
Copy the following VBA Code and Paste it over-writing the existing lines, between cmdExpand_Click() . . . End Sub lines as shown below:

Private Sub cmdExpand_Click()
Dim Nodexp As MSComctlLib.Node

If cmdExpand.Caption = "Expand All" Then
    cmdExpand.Caption = "Collapse All"
    
    For Each Nodexp In tvw.Nodes
        Nodexp.Expanded = True
    Next Nodexp
Else
    cmdExpand.Caption = "Expand All"
    
    For Each Nodexp In tvw.Nodes
        Nodexp.Expanded = False
    Next Nodexp
End If

End Sub

Save and Open frmMenu2 in normal view.
The Command Button Caption is Expand All now.
Click on the Command Button to expand all the Nodes. All the Nodes are in the expanded form now. The Caption of the Command Button changes to Collapse All.
Click on it again and all the Nodes are in a collapsed state, the Caption Text changes back to Expand All again.

Next week we will see the usage of CheckBoxes on Nodes to learn how we can identify checked Nodes and work with them.

Download ProjectMenuV22.zip

CLASS MODULE

Assigning Images to Tree View Nodes

a.p.r. pillaiOctober 01, 2020ImageList, Nodes, TreeView Control 3 comments

Introduction.

Last week, we have created the Access Project Menu to TreeView Control and I hope you were able to create it on your own and run it, in your version of Microsoft Access. There is a Demo Database, created under Access 2007 and attached to the following article for download. The Link to that Article is given below:

Creating Access Menu with Tree View Control

You may download the database so that you can add the new VBA Code, that pertains to the above topic, and try it out in the same Database.

This is the continuation of the earlier Article and we need the same Demo Access Menu Project to assign Images to Nodes.

MS-Office / Windows 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:

SOLVED MSCOMCTL.OCX DOWNLOAD, REGISTER IN 64-BIT WINDOWS

Sample Demo Image.

When we complete our Access Project Menu, Images on Nodes will look like the Image given below:

Optionally, we can assign two Images on each Node. One Image is displayed in a normal state and a different one is displayed when the Node receives a Click.

Here, we have assigned the Root-level Node with the Closed-Folder Image for normal view and the Open-Folder-like Image will appear when the Node receives Click.

Similarly, the Child-Nodes have an Arrowhead Image, facing to the left side, in normal view, and another Arrowhead Image pointing to the right is displayed, when the Node is selected.

You can use the same Image for both (normal and for Click Event) so that the same image stays without any change in both instances. If you use either one of these two parameters, say use the Normal View parameter only and omit the second one, then the Node click will not display any image.

Ideal Image Sizes for Nodes.

The Image format can be of any common image type, like bmp, jpg, jpeg, ico, tiff etc. You can find plenty of Icon images by searching on Google. The Ideal Image size, which looks good on the Node, is 16 x 16 pixels. The ImageList Control has preset Image-size values like 16 x 16, 32 x 32, 48 x 48 pixels, and Custom Size Options to choose from.

Higher Image Size Options 32 x 32 or 48 x 48 Pixels will display larger images and occupies more space on the Tree View Display.

Node Graphics with Different Image Sizes.

The following sample image below shows a 32 x 32 Pixels size Icon:

TreeView Control with Node Image Size 48 x 48 Pixels:

If you prefer to use Custom Image Option then the actual image size provided will be displayed without change.

Image Quality and Size Considerations.

We have used Image Size 16 x 16 in the first sample Image above. If we upload a Custom Image size, larger than 48 x 48, like 512 x 512 Pixels or more, and use the Option 16 x 16 it reduces the size to the resolution specified but the clarity of the image will be reduced or distorted.

The best approach is to find small images with better quality, that can fit into the 16 x 16 pixels resolution (canvas size). It works with both 16 x 16 pixels or custom settings, without degrading the quality of the image.

You may experiment with different image types, sizes, and quality, and do trial runs before finalizing. You may use MS-Paint, or whatever Image editing programs you have and create/Import and edit images to your liking.

Before proceeding further create four or more small images and save them in the database folder itself. Upload them into the ImageList control and try them on the Tree View Control, by changing the Nodes Add() method’s last two parameters.

You may Download the Demo Database: ProjectMenu.accdb, from the earlier Article Page.

Prepare for the Trial Run.

Open the ProjectMenu.accdb database.
Make a copy of the Form frmMenu and name it as frmMenu2 and keep it for later use.
Open frmMenu in Design View.
Select ActiveX Controls Option, from the Controls Button Group, find the file Microsoft ImageList Control and click OK to insert an ImageList control, drag and place it anywhere in the empty area on the Form.
Form with ImageList Control highlighted in Design View is given below for reference:
Display its Property Sheet and change the Name Property value to ImageList0.
Right-Click on the ImageList Control and highlight the ImageListCtrl Object Option in the displayed Menu and Select Properties to display the Control’s Image settings Property Sheet.
Select the 16 x 16 image size Radio Button on the General Tab, indicating that we need the smallest of the three image sizes for the Node. The setting here takes effect on all Images we add to the ImageList Control.
Click Apply Command Button and then the OK button to close the Property Sheet.

First, we must add the required images to the ImageList Control before we can use them in Tree View Control.

Image Loading Approaches.

There is an easy way and a hard way to Add Images to the ImageList Control. The easy way works without VBA Code and the other method needs VBA. We will go by the hard way first with VBA and then try the easy way, so you will know the difference between when to use code and when without Code. A VBA-based method is good for experimenting, with different image sizes before finalizing what looks good on the Node.

We will use the ImageList Object’s Add() method to add images to the Control like we did for Tree View data for Node. This way we add several images to the ImageList control and use them at run-time.

The Syntax of Add() Method of ImageList control is as given below:

ObjImgList.ListImages.Add([Index],[Key],[Picture]) As ListImage

The first two parameters are optional. The third argument uses the LoadPicture() Function to open images from the specified location and add it to the list. The function parameter is the Image File Path Name. All the image files are added, one after the other to the ImageList Object, in the Order they are placed. The Index values are automatically generated, in consecutive numbers starting from 1 (one) onwards.

After adding all the images to the ImageList Object, the Object reference must be passed over to the Tree View Control’s ImageList Property.

The VBA Code.

The sample VBA Code for loading images for our Menu above is given below:

Dim tvw As MSComctlLib.TreeView
Const KeyPrfx As String = "X"
Dim objimgList As MSComctlLib.ImageList

Private Sub CreateImageList()
Dim strPath As String

'TreeView Object reference set in tvw
Set tvw = Me.TreeView0.Object
'Clear the Tree View Nodes, if any.
tvw.Nodes.Clear

'ImageList Object reference set in objimglist
Set objimgList = Me.ImageList0.Object

strPath = CurrentProject.Path & "\"

With objimgList.ListImages
'Key Names are Case sensitive.
    .Add , "FolderClose", LoadPicture(strPath & "folderclose2.jpg")
    .Add , "FolderOpen", LoadPicture(strPath & "folderopen2.jpg")
    .Add , "ArrowHead", LoadPicture(strPath & "arrowhead.bmp")
    .Add , "LeftArrow", LoadPicture(strPath & "LeftArrow.bmp")
    .Add , "RightArrow", LoadPicture(strPath & "RightArrow2.bmp")
End With

With tvw
    .ImageList = objimgList
End With

End Sub

Once we are through with this procedure it is easy to add the images to the Tree View Nodes.

The TreeView Nodes' Add() Method and Image Parameters.

The Tree View Object Add() Method’s last two parameters are for the Node Images. Let us look at the TreeView Object Node's Add() method Syntax one more time:

tvw.Nodes.Add([Relative],[Relationship],[Key],[Text],[Image],[SelectedImage]) As Node

The last two parameters are for Node Images. The first Image parameter is displayed for the Node’s normal view and the second image displays when the Node is selected. The [Image] and [SelectedImage] values can be either the ImageList Index Number or the Key-Value.

The CreateImageList sub-routine adds five images to the ImageList Control. Out of the first two Images, the first one (FolderClose) is for Root-level Node’s Normal View and the second one (FolderOpen) image is displayed when the root-level Node is selected.

The last two images are for the Child Node's normal view and for the Click Event action respectively.

The ArrowHead image is ignored.

The Form_Load() Event Procedure Changes.

The modified FormLoad() Event Procedure is given below:

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim nodKey As String
Dim PKey As String
Dim strText As String
Dim strSQL As String

Dim tmpNod As MSComctlLib.Node
Dim Typ As Variant

'1. Initializes TreeView Control Object
'2. Creates ImageList in ImageListObject
CreateImageList 

With tvw
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines
    .LabelEdit = tvwManual
    .Font.Name = "Verdana"
    .Indentation = 400
End With

strSQL = "SELECT ID, Desc, PID, Type,Macro,Form,Report FROM Menu;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rst.EOF And Not rst.BOF
    If Nz(rst!PID, "") = "" Then
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
      Set tmpNod = tvw.Nodes.Add(, , nodKey, strText, "FolderClose", "FolderOpen")
      
      'Root-Level Node Description in Bold letters
      With tmpNod
        .Bold = True
      End With
    Else
        PKey = KeyPrfx & CStr(rst!PID)
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        Set tmpNod = tvw.Nodes.Add(PKey, tvwChild, nodKey, strText, "LeftArrow", "RightArrow")
     
     'Check for the presense of Type Code
        If Nz(rst!Type, 0) > 0 Then
            Typ = rst!Type
            Select Case Typ
                Case 1 'save type Code & Form Name in Node Tag Property
                    tmpNod.Tag = Typ & rst!Form
                Case 2 'save type Code & Report Name in Node Tag Property
                    tmpNod.Tag = Typ & rst!Report
                Case 3 'save type Code & Macro Name in Node Tag Property
                    tmpNod.Tag = Typ & rst!Macro
            End Select
        End If
        
    End If
    rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set db = Nothing

End Sub

The Add() method line of TreeView Nodes has been highlighted in the VBA Code above, where the Image Key String Parameter Values are inserted for both normal and Click Views of the Images.

Alternatively, you may use Image Index Values 1 and 2 for the Root-level Nodes, and Index Numbers 4 and 5 for the Child Nodes.

You may change the Values and try it out yourself.

A new Demo Database with all the changes and additional Image Loading procedures is attached for you to Download.

Note: Create four new images, as explained above, for your own trial runs and change the Image's Names and location Addresses in the above Code, if you save the images in a different location.

Next, we will try out the easy method with the Images and I will share my Images with you.