By Arthur Fuller
The instructions in this article are for Access 2000. Some of the properties may differ in other versions, but the technique is basically the same.
Quite a few Windows applications use the TreeView control–Outlook, Explorer, MyComputer–the list is long. But how many Access applications use it? Very few, thanks to the lack of documentation for this powerful control. An article in Element K Journals (published by element K Press) awakened my interest.[1] I followed its instructions and had something working in just a few minutes!
Still, for what I had in mind, the code presented in the article produced a few problems:
·
The Element K code was DAO,
which won’t work in SQL Server, so converting the code to ADO was the first problem.
·
The Element K code manufactures
unique keys for the nodes, a problem if you have in mind to storinge and retrievinge PKs and FKs on
demand, or doing
something intelligent with them upon selection. (And I most definitely had this in mind.).
·
A TreeView might populate slowly
when working with bound to lots of data.
But there are ways to limit the population. One way is to populate the first
level, and wait for that node to be selected before populating sublevels. For
instance, you could show only the month names in the top level and expand the
second and third levels to the days of the selected month, and then to the
selected day’s sales, respectively.
· The last reason I wanted to rewrite the Element K Journals code was purely personal: I like to test equality, not inequality.
Figure 1: This form requires about a dozen lines of code, in total. When a level-1 node is selected, the product subform disappears. When a level-2 node is selected, the product subform appears. In both cases, the subforms automatically display data from the selected node. When a product is selected, the matching supplier information is automatically displayed.
I hope that Figure 1
will convince you of the merits of the TreeView interface. The fFollowing is a
step-by-step recipe for creating this the form shown in
Figure 1form. Before examining the code that
makes it work, I encourage you to follow the recipe and see how quickly results
can emerge. To quickly create a TreeView control:
1. Open a blank database file.
2. Import the Suppliers and Products tables from the Northwind database (the sample database that comes with Access).
3. Base simple forms on the Suppliers and Products tables and name
fsubSuppliers and fsubProducts. If you use the AutoForms Wizard, you’ll probably
need to delete the subform component from fsubSuppliers,,
which the wizard automatically adds.
4. Create a new unbound form and name it frmTreeViewTest (no recordsource).
5. Place a TreeView object on it by making the following choices:
Insert/ActiveX object/Microsoft TreeView. (If you may see more than
one TreeView control, choose, choose
the latestnewest version). Name the control
twvTest.
6. Drop the two subforms onto frmTreeViewTest in approximately the same locations shown in Figure 1.
7. Set all the forms’ attributes as follows:
· no navigation buttons
· no record selectors
· Views Allowed: form only
· both scrollbars visible
name the TreeView control tvwTest
8. Create a new module and paste the code from Listing 1 into it.
9. In the frmTreeViewTest form’s OnLoad
event, paste the following code:
dbaTVPopulateADO tvwTest, "Suppliers", 1, _
"CompanyName", "SupplierID"
dbaTVPopulateADO tvwTest, "Products", 2, _
"ProductName", "ProductID", "SupplierID"
10.
Copy the appropriate Finder code
from Listing2 and Listing3Listings 2 and 3
into their respective subformsfsubProducts and fsubSuppliers,
respectively. This code handles the synchronization between TreeView
and forms.
11.
Copy the code in Listing 4 to tvwTest’s (the TreeView
control) NodeClick event.
Listing 1
Public Sub dbaTVPopulateADO _
(ctlTree As Control, _
strRecordSource As String, _
lvlNum As Integer, _
txtField As String, _
Optional pstrKeyField As String, _
Optional pstrParentField As String)
'--------------------
Dim rst As New ADODB.Recordset
Dim strKey As String, strRelative As String
Dim fldKey As ADODB.Field
Dim fldParent As ADODB.Field
Dim fldText As ADODB.Field
Dim fIsKey As Boolean, fIsParent As Boolean
Dim intRel As Integer, intNodeCount As Integer
Dim objNode As Node
rst.Open strRecordSource, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
fIsKey = True
fIsParent = True
'Determine which fields in strRecordSource contain the Text
'field, Key field, and Relative/Parent field Add method's
'arguments.
With rst
Set fldText = .Fields(txtField)
If pstrParentField <> "" Then
Set fldParent = .Fields(pstrParentField)
Else
fIsParent = False
End If
If pstrKeyField <> "" Then
Set fldKey = .Fields(pstrKeyField)
Else
fIsKey = False
End If
'setup complete. walk through the data source, beginning at the top
.MoveFirst
Do Until .EOF
'Create necessary Key and Relative strings.
'StrConv() allows for potential Text values as keys.
If fIsKey Then
'This next line is no guarantee of uniqueness.
'Just load two tables with autonumbers beginning at 1, to see the problem.
' strKey = rst(pstrKeyField) & "L" & lvlNum
'This line solves the problem by concatenating the PK, pk Column name and Level
'This results in keys like this:
'‘ PK
–ColumnNameLevel
' 110-AirPackageID3
' 347-EventID2
' 116-AirPackageID3
' 356-EventID2
strKey = rst(pstrKeyField) & "-" & pstrKeyField & lvlNum
Debug.Print strKey
End If
If fIsParent Then
'Same problem with this next line:
' strRelative = rst(pstrParentField) & "L" & (lvlNum - 1)
'Same solution: concatenate PK, tablename and level
strRelative = rst(pstrParentField) &
"-" & _
pstrParentField & (lvlNum -
1)
Debug.Print strRelative
End If
'Add the node.
Set objNode = ctlTree.Nodes.Add(Text:=.Fields(txtField))
With objNode
'Set Key and Parent properties when necessary
If fIsKey Then
.Key = strKey
End If
If fIsParent Then
On Error Resume Next
Set .Parent = ctlTree.Nodes(strRelative)
.Parent.Sorted = True
End If
objNode.Expanded = False
End With
.MoveNext
Loop
.Close
End With
Set fldKey = Nothing
Set fldParent = Nothing
Set fldText = Nothing
Set objNode = Nothing
Set rst = Nothing
End Sub
Public Function KeyToPK(strKey As String) As Long
'This function unconcatenates the pk from the manufactured node key
'See explanation above.
Dim lngPK As Long, intPos As Integer
intPos = InStr(1, strKey, "-")
lngPK = Val(Left(strKey, intPos))
KeyToPK = lngPK
End Function
Listing 2
Public Sub Finder(lngKey)
' Find the matching product record, given its PK
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Find "[ProductID] = " & Str(lngKey)
Me.Bookmark = rs.Bookmark
End Sub
Listing 3
Public Sub Finder(lngKey As Long)
' Find the matching supplier, given its PK
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Find "[SupplierID] = " & Str(lngKey)
Me.Bookmark = rs.Bookmark
End Sub
Private Sub tvwTest_NodeClick(ByVal Node As Object)
'This is the procedure that extracts
' 1. The level, by looking at the rightmost character
' 2. The key(s) by pulling off the right two characters
#If dbaEXPERIMENTING Then
Dim varReturn As Variant
#End If
Dim strLevel As String
'Dim lngTarget As Long
With Node
strLevel = Right(.Key, 1)
Select Case strLevel
Case "1"
Me.fsubProducts.Visible = False
'lngTarget = KeyToPK(.Key)
'fsubSuppliers.Form.Finder lngTarget
fsubSuppliers.Form.Finder KeyToPK(.Key)
Case "2"
Me.fsubProducts.Visible = True
fsubProducts.Form.Finder KeyToPK(.Key)
fsubSuppliers.Form.Finder KeyToPK(.Parent.Key)
Case Else
'do nothing
End Select
#If dbaEXPERIMENTING Then
Debug.Print .Key, .Text
varReturn = SysCmd(acSysCmdSetStatus, .Key & ":" & .Text)
#End If
End With
End Sub
The procedure is flexible and you can pass almost anything to it. For example, I have successfully passed complex multi-table SELECT statements. For the sake of simplicity, we’ll use a single SELECT statement per level, which omits loading any unnecessary rows.
A working TreeView consists of two types of object: the TreeView itself and the nodes that comprise it. A node object has several important properties:[2]
Key: – the unique identifier for a
given node
Text:
– what
is displayed for a given node
Parent:
– if
the node is a subordinate, this property contains the key of its parent
To add nodes to a
TreeView, you may create a new node, set its attributes, and then add i. Or,
alternatively, you can accomplish the same all in one step. In Listing 1, I
have chosen the multi-step method, to make it perfectly clear what’s happening.
Listings 2 and 3
contain the specialized “finder” code to synchronize the subforms with the
TreeView.
Let the combo-box
wizard do most of the work, then make the following modifications:
Make the procedures
public, so they can be called from outside the forms.
second, the key value
is passed in rather than deduced within the procedure. This enables the
TreeView to call the appropriate “finder” and supply the key value which it
deduces from the selected node’s key.
Methods
Nodes don’t have methods. All events belong to the TreeView itself, and they receive a reference to the selected node. For example, here is the TreeView’s click event:
This code is straightforward. One line [EJC1] worth noting, though,
ensures that the supplier’s subform is synchronized. This is
necessary because if multiple parent nodes are open, the operator could go from
one product to another across suppliers. In a TreeView containing more than two
nodes, each level would have to be dealt with similarly. Thus the Case block
makes it easy to add new cases.
For my purposes, I wanted all the level-one
nodes presented in collapsed mode. To achieve this, I added these lines to the
main form’s OnLoad event after calling the procedure
that populates the TreeView:
Dim n As Node
For Each n In tvwTest.Nodes
n.Expanded = False
Next
This code simply walks through the nodes in
the TreeViews Nodes collection, collapsing each in turn. Given a large number
of nodes, this could take a while. But as with all large-database techniques,
the developer is advised to follow the Sally Rand Rule[EJC2].[3]
The following discussion of properties and
methods highlights only those I find most interesting. This article intends to
persuade its reader that TreeViews isare a powerful and easily- implemented
interface device. If you want exhaustive detail on TreeView and node objects, I
suggest this: Follow the above instructions to create a working TreeView
project. Run it and open the form. Open the code window (Alt+F11) and then
invoke the Object Browser (F2). Select the MsComCtlLib library, and then the
TreeView class.
Figure 2: Use the Object Browser to inspect the TreeView class for an exhaustive look at its properties.
Here is a list of the
interesting properties and
methods, from the viewpoint of this article.Tables 1, 2, and 3 lists several TreeView properties, events, and methods.
Table 1: PropertiesTreeView Properties
and Methods
Properties
|
Property |
Description |
|
CheckBoxes |
True or False. |
|
PathSeparator |
When the TreeView contains nodes that
pertain to files, the PathSeparator and FullPath properties are |
|
ImageList |
Contains the images that appear in the
TreeView control. |
|
Indentation |
Defines how far the child nodes are indented when they appear below the parent node. |
|
LabelEdit |
The LabelEdit property determines how the
user can edit the textual part of a node. |
|
LineStyle |
If set to the constant value tvwTreeLines
(the default), lines appear between node and parents. |
|
Nodes |
Contains a reference to the Nodes collection. |
|
Scroll |
If set to True, scroll bars appear. |
|
Sorted |
Contains a Boolean value that pertains to
both a TreeView control and a Node object. |
|
Style |
Contains the attributes that control the graphics, if any, that appear next to each node. It also controls whether plus and minus signs appear. These graphics indicate to the user whether the node is expanded or collapsed. Lines may or may not connect the different nodes visually. |
Table 2: Events
Of the available
events, these two are most pertinent.
|
NodeClick |
From the perspective of this article, NodeClick() is the most important event. The TreeView control receives a reference to the node that was clicked. The example code retrieves the node’s key, strips the suffix, and calls the forms’ finders to synchronize the subforms. |
|
DoubleClick |
The example doesn’t use this, but you might want to tie some special processing to this event. |
Table 3: Methods
|
Refresh |
Forces the control to redraw itself. |
|
Add |
This method applies to the Nodes collection, as in: TreeView.Nodes.Add(, , "A", "Main Node 1", 1, 1) |
|
Delete |
Removes a node from the TreeView. Same parameters as Add. |
Node Properties and
Methods
Far and away the most important property of a node is its Key. This value uniquely identifies each node. For me, this turned out to be the biggest problem with the Element K Journals code. Its simple and illustrative method for manufacturing keys is prone to duplicates. Whatever method you devise to create unique node keys, it must work flawlessly in all situations.
What I decided to do, having discovered
this problem, was to concatenate the actual PK plus a dash with its CcolumnName
and its depth. In this scheme, a key could conceivably beresemble
1-SupplierID1.
I chose this factory-method because:
It readily distinguishes “1” in the Supplier table from “1” in the
Products table.
Since these will not exist at the same TreeView level, there will be
no collision in the suffix “Level”.
The
"-" character “-“ is easily located using
InStr(), so the decryption method— – translating
node key to PK— --
is simplified.
Table
4:
Properties:
???
Properties
|
Properties |
Description |
|
Child |
Contains a reference to the first child of a node, if one exists. |
|
Children |
Returns a number indicating the number of
children contained by the specified node. |
|
Expanded |
If set to True, the children of the
current node appear; otherwise, they do not. Double-clicking a node toggles
this property. |
|
ExpandedImage |
Specifies the image to display (if any)
when the node is expanded. |
|
Image |
Specifies the image to display (if any) when the node is collapsed. |
|
FirstSibling |