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.  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.

F. Access VBA Code.

13. 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
    
    
    

14. 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.

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.

9. Turn the frmSample in Design View.
10. 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:

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).

11. 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

12. 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.
13. 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.
14. 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.

15. 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.

16. Open your Sample Table and change the ParentID values of the Tables related Child Records as shown below:
17. 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.
18. 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.

19. 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