Advanced: Requires expert coding, interoperability, and multiuser skills.
The Data Outline control is not included with the Microsoft Office 97Developer Edition (ODE) as it is in the Microsoft Access Developer'sToolkit versions 2.0 and 7.0. The TreeView control enables you to display ahierarchical list of items, and you can use it in your Microsoft Access 97database as a replacement for the Data Outline control.
This article discusses some differences you may encounter when you use theTreeView control in place of the Data Outline control. It covers thefollowing topics:
- How to Fill Levels with Data in the TreeView Control
- How to Link Nodes in the TreeView Control
- How to Associate a TreeView Node with a Form in Your Database
- Distributing the TreeView Control with a Run-time Application
- Differences in Event Models Between TreeView and Data Outline Controls
- TreeView Control Performance
- Limitations of the TreeView Control vs. the Data Outline Control
NOTE: In order to make it easier for you to upgrade a database that usesthe Data Outline control in Microsoft Access 97, you can obtain version1.2 of the Data Outline control free from Microsoft's download services.Version 1.2 of the Data Outline control works with Microsoft Access 97databases. However, an updated Data Outline control will not be availablein future releases, and it is strongly recommended that you migrate to adifferent control, such as the TreeView control, for all new development.For information about downloading version 1.2 of the Data Outline control,please see the following article in the Microsoft Knowledge Base:
ACC97: Access 97 Data Outline ActiveX Control Available in Download Center
This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to the "BuildingApplications with Microsoft Access 97" manual.
Both the TreeView control and the Data Outline control can displayhierarchical data. However, the ways in which you populate the controlswith data are quite different. For example, many features of the DataOutline control that you set in its property sheet must be set with VisualBasic code in the TreeView control.
A key element in the TreeView control is the Node object in the Nodescollection. You use the Nodes collection to create hierarchical levels inthe TreeView control.
How to Fill Levels with Data in the TreeView Control
You must use Visual Basic code to fill a level in the TreeView controlwith data. In most cases, you open a RecordSet object and loop through therecords to create Nodes in the TreeView control. The following examplepopulates a TreeView control with a list of Customers in the Northwindsample database.
- Start Microsoft Access and open the sample database Northwind.mdb
- Create a new form not based on any table or query in Design view.
- On the Insert menu, click ActiveX Control.
- In the Insert ActiveX Control dialog box, select Microsoft TreeView Control, version 5.0, and then click OK.
- Set the following properties for the TreeView control:
- Set the form's OnLoad property to the following event procedure:
Private Sub Form_Load() Dim DB As Database, RS As RecordSet Set DB = CurrentDb ' Open a Recordset and loop through it to fill the TreeView ' control. Set RS = DB.OpenRecordset("Customers", dbOpenForwardOnly) Do Until RS.EOF Me!axTreeView.Nodes.Add , , RS!CustomerID, RS!CompanyName RS.MoveNext Loop RS.Close End Sub
- Save the form as frmCustList and switch to Form view. Note that the TreeView control fills with a list of Customer names.
How to Link Nodes in the TreeView Control
Each level in the Data Outline control has a LinkMasterFields propertythat enables you to link each level in the form to the level above it. Inthe TreeView control, you use the Key property of the Node object to linkone node to another.
You can set the Key property when you use the Add method of the Nodescollection to add a level to the TreeView control. Then you reference thatkey when you add new nodes to the TreeView control, and that is howdifferent levels are linked together. Following are some important tipsabout using the Key property:
- The value of each Key property in the TreeView control must be unique.
For example, the value of the Key property for a node in the second level of your control cannot duplicate the value of any keys you created anywhere else in the control. If you duplicate a key anywhere in your control, you receive the following trappable error:
Run-time error '35602':
Key is not unique in collection
- The Key property must contain some non-numeric data. If you use the primary key field of a table as the key for a node, and if that field is numeric, you can easily concatenate some text before or after the value to satisfy this requirement. If your key does not contain non- numeric data, you receive the following trappable error:
Run-time error '35603':
- The Key property is case-sensitive. For example, "ALFKI" is a different key than "alfki." You can easily resolve this case sensitivity by using the StrConv() function to explicitly convert all of your keys to thesame case.
The following example builds on the form you created in the previoussection and creates three levels in the TreeView control showingcustomers, orders, and order details. Note in the example how the letter"t" and the StrConv() function are used when creating the second level tosatisfy the requirements of the Key property.
- Start Microsoft Access and open the sample database Northwind.mdb.
- Open the frmCustList form in Design view.
- Change the event procedure in the form's OnLoad property to thefollowing:
Private Sub Form_Load() Dim DB As Database, RS As RecordSet Dim strOrderKey As String Set DB = CurrentDb ' Fill Level 1 using CustomerID as the Key property. Set RS = DB.OpenRecordset("Customers", dbOpenForwardOnly) Do Until RS.EOF Me!axTreeView.Nodes.Add , , RS!CustomerID, RS!CompanyName RS.MoveNext Loop RS.Close ' Fill Level 2. Set RS = DB.OpenRecordset("Orders", dbOpenForwardOnly) Do Until RS.EOF ' Link to Level 1 by referencing the CustomerID key and set ' the node as a child node of Level 1. Use "t" and the ' StrConv() function in the new Key property for Level 2, ' because OrderID is a numeric field. strOrderKey = StrConv("t" & RS!OrderID, vbLowerCase) Me!axTreeView.Nodes.Add RS!CustomerID, tvwChild, strOrderKey, _ RS!OrderID & " " & RS!OrderDate RS.MoveNext Loop RS.Close ' Fill Level 3. Set RS = DB.OpenRecordset("Order Details", dbOpenForwardOnly) Do Until RS.EOF ' Link to Level 2 by referencing the strOrderKey key and set ' the node as a child node of Level 2. strOrderKey = StrConv("t" & RS!OrderID, vbLowerCase) Me!axTreeView.Nodes.Add strOrderKey, tvwChild, RS!ProductID, _ RS!ProductID & " " & Format(RS!UnitPrice, "Currency") RS.MoveNext Loop RS.Close End Sub
- Save the form and switch to Form view. Double-click a customer's name to expand one level and see the order numbers and dates for that customer; double-click an order number to see the product number and unit price for each item in the order.
How to Associate a TreeView Node with a Form in Your Database
Each level in the Data Outline control has a FormName property that youcan set to associate a particular form with a level in the control. In theTreeView control, you can use the NodeClick event to reference thecurrently selected node, and then use the Key property of the node in theOpenForm method's Where condition to open a form to the correct record.For example:
Private Sub axTreeView_NodeClick(ByVal Node As Object) DoCmd.OpenForm "Customers", , , "[CustomerID] = '" & Node.Key & "'" End Sub
You can also add the same functionality to a button on your form, as thefollowing example illustrates.
The following example builds on the form you created in the previoussections. It uses the length of the Key value for the selected node todetermine whether to open the Customers, Orders, or Products form.
- Start Microsoft Access and open the sample database Northwind.mdb.
- Open the frmCustList form in Design view.
- Add a Command button to the form and set the following properties:
Caption: View Details
OnClick: [Event Procedure]
- Set the OnClick property of the command button to the following eventprocedure:
Private Sub cmdOpenForm_Click() Dim CurNode As Node Set CurNode = Me!axTreeView.SelectedItem On Error GoTo cmdOpenForm_Error ' Evaluate the Key value of the selected node. Select Case Len(CurNode.Key) ' All CustomerID keys are 5 characters long. Case 5 DoCmd.OpenForm "Customers", , , "[CustomerID] = '" & _ CurNode.Key & "'" ' All OrderID keys are 6 characters long. Case 6 DoCmd.OpenForm "Orders", , , "[OrderID] = " & Mid _ (CurNode.Key, 2) ' Anything else must be an Order Detail record. Case Is > 6 ' Extract the ProductID from the node key to use in the ' Where condition when you open the Products form. Dim i As Integer i = InStr(CurNode.Key, "p") DoCmd.OpenForm "Products", , , "[ProductID] = " & Mid _ (CurNode.Key, i + 1) End Select Exit Sub cmdOpenForm_Error: Select Case Err ' If error is because nothing is selected in TreeView. Case 91 MsgBox "Please select an item in the TreeView control." Case Else MsgBox "Error: " & Err & vbCr & Err.Description End Select Exit Sub End Sub
- Save the form and switch to Form view. Select any node on any level in the TreeView control, and then click the View Details button to open theassociated form to the correct record.
If an editable field on the form that is opened contains data that isdisplayed in your TreeView control, you can write code to update the Textproperty of the selected TreeView node if data changes on the form. Forexample, if your TreeView control displays the CompanyName field from theCustomers table, you can add code to the AfterUpdate event of theCompanyName field on the Customers form to update your TreeView node:
Forms!MyForm!MyTreeView.SelectedItem.Text = Forms!Customers!CompanyName
This method is faster than clearing and refilling the entire TreeViewcontrol when only one record has changed.
Also, if a user can change a field that you are using in the Key propertyof a node in your TreeView control, you must update that Key property. Youonly have to update the parent node, and the change is automaticallypropagated to all child nodes:
Forms!MyForm!MyTreeView.SelectedItem.Key = Forms!Customers!CustomerID
Distributing the TreeView Control with a Run-time Application
The TreeView control is contained in the Comctl32.ocx file, whichMicrosoft Office 97 Developer Edition (ODE) sets up in your Windows Systemfolder. You must include this file when you redistribute an applicationthat contains the TreeView control.
When you include Comctl32.ocx in the List Of Files box in the SetupWizard, the Wizard searches for that file's dependency file, Comctl32.dep.The dependency file tells the Setup Wizard what other support files needto be included with the ActiveX control. If you have Comctl32.dep on yourhard drive, you will notice that the Setup Wizard automatically includesComcat.dll in the List Of Files box when you add Comctl32.ocx; Comcat.dllis a required support file for Comctl32.ocx. If the Setup Wizard cannotlocate the Comctl32.dep file, you must manually add Comcat.dll to the listof files you redistribute with your application.
Differences in Event Models Between TreeView and Data Outline Controls
The Data Outline and TreeView controls each support different eventmodels. As a result, you may have to rewrite portions of your code whenyou migrate from the Data Outline control to the TreeView control. Thefollowing table compares the events in the two controls and identifieswhere no corresponding event is available.
Data Outline Control Event TreeView Control Event --------------------------------------------------- AfterCollapse Collapse AfterExpand Expand AfterFormClose <none> AfterFormOpen <none> AfterFormUpdate <none> AfterMove <none> AfterRefresh <none> AfterRequery <none> AfterSelChange NodeClick AfterStartup <none> DoKeyPress KeyPress DoRowClick <none> (Closest events are Click and MouseDown) DoRowDblClick <none> (Closest event is DblClick) Enter Enter ErrorEvent <none> Exit Exit FailCollapse <none> FailExpand <none> FailFormOpen <none> FailFormUpdate <none> FailMove <none> FailSelChange <none> GotFocus GotFocus KeyDown KeyDown KeyUp KeyUp LostFocus LostFocus MouseDblDown <none> (Closest event is DblClick) MouseDown MouseDown MouseUp MouseUp RequestCollapse <none> (Closest event is Collapse) RequestExpand <none> (Closest event is Expand) RequestFormOpen <none> RequestFormUpdate <none> RequestHelp <none> RequestMove <none> RequestSelChange <none> Updated Updated <none> AfterLabelEdit <none> BeforeLabelEdit <none> MouseMove <none> OLECompleteDrag <none> OLEDragDrop <none> OLEDragOver <none> OLEGiveFeedback <none> OLESetData <none> OLEStartDrag
TreeView Control Performance
Filling TreeView nodes from an array is faster than filling them from aRecordset object. However, it is common to use the TreeView control todisplay data from a table or a query, so Recordset objects are usedfrequently. You can improve the speed with which your TreeView fills withdata by using the DbOpenForwardOnly argument of the OpenRecordset method,as shown in the examples in this article.
If you open and loop through Recordset objects to fill TreeView nodes withdata, it may take some time to open a form or to display TreeView controldata when you are working with large recordsets. In contrast, the DataOutline control automatically binded to your data, which made it faster tosee data from large tables or queries.
Limitations of the TreeView Control vs. the Data Outline Control
The Data Outline control and the TreeView control are very different fromone another in many respects. Because the TreeView control can displayhierarchical data, it is the best choice among the ODE controls to replaceyour Data Outline control; however, there are some features of the DataOutline control that the TreeView control cannot emulate. For example:
- The TreeView control can only display one-to-one or one-to-many relationships between the levels in the control; the Data Outline control permits many-to-one relationships as well in its levels.
- You cannot set font properties for each level of data in the TreeView control; however, you can select a single font for all levels in the control.
- There is no DisplayWidths property in the TreeView control as there is in the Data Outline control, which can make it awkward to display two ormore items of data in a single node. For example, if your TreeView nodedisplays CustomerID & " " & CompanyName, the alignment of the data will be inconsistent if the size of the CustomerID field varies. Youmight see a node that looks like this:
1 ABC Company 27 A Second Company 4 XYZ Company 427 Northwind Traders You can compensate for this limitation by writing code to test the width of each field, and then pad with an appropriate number of spaces to properly align the columns.
For more information about using the TreeView control in Microsoft Access97, search the Help Index for "TreeView control."