Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Tree View Control Check-Mark Add Delete Nodes

Introduction.

In this Episode of Tree View Control Tutorial we will learn how to  Add/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.  Addition of the Node can be on the same level of the marked Node or as a Child Node.

The TreeView Control Tutorial Sessions so far.

  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

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, 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 controls structure, arranged in hierarchical order and easy to understand.

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


New Visitors Get Updated for this Tutorial Session.

If you have not gone through the first Tutorial Session 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 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:

  1. 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.
  2. 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.
  3. 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 into 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 have 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 Text Boxes at 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 Check Boxes on the TreeView Control.

Normally, the Check-Box is not displayed on 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 check-box too.

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

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

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

The Demo TreeView Image given at the top, Details.

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

At the Right-side there are three Text Boxes, 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 it’s Child Nodes.

Before Selecting the Add Node  Command Button, the Text Property Value must be edited to replace 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 works together to set the Rule, as  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 I have check-marked the Date Field Node, among other Child-Nodes.  The Right side Property sheets shows it’s 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 Fields 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 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 needs 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 uses 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 check-marks and takes a Count of check-marked items.  If 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 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 selection of 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 set of SQL Strings are created based on the selected Node and Child-Node Check Box option above the Add Node command Button..

  1. If ParentID Property Value on the Form is empty and the Child Node check-box is not checked then a Root-Level new Record will be created, because the user Check-Marked a Root-level Node.
  2. 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.
  3. If ParentID Value >= 0  and Child Node check-box option not selected (not checked) then the New Record is created for the new Node, at the same level of the check-marked Node.

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

Next, based on the selection of option the SQL is executed to create New Record on the Sample Table, with 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 on 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 it’s Children, if present, from the Tree View Control.  The related records are also 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 check marks.  If there are 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 look for the presence of Child Node(s) of the selected Node.  If child nodes are found, then a message is displayed on 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 grand-parent 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 on the Table are also deleted, one after the other. Then deletes the marked Parent Record. 

If the marked Node have 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 on 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  frmSample Form is given below:

Your Observations, Comments, Suggestions are welcome.

The Demo Database is attached for Download.


DICTIONARY OBJECT

  1. Dictionary Objects Basics
  2. Dictionary Object Basics- 2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item
Share:

Assigning Images to Tree View Nodes-2

Introduction.

Hope, you found last few weeks Tutorial Sessions, of Microsoft TreeView and ImageList Control interesting and 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 have created 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 aide of both these Controls we could create a beautiful Microsoft Access Project Menu, having menu items in an 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 it’s Child Nodes from view.  When the User clicks on the Root-level Node the FolderOpen image appears and exposes it’s 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 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 is highlighted on the Design View of the above frmMenu Form is given below for reference:

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 have 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 have 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 it to the ImageList Control, without any VBA Code.

Besides that you can share the ImageList Control to other Projects, by simply Copy and Pasting the ImageList Control to other 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.

  1. Open the form frmMenu2 in Design View.
  2. Insert Microsoft ImageList Control from the Activex Controls List, somewhere on the empty space on frmMenu2 Form.
  3. Change it’s Name Property value to ImageList0.
  4. Right-Click on the Image List Control, highlight ImageListCtrl Object Option, on the displayed Menu, and select Properties.
  5. 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 into the Images Tab.
  6. Select the Images Tab. The Properties Images Tab looks like the following Image:
  7. Click on the Insert Picture Command Button, find the folder_closed sample image (if you have one or select the image you have prepared earlier), you have created for 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.

  8. Three Text Box controls: Index, Key and Tag are now enabled.  The Index control have the Index Value 1 inserted into it automatically.
  9. 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.  It’s data Type should be of String Type and unique among all the image Key Values. 
  10. The Tag Property can be used to record some useful information, like the Path Name of the Image.
  11. 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.
  12. If you would like to remove some image from the control then select that Image and click Remove Picture.
  13. 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 to repeat the upload process again.

    The ImageList Control with more Images:

  14. 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. 
  15. 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 to anywhere you want or share the Form/Database to others, with the images. You can add more images this way from new location.
  16. 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.
  17. 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

  1. Open frmMenu2 Form in Design View.
  2. Select Collapse All Command Button, open it’s Click Event Procedure and remove the Code.
  3. Remove that Command Button itself from the Form.
  4. Select Expand All Command Button, open it’s Click Event Procedure.
  5. 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
  1. Save and Open frmMenu2 in normal view.
  2. The Command Button Caption is Expand All now. 
  3. Click on the Command Button to expand all the Nodes.  All the Nodes are in expanded form now. The Caption of the Command Button changes to Collapse All.
  4. Click on it again and all the Nodes are in collapsed state, the Caption Text changes back to Expand All again.

Next Week we will see the usage of Check Boxes on Nodes to learn how we can identify checked Nodes and work with it.

CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality
Share:

Assigning Images to Tree View Nodes

Introduction.

Last week we have created Access Project Menu on 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 it to the following Post for download.  The Link of that Article is given below:

You may download the database so that you can add the new VBA Code pertains to the above topic and to try 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 and learn.

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 the following link for some corrective actions, which may be helpful to solve your issue:


Sample Demo Image.

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

Optionally, we can assign two Images on each Node.  One Image is displayed in normal state and different one displays 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 Arrow-head Image, facing to the left-side, in normal view and  the Arrow-head Image, pointing to the right, 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 in Google.  Ideal Image size, that looks good on the Node, is 16 x 16 pixels.  The ImageList Control have the 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 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 good quality, that can fit into the 16 x 16 pixels resolution (canvas size).  It works with both 16 x 16 pixel or custom settings, without loosing the quality of the image.

You may experiment with different image types, size and quality, 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.  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.

  1. Open the ProjectMenu.accdb Database.
  2. Make a Copy of the Form frmMenu and name it as frmMenu2 and keep it for later use.
  3. Open frmMenu in Design View.
  4. Select ActiveX Controls Option, from the Controls Button Group, and 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:

  5. Display it’s Property Sheet and change the Name Property value to ImageList0.
  6. 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.
  7. Select 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.
  8. Click Apply Command Button and then the OK Button to close the Property Sheet.

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

Image Loading Approaches.

There is an easy way and 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, when to use Code and when without Code.  VBA based method is good for experimenting, with different images, 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 to Node.  This way we add several images to the ImageList Control and use 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 PathName.  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.

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 Nodes Add() method’s 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 for Node’s normal view and the second image displayed when the Node 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 used for the Child Nodes normal view and for the Click Event action.

The ArrowHead image is ignored. 

FormLoad() Event Procedure with 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 is highlighted on 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, 2 for the Root-level Nodes and Index Numbers 4, 5 for Child Nodes.

You may change the Values and try out yourself.

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

Note: Create four new images, as explained above, for your own trial runs and change the Images 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 to you.

Sample Database for Download.

  1. MS-Access and E-Mail
  2. Invoke Word- Mail Merge from Access2007
  3. Automated Email Alerts
Share:

Creating Access Menu with Tree View Control

The Microsoft Access Project Menu, when finished with the Tree View Control, will look like the Image given below.

The Image above shows the Report Group’s third Option Custom Report is selected and highlighted, with the Report Filter Parameter Form open, overlapping the Menu Screen, for User’s input.

Before going into that, in last Week’s Lesson we have learned how to organize the related items in hierarchical order, using Microsoft Tree View Control, based on the Sample  data Table. 

I have made a point last week, that the related items in Tree View control’s data need not necessarily be next to each other.  After this you will be more clear about as how to update Relative Keys of Child Nodes, irrespective of it’s physical position of the records in the Table, but based on the relationship with it’s Parent Node IDs. 

This was the Data Table that we have used and finished with last week’s exercise:

Can you add the following list of items at the end of the above table and update their ParentID field values so that the TreeView display look like the sample image given below:

New records for Table item record related field:

  1. Text Field.
  2. Number Field.
  3. Date/Time Field.
  4. Hypelink Field.

Form related Controls:

  1. Text Box.
  2. Command Buttons.
  3. Combo Box.
  4. List Box.

Report related Controls:

  1. Text Box.
  2. Label.
  3. Graph Chart.

Assign ParentID Values to these items so that the Tree View Display looks like the following Image:

Now, we will proceed with the creation of an MS-Access Project Menu and learn what it takes to create one. A simple Menu Image is given below:


It is a simple Menu with only three group of options: Forms, Report Views and Macros. 

Under Forms Group two options are given, the first one displays the Tree View controls Menu table record.  The second Option displays the same records in continuous form mode.

The first option under Report View displays a Report on products Category records, from the Categories Table of NorthWind.accdb database.

The second option displays the Products List-Price Report.

The third option opens a Parameter Form so that the User can set the Minimum and Maximum List-Price values range, to Filter data for the Products List-Price Report.

Under the Macros Processes Group both option runs Macro1 and Macro2 respectively and displays different messages.

We need a Menu Table with the above option records with some additional fields, besides the usual TreeView’s Unique IDs, Description and ParentID data fields.  The Menu Table Image is given below:

Create a Table with the above structure and add the above records and save it with the name MenuID field is AutoNumber, PID and Type fields are Numeric fields, others are Text Fields.

We are familiar with the first three Data Fields: the Unique ID, Description and the ParentID Fields. Here, I have shortened the ParentID field name to PID.

We need four more fields in the Menu Table, one field Type for the object type Code and three fields Form, Report and Macro.

Type Field contains the Access Object Type Numeric Codes to identify the Option the User clicked on.

  • Form field is for Form Names, object Type code 1,
  • Report Field contains Report Names, object Type code 2,
  • Macro Field is for Macro Names, object type code 3.

Note: All the object names can be put in one Column. We have used separate fields for clarity only.  If you do that then make changes in the VBA Code, wherever it references different field names.

Based on the code numbers we can pickup the Object Names, from their respective fields and call the DoCmd.Openform or Docmd.OpenReport or Docmd.RunMacro to execute the action on the Child Node Clicks.

Now, the only question remains is how to link/store these two information (the Type Code and Object Name) on the Child Nodes?  We will take up that topic when we start Adding the Nodes to the Tree View control.

We need two more data Tables for sample Forms and Reports.  The Categories Table and Products Tables, from the NorthWind.accdb sample Database.  To save your time I have attached the Demo Database with all the Objects and Programs at the end of this Page to Download and try it out.

Create two Forms using the Menu Table with the names Data Entry and another Form Data View in continuous Form mode.

Create two Reports, one on the Categories Table with the report name: Categories,  another report on Products Table with the name Products Listing.  Add a long Label control below the main heading on the Products Listing Report and change the Name Property Value to Range.

Create a small form with two unboound Text Boxes and change their name Property Value to Min & Max, like the design given below:

Add two Command Buttons as shown above.  Change the Caption Property Value of the first Button to Open Report and the Name Property Value to cmdReport.

Change the Second Command Button’s Caption to Cancel and the Name Property value to cmdCancel.

Display the Code Module of the Form.  Copy and Paste the following Code into the Form Module and save the Form:

Private Sub cmdOpen_Click()
Dim mn, mx, fltr As String
mn = Nz(Me![Min], 0)
mx = Nz(Me![Max], 9999)
If (mn + mx) > 0 Then
    fltr = "[List Price] > " & mn & " And " & "[List Price] <= " & mx
    DoCmd.OpenReport "Products Listing", acViewReport, , fltr, , fltr
Else
    DoCmd.OpenReport "Products Listing", acViewReport
End If

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close
End Sub

When the User sets a Value Range by entering the Minimum and Maximum List Price range in their respective Text Boxes the Report Filter criteria String is created.  The Report Filter String value is passed to the Product Listing Report as Open Report command Parameter.  The Filter String value is also passed as OpenArgs (Open Argument) Parameter. 

The Filter parameter filters the Report Data, based on the Criteria, specified in Min & Max fields, and the open argument value is copied to the Range Label Caption when the Report is open.

Copy and Paste the following Code into the Product Listing Report’s VBA Module:

Private Sub Report_Open(Cancel As Integer)
    DoCmd.Close acForm, "Parameter"
    Me.Range.Caption = Nz(Me.OpenArgs, "")
End Sub
  1. Create a new Form, with the name frmMenu and add the Microsoft TreeView Control from the Activex Control’s List.  Resize the Control as shown in the Design View below:
  2. Change the Tree View Control’s name to TreeView0 in the normal Property Sheet.
  3. Add a Command Button below the Tree View control.  Change it’s Name Property Value to cmdExit and Caption Property value to Exit.
  4. Right-Click on the Tree View Control and highlight the TreeCtrl_Object option and select Properties to display the Property Sheet.
  5. Change the following Property Values as given below:
  • Style = 7 (tvwTreeLinesPlusMinusPictureText)
  • Line Style = 1 (tvwRootLines)
  • LabelEdit = 1 (tvwManual)

Last Week we have changed the first two Property Values.  When LabelEdit Property’s default value is 0 -  tvwAutomatic, Clicking on the Node twice (not double-click) the Node-Text will go on Edit Mode and you can change the Text.  But it will not directly update on the data source field.  By changing it to 1 – tvwManual will prevent it from going into edit mode.

We can change this through Code by adding the following lines in the Form_Load() Event Procedure:

With Me.TreeView0.Object
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines	
    .LabelEdit = tvwManual
End With

Last Week we have used the Form_Load() Event Procedure to read the Tree View Node values to create the Root-level and Child Nodes.  We need the same Procedure here also with few lines of  additional Code.

Besides that we need to trap the Node_Click() Event of Nodes to check which Option the User has selected.

Copy and Paste the following VBA Code into the Form Module and Save the Form.

Option Compare Database Option Explicit Dim tv As MSComctlLib.TreeView Const KeyPrfx As String = "X" 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 Set tv = Me.TreeView0.Object tv.Nodes.Clear

‘Change the TreeView Control Properties

With tv
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines
    .LabelEdit = tvwManual
    .Font.Name = "Verdana"
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 = tv.Nodes.Add(, , nodKey, strText) '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 = tv.Nodes.Add(PKey, tvwChild, nodKey, strText) '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 Private Sub cmdExit_Click() If MsgBox("Close Menu Form? ", vbYesNo, "cmdExit_Click()") = vbYes Then DoCmd.Close End If End Sub Private Sub TreeView0_NodeClick(ByVal Node As Object) Dim varTag, typeid As Integer Dim objName As String, nodOn as MSComctlLib.Node If Node.Expanded = False Then Node.Expanded = True Else Node.Expanded = False End If

‘Reset the earlier lighlight to normal

For Each nodOn In tv.Nodes
    nodOn.BackColor = vbWhite
    nodOn.ForeColor = vbBlack
Next

‘changes BackColor to light Blue and ForeColor White

tv.Nodes.Item(Node.Key).BackColor = RGB(0, 143, 255)
tv.Nodes.Item(Node.Key).ForeColor = vbWhite

‘—Highlight code ends-

varTag = Nz(Node.Tag, "") If Len(varTag) > 0 Then typeid = Val(varTag) objName = Mid(varTag, 2) End If Select Case typeid Case 1 DoCmd.OpenForm objName, acNormal Case 2 DoCmd.OpenReport objName, acViewPreview Case 3 DoCmd.RunMacro objName End Select End Sub

On the Global Declaration Area of the Module the Tree View Object is declared.  A constant variable KeyPrfx is declared with the value “X”.

The Form_Load() Event Procedure of last week’s Article we have modified with additional Code.  I have commented the new Code segment to give an indication of what it does but will explain what it does.

The Procedure declares Database, Recordset and four String Variables.  Next two line declares a temporary Node Object: tmpNod and Typ Variant Variables are declared.

Next, the TreeView Object tv is assigned with the TreeView0 Object on the Form.  The TreeView0’s existing Nodes, if any, are cleared with the statement: tv.Nodes.Clear, in preparation for loading all the Nodes again.

We have implemented the following Code to modify the Tree View control’s Properties through Code, rather than through the Property Sheet.

With tv
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines
    .LabelEdit = tvwManual
    .Font.Name = "Verdana"
End With 

The Tree View Font is changed to Verdana. Besides that we will bring in some more functions like expand or collapse all the Menu Groups with one click, rather than manually expanding or collapsing one group after the other.

The new SQL String is modified to add the new Fields Type, Form, Report and Macro Fields from Menu Table.

The Menu Table’s first record is checked for the presense of any value in PID field, if it is empty then it is a Root-level Node record. It is added to the Tree View Object as the Root level Node and it’s reference is saved in the tmpNod Object.

The Node have several properties like Forecolor, Bold and several others out of that we have taken the Bold Property and assigned True to make the Root level Node look different than it’s Child Nodes.

If it is not Root Node entry then it has the PID value, the program takes the Else clause and the record is added as a Child Node.  Here, we checks the Type field value.  If it contains one of the three values 1, 2 or 3 then we must take the value from Form, Report or Macro Name along with that the Type Code and join them together  (like ”1Data Entry”, “2Category Listing” etc.) and save it in the Tag Property of Child Nodes.  We are familiar with the Tag Property in Access controls, like Text Boxes, Labels, Command Buttons and others, but we rarely use it.

The cmdExit_Click() Procedure closes the Menu Form, if the response from the User is affirmative.

When the User clicks on a Child Node, the value we have saved in it’s Tag Property must be extracted and checked to determine what to do next.  For this we need a TreeView0_NodeClick() Event Procedure.

Private Sub TreeView0_NodeClick(ByVal Node As Object) Dim varTag, typeid As Integer Dim objName As String, nodOn as MSComctlLib.Node If Node.Expanded = False Then Node.Expanded = True Else Node.Expanded = False End If

‘Reset the earlier lighlight to normal

For Each nodOn In tv.Nodes nodOn.BackColor = vbWhite nodOn.ForeColor = vbBlack Next nodOn

‘changes BackColor to light Blue and ForeColor White tv.Nodes.Item(Node.Key).BackColor = RGB(0, 143, 255) tv.Nodes.Item(Node.Key).ForeColor = vbWhite ‘—Highlight code ends- varTag = Nz(Node.Tag, "") If Len(varTag) > 0 Then typeid = Val(varTag) objName = Mid(varTag, 2) End If Select Case typeid Case 1 DoCmd.OpenForm objName, acNormal Case 2 DoCmd.OpenReport objName, acViewPreview Case 3 DoCmd.RunMacro objName End Select End Sub

The Click() Event Procedure receives the clicked Node’s Reference as Parameter in the object Node.  At the beginning of this procedure we have declared few Variables.

Next few lines checks whether the clicked Node is in expanded or collapsed state.

Normally, to expand a Node, to show it’s hidden child Nodes, either we click on the + (plus symbol) at the left side of a Node or double-click on the Node itself.  Double-Clicking on the Node again or clicking on the – (minus symbol) will hide the Child Nodes.

With the following Code segment we can expand or collapse Child-Nodes with a single Click:

If Node.Expanded = False Then
    Node.Expanded = True
Else
    Node.Expanded = False
End If 

The next six executable lines ensures that the Node received the Click remains highlighted.

‘Reset the earlier Highlight to Normal

For Each nodOn In tv.Nodes nodOn.BackColor = vbWhite nodOn.ForeColor = vbBlack Next nodOn

‘Changes BackColor to light Blue and ForeColor White tv.Nodes.Item(Node.Key).BackColor = RGB(0, 143, 255) tv.Nodes.Item(Node.Key).ForeColor = vbWhite ‘—Highlight code ends-

Next, the Tag Property value is read into the varTag Variable. If it is not empty then the value is split into two part. The Numeric value is extracted and saved in Typid variable and the Object Name part is saved in variable objName.

Depending on the value in Typid variable the Docmd is executed to open the Form, Report or Runs the Macro.

We will add two more Command Buttons on the Top of the Menu. One to expand all the Nodes with one Click and the second one to collapse all the Nodes.

  1. Add two more Command Buttons at the top area of the Tree View Control as shown on the design below.
  2. Change the Name Property Value of the left Command Button to cmdExpand and the Caption to Expand All.
  3. Similarly, change the right-side Command Button’s Name Property to cmdCollapse and the Caption to Collapse All.
  4. Copy and Paste the following VBA Code below the existing Code in the frmMenu Form Module and save the Form.
Private Sub cmdExpand_Click()
Dim Nodexp As MSComctlLib.Node

For Each Nodexp In tv.Nodes
    If Nodexp.Expanded = False Then
        Nodexp.Expanded = True
    End If
Next Nodexp
End Sub


Private Sub cmdCollapse_Click()
Dim Nodexp As MSComctlLib.Node

For Each Nodexp In tv.Nodes
    If Nodexp.Expanded = True Then
        Nodexp.Expanded = False
    End If
Next Nodexp
End Sub

At the beginning of the cmdExpand_Click() Event we have declared a Tree View Node object NodExp.  The For . . . Next loop takes one Node at a time and checks whether it is in expanded form or not.  If not then it’s Expanded Property value is set to True.

Similarly the cmdCollapse_Click() Event makes a similar check and if it is in expanded state then the Expanded Property value is set to False.

The full Tree View Control’s all Nodes can be expanded and makes all their child Nodes visible at once or all Child Nodes kept hidden except the Root-level Nodes.

Hope you enjoyed creating the new Menu for your Project.  If you run along the design task step by step then your Menu should look like the finished Menu Image given at the top.

During the Year 2007 I have designed a Menu in one of my Projects, for Vehicles Service Contract System, using the Tab Control with several Pages. Each Page having 10 or more Options and to make each Page appear in turn at the same area, when the User Clicks on the Command Buttons lined up at either side of the Menu. Command Buttons at the right-side also changes, based on the selection of left side Button.

Click to Enlarge

You can find the Menu Design with Tab Control Article on this Link:https://www.msaccesstips.com/2007/06/control-screen-menu-design.html


CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality
Share:

Microsoft TreeView Control Tutorial

A. Introduction.

Microsoft Tree View Control is part of Microsoft Windows Common Controls. It is an interesting piece of Object that displays related data in an hierarchy of Nodes.  It can display related data, like entries and sub-entries in an Index List or listing of folders like Windows Explorer’s Left-Pane or a List of related Items in an hierarchical structure with Tree-Lines, Check-Boxes and Graphics of bitmap Images.

The ListView and ImageList Controls are part of Windows Common Controls and we will be using them along with the TreeView Control in Microsoft Access. 

I think you would like to look at some sample TreeView Control Demo Images, which we will be working on to build in the coming few weeks.

B. Sample Demo Images.

  1. The Sample Demo TreeView Image with all Nodes in Collapsed form.
  2. The above TreeView Control Nodes in expanded view.
  3. TreeView Sample Display, with arrow-head Image Icons displayed to the left of each Node Text.
  4. Next, the TreeView Display with linked data in a Sub-Form.  Root level Nodes have two Images.  Folder-Closed Image is displayed in normal mode.  When the Root Level Node receives a Mouse Click it displays the Folder-Open image and displays the Child-Nodes in expanded form. 

    Related information is displayed on the Sub-Form based on selection of Root level Node.

    One of the Child-Node item selected it displays another Form (normally kept hidden) with related information.

  5. On the next Form Image there are two Panels. The Product Category Item related Nodes are in TreeView Control, in the left Panel.  When one of the Category item receives a Click on the TreeView control, related Product Items with Quantity and List Price in separate Columns will appear in ListView Control, in the right-side Panel.

C. Creating Sample Data for Trial Run.

Let us try out the TreeView Control with some sample data shown below, based on the first two images shown at the beginning of this Page.

The above sample data table have three Fields. 

  • The ID field is an AutoNumber field with Unique ID Numbers.  The AutoNumber type is selected for our convenience.  In either case All records in the Table should have a Unique ID Value.  If it is Numeric then it should be converted into String Type, before adding it to the TreeView Control.
  • Second field is Node Description (Desc). The rows of information on this Column are  logically related. 
  • The third ParentID field is Numeric Type, to match the type of ID Field.  But it should be converted to String Type before using it on TreeView Control.

We must know how the Description Column values are related each other, based on that we can establish the relationship by entering related values into the ParentID field.

For example: The logical arrangement of relationship between Author of Books, Publishers of the Books,  the Book Stores where the Books are on Sale etc. or like Relationship between members iof a Family Tree. 

Relationship between Product Category, Products, Stock, Price and so on. All these information may not appear under one Column in a single Table.  They may appear in different Columns or in different tables as well.

The ParentID field is very important that it determines the hierarchical arrangement of Nodes. If the ParentID Field is empty then that record should go as a Root-level Node.  The Child-Node always should have their ParentID filled in with it’s Parent records ID Value.

Root level Node can have one or more Child Node(s), Child Node can have it’s own child Node(s).

We will load the above data into a TreeView Control and see how it looks.  Then we will fill up the ParentId field with related IDs to change the view, the way we want to see it in a logical order.

D. Windows Common Controls Library file.

  1. But, first thing first, open one of your Database or create a new one.
  2. Open the VBA Window (ALT+F11) and select References… from Tools Menu.
  3. Look for the File: Microsoft Windows Common Controls in the displayed list of files and put check-mark to select it.

    If you could not find the file in the list, Click Browse... Button and find the file: MSCOMLIB.OCX in the Windows System directory, for Windows 7 Version look for the file in SysWOW64 folder.  Click OK to close the Library Files listing Control.

  4. Create a Table with the following structure:
  5. Save the Table with the name Sample.
  6. Fill the Table with the sample data of 12 records as shown on the data view Image above.


    E. Creating TreeView Control on Form

  7. Create a New blank Form.
  8. Click on Activex Controls button from the Controls Group, find the Microsoft TreeView Control and select it.
  9. Click OK to insert a TreeView control on the Form.
  10. Drag the control down and to the right to leave some space at the top and left of the TreeView Control.  Drag the bottom right corner sizing handle towards the right and bottom corner to make the control larger, like the sample image given below.
  11. Display the Property Sheet of the control and change it’s Name Property Value to TreeView0, if it is different there.
  12. Display VBA Editing Window of the Form.
  13. F. Access VBA Code.

  14. Copy and Paste the following VBA Code into the Module overwriting the existing lines of Code there:

    Option Compare Database Option Explicit Dim tv As MSComctlLib.TreeView Const KeyPrfx As String = "X" Private Sub Form_Load() Dim db As Database Dim rst As Recordset

    Dim strSQL As String Dim nodKey As String Dim ParentKey As String Dim strText As String Set tv = Me.TreeView0.Object strSQL = "SELECT ID, Desc, ParentID FROM Sample;" Set db = CurrentDb Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) Do While Not rst.EOF And Not rst.BOF If Nz(rst!ParentID, "") = "" Then nodKey = KeyPrfx & CStr(rst!ID) strText = rst!Desc

    ‘Add the TreeView Root Level Nodes tv.Nodes.Add , , nodKey, strText Else ParentKey = KeyPrfx & CStr(rst!ParentID) nodKey = KeyPrfx & CStr(rst!ID) strText = rst!Desc

    ‘Add the Record as Child Node tv.Nodes.Add ParentKey, tvwChild, nodKey, strText End If rst.MoveNext Loop rst.Close Set rst = Nothing Set db = Nothing End Sub

  15. Save the Form with the name frmSample, but don’t close the VBA Window.

    G. VBA Code Line-by-Line.

Let us take a quick look at the VBA Code and understand what it does.

In the Global Declaration Area, of the Form Module, the Variable tv declared as TreeView Object.  The KeyPrfx declared as Constant , with String Type value “X”. 

The TreeView Node’s Key Value must be always of String Type and needs at least one non-numeric character present in the Node Key.  Our sample Table Key Values are all in numeric form, we can convert and add it to the Constant value “X”.  Numeric Value converted into String Type alone will not accept as Node-Key.

Note: If the Node-Key and Parent-Key  values are already in Alpha or Alpha-Numeric form then the question of conversion doesn’t arise.  All Node-Key values must be Unique.

In the Form_Load() Event Procedure the Database and Recordset objects are declared.  Four String Variables are also declared.

The statement Set tv = Me.TreeView0.Object statement assigns, the TreeView0  Object on the Form, to the object variable tv.

The OpenRecordset() statement opens the Sample Table Records using the SQL strSQL.

The Do While… statement ensures that the recordset is not empty, if empty then exit the Loop and end the Program.

If there are records then the first record’s ParentId field is checked for the presense of some value in there or not.

If it is empty then that record is for TreeView control’s Root level Node item.  The Root level Node needs only the unique Node-Key Value, which we already have in the ID Field, and Item Description Field value for Text Argument.

If the ParentID field have some value then the record is a Child-Node (Child of Root level Node or child of some upper-level Child Node) of the TreeView Object. 

The next line creates the Key Argument Value in nodKey String Variable, with the ID field Value,  converted into String and added to the constant prefix X, Node-Key becomes X1.

The rst!Desc field value added to the String Variable strText, simply for clarity and to make it short in the Nodes.Add() method’s Parameter listing, if the field reference is very lengthy then this will keep the Add() method neat and tidy.

The next executable line: tv.Node.Add() calls the Add() method of TreeView.Nodes Object to add the Node to TreeView0 control on the Form frmSample

The Syntax of Add() method is given below for reference:

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

All six Parameters of Add() method are optional.  If you call this method without any parameters then an Empty Root level Node will be added and a blank tree-line will appear as an indicator in the TreeView control.

For TreeView Root Level Node requires the Key and Text Argument values.

For Child Nodes both [Relative] and [Relationship] Arguments are required. Omitting any one of them will insert the Node as a Root Node, but will not generate any error.

[Relative] is the NodKey of an existing Node, entered into the related record’s ParentID field.  [Relationship] is a Constant tvwChild with numeric value 4, identifying it as a Child Node of Key Value in ParentID Field.

The other Constant values for Relationship Argument are used for positioning Child Nodes in a specific location.  The Constant Values are as follows :

tvwFirst = 0,  places as first Node, at the level of the relative Node.

tvwLast = 1,  places as last Node, at the level of the relative Node.

tvwNext = 2,  places the Node after a specified Node.

tvwPrevious = 3, placess the Node immediately preceeding the specified Node.

Note: You may experiment by setting  each Value in the Relationship Argument and Run the Code in Debug Mode, after keeping the VBA Window and Form in Normal View side by side. Watch how the Nodes are getting arranged in each cycle of the code execution to understand. 

These will be useful while editing the TreeView Control by Deleting an Item and inserting another item in it’s place or add a new Node at a specific location.

A Node with [Relative] Key must exist in the Nodes Collection before attempting to add a Child-Node to that Node, otherwise the Add() method generates an error.

This process is repeated till all the records are processed in the recordset.

Note: You may review the VBA Code again after the Demo Runs.

H. The First Trial Run.

Open the Form in Normal View.  The Trial Run result will look like the Image given below.

It doesn’t look more than a normal Listbox.  Remember we have not filled-in any value in the ParentID field in our Sample Table.  We have to establish some relationship between the Items in the rows of Record to move and position them in an hierarchical order in the TreeView Control.

I. Understanding the Relationship between Records.

  1. Open the Sample Table and let us examine the Records and how they are related.
  2. Let us leave the Database item alone as a Root Item.

    The Database Object also have some top-level objects: Application, DBEngine, Workspaces Collection and Databases Collection, which we have omitted here.

  3. Then we have the Tables group Item with ID value 2.
  4. Next Table, Fields and Field items are related to the Tables group.  We want Table, Fields and Field items to line up under the parent Item Tables Group Record with ID value 2.
  5. Let us call the record Tables as the Parent Node, Table, Fields and Field records as Child-Nodes.

    J. Updating the ParentID Field.

  6. So we need to update the value 2 (Node-Key of Tables) in ParentID field of Table, Fields and Field  records.
  7. Please update only those records and close the Table.  When it is done the records will look like the Image given below:
  8. Now, open the your frmSample in Form View and check the TreeView Control.  The result will look like the earlier one without any change.  The changes are already happened but it is not visible to you.

    K. The Property Sheet of TreeView Control.

  9. The TreeView Control has it’s own Property Sheet and the settings in there influences it's appearance.  So we will make a change in one of it’s Properties and come back to view the TreeView again.

  10. Turn the frmSample in Design View.
  11. Right-Click on the TreeView Control and high-light TreeCtrl_Object from the Shortcut Menu and select Properties.

    The Property Sheet will look like the Image given below:

  12. Settings on this Property Sheet changes the appearance of the TreeView Display.

    The left-side top Property Style is already set with the maximum features available Option-7 (tvwTreeLinesPlusMinusPictureText).

  13. Change the LineStyle Property Value = 1 (tvwRootLines) and Click Apply button then click OK to close the Property Sheet.

    L. Run After the LineStyle Property Value Change

  14. Save the Form and open it in Normal View.  Now, the Tree Lines are appearing correctly.  The Tables Node have a plus (+) sign at the left side, indicating that this Node have one or more Child Nodes in the next level and they are not in expanded form.
  15. Click on the plus symbol to expand the Node and display the Child Nodes, with the same ParentID.  When you click on the Minus Symbol the Child Nodes are collapsed and hidden, changing the symbold to plus sign again.
  16. The display will look like the following Image, when expanded:


    M. ParentID Updating of Other Records.

    We will update the Forms record ID Value (Node-Key Value) into Form, Controls and Control records’ ParentID fields, so that these records will list under the Forms Node as it’s Child Nodes.

    Similarly update ParentID field of Report and Controls records with Reports ID (Node-Key Value) Value  so that Report and Controls items will position under the Parent Node Reports, as it’s Child Nodes.

  17. Make changes to your Sample table records with the ParentID values as shown below:

    After the above changes the TreeView Display will look like the following Image, when all the Nodes are in expanded form.

    All Child Nodes related to the Root level Nodes: Tables, Forms and Reports are grouped as a list under their Parent Nodes.  But a Child Node may have a Parent Node, Grand-Parent Node or a Great Grand-Parent Node.

    N. Arranging All Object in Logical Hierarchical Order.

    For example let us take the first Root level Node Tables.  Logically Field (with record ID 5) is directly related to the Fields collection (record ID 4), the Fields collection related to Table and Table is part of Tables collection.  Each item in the group (record number 5 to 2) is related to one step up to the next level.

    So let us position these Child Nodes correctly under their own Parent Node and see how it looks.

  18. Open your Sample Table and change the ParentID values of the Tables related Child Records as shown below:

  19. The Field with ID-5 record’s Parent is Fields, record with ID-4, hence we have updated the 5th record’s ParentID field with ID Number 4.
  20. Like wise 4th record’s ParentID field updated with 3 and 3rd Record’s ParentID is updated with record number 2.

    Note: Don't assume that the items arranged in this way must be next to each other.

  21. After changes to the records save the Table and Open the frmSample to view the changes.  Your TreeView display should look like the image given below, with all Nodes in expanded form.

The Child-Node of a Root level Node can be a Parent-Node to it’s own Child or Children.  This way it can go several step down the tree.

Change the other two group of Child Node's ParentID field values to look like the Image given above.


DICTIONARY OBJECT

  1. Dictionary Objects Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item
Share:

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

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 Menus and Toolbars Objects Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Combo Boxes Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference TreeView Control 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 Control Item Macros Menus RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields ImageList Join ListView Control Methods Microsoft Nodes Numbering System Records Security Split SubForm Table Tables Time Difference Utility 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 Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo 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