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.
- Microsoft TreeView Control Tutorial
- Creating Access Menu with TreeView Control
- Assigning Images to TreeView Control
- Assigning Images to TreeView Control-2
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
No comments:
Post a Comment
Comments subject to moderation before publishing.