ACC2000: How to Migrate from Data Outline Control to TreeView Control (209927)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209927
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

The Data Outline control is not included with the Microsoft Office 2000 Developer (MOD) as it was in the Microsoft Access Developer's Toolkit versions 2.0 and 7.0. Instead, you can use the TreeView control to display a hierarchical list of items in your Microsoft Access 2000 database.
This article discusses some differences you may encounter when you use the TreeView control in place of the Data Outline control. It covers the following 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 program
  • Differences in event models between TreeView and Data Outline controls
  • TreeView control performance
  • Limitations of the TreeView control versus the Data Outline control
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

MORE INFORMATION

Both the TreeView control and the Data Outline control can display hierarchical data. However, the ways in which you populate the controls with data are quite different from each other. For example, many features of the Data Outline control that you set in its property sheet must be set with Microsoft Visual Basic code in the TreeView control.

A key element in the TreeView control is the Node object in the Nodes collection. You use the Nodes collection to create hierarchical levels in the TreeView control.

How to Fill Levels with Data in the TreeView Control

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

You must use Visual Basic code to fill a level in the TreeView control with data. In most cases, you open a Recordset object and loop through the records to create nodes in the TreeView control. The following example shows you how to populate a TreeView control with a list of customers in the Northwind sample database, which uses Data Access Objects (DAO), and in the NorthwindCS sample project, which uses ActiveX Data Objects (ADO).

Using DAO

  1. Start Access and open the sample database Northwind.mdb.
  2. Create a new form not based on any table or query in Design view.
  3. On the Insert menu, click ActiveX Control.
  4. In the Insert ActiveX Control dialog box, select Microsoft TreeView Control, version 6.0, and then click OK.
  5. Set the following properties for the TreeView control:
        Name:    axTreeView
    Width:   3"
    Height:  2"
  6. Set the form's OnLoad property to the following event procedure:
    Private Sub Form_Load()

    Dim DB As DAO.Database, RS As DAO.Recordset
    Set DB = CurrentDb
    
    ' Open a Recordset and loop through it to fill the TreeView
    ' control.
    ' The Key value, RS!CustomerID, is naturally alphabetical
    
    Set RS = DB.OpenRecordset("Customers", dbOpenForwardOnly)
    Do Until RS.EOF
       Me!axTreeView.Nodes.Add , , RS!CustomerID, RS!CompanyName
       RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

End Sub
  7. Save the form as "frmCustList," and then switch to Form view.

    Note that the TreeView control fills with a list of Customer names.

Using ADO

Follow the preceding steps, with the these modifications:
  • In step 1, open the sample project NorthwindCS.adp.
  • In step 6, insert the following code:
    Private Sub Form_Load()

    Dim CN As ADODB.Connection, RS As ADODB.Recordset

    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset

    'open a recordset and loop through it to fill the Treeview Control
    RS.Open "customers", CN, adOpenForwardOnly

    Do Until RS.EOF
        Me!axTreeView.Nodes.Add , , RS!CustomerID, RS!CompanyName
        RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
    Set CN = Nothing

End Sub

How to Link Nodes in the TreeView Control

Each level in the Data Outline control has a LinkMasterFields property that enables you to link each level in the form to the level above it. In the TreeView control, you use the Key property of the Node object to link one node to another.

You can set the Key property when you use the Add method of the Nodes collection to add a level to the TreeView control. Then you reference that key when you add new nodes to the TreeView control, thus linking different levels together. Here are some important tips about 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 that you created anywhere else in the control. If you duplicate a key anywhere in your control, you receive the following trappable-error message:
    Run-time error '35603':
    Invalid key
  • 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 message:
    Run-time error '35603':
    Invalid key
  • 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 the same case.
The following example builds on the form that you created in the previous section and shows you how to create three levels in the TreeView control. These three levels display customers, orders, and order details. Notice in the example how the letter o and the StrConv() function are used when creating the second level to satisfy the requirements of the Key property.

Using DAO

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Open the form "frmCustList" in Design view.
  3. Change the form's OnLoad event procedure to the following:
    Private Sub Form_Load()
    
    Dim DB As DAO.Database, RS As DAO.Recordset
    Dim strOrderKey As String, strProductKey As String
    
    Set DB = CurrentDb
    
    ' Open recordset and loop through it to fill TreeView control
    ' Fill Level 1 using CustomerID as the Key property, already alphabetical
    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 "o" and the
        ' StrConv() function in the new Key property for Level 2,
        ' because OrderID is a numeric field.
        strOrderKey = StrConv("o" & RS!OrderID, vbLowerCase)
        Me!axTreeView.Nodes.Add RS!CustomerID.Value, 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.
        ' Product Key is made alpha with "p" for parsing in cmdOpenForm_Click
        strOrderKey = StrConv("o" & RS!OrderID, vbLowerCase)
        strProductKey = StrConv(strOrderKey & "p" & RS!ProductID, vbLowerCase)
        Me!axTreeView.Nodes.Add strOrderKey, tvwChild, strProductKey, _
           RS!ProductID & "   " & Format(RS!UnitPrice, "Currency")
        RS.MoveNext
    Loop
    RS.Close

    Set RS = Nothing
    Set DB = Nothing
    
End Sub
  4. 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.

Using ADO

Follow the preceding steps, with the these modifications:
  • In step 1, open the sample project NorthwindCS.adp.
  • In step 3, insert the following code:
    Private Sub Form_Load()

    Dim CN As ADODB.Connection, RS As ADODB.Recordset, strOrderKey As String, strProductID As String
    
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    
    ' Open a recordset and loop through it to fill the Treeview Control
    ' Fill Level 1 using customerID as Key property, already alphabetical
    RS.Open "customers", CN, adOpenForwardOnly
    Do Until RS.EOF
        Me!axTreeView.Nodes.Add , , RS!CustomerID, RS!CompanyName
        RS.MoveNext
    Loop
    RS.Close
    
    ' Fill Level 2
    RS.Open "orders", CN, adOpenForwardOnly
    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 "o" and the StrConv()
        ' function in the new key property for Level 2, because OrderID is a numeric field
        strOrderKey = StrConv("o" & RS!OrderID, vbLowerCase)
        Me!axTreeView.Nodes.Add RS!CustomerID.Value, tvwChild, strOrderKey, RS!OrderID & "  " & RS!OrderDate
        RS.MoveNext
    Loop
    RS.Close
    
    ' Fill level 3
    RS.Open "[order details]", CN, adOpenForwardOnly
    Do Until RS.EOF
        ' Link to Level 2 by referencing the strOrderKey key and set
        ' the node as a child node of level 2.
        ' Product Key is made alpha with "p" for parsing in cmdOpenForm_Click
        strOrderKey = StrConv("o" & RS!OrderID, vbLowerCase)
        strProductID = StrConv(strOrderKey & "p" & RS!ProductID, vbLowerCase)
        Me!axTreeView.Nodes.Add strOrderKey, tvwChild, strProductID, RS!ProductID & "  " & Format(RS!UnitPrice, "currency")
        RS.MoveNext
    Loop
    RS.Close

    CN.Close
    Set RS = Nothing
    Set CN = Nothing

End Sub

How to Associate a TreeView Node with a Form in Your Database

NOTE: This section applies only to Access databases (*.mdb).

Each level in the Data Outline control has a FormName property that you can set to associate a particular form with a level in the control. In the TreeView control, you can use the NodeClick event to reference the currently selected node, and then use the Key property of the node in the OpenForm 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 the following example illustrates.

This example builds on the form you created previously. It uses the length of the Key value for the selected node to determine whether to open the Customers, Orders, or Products form, and parses the Key value to determine the Customer, Order, or Product ID.
  1. Start Access and open either the sample database Northwind.mdb.
  2. Open the "frmCustList" form in Design view.
  3. Add a Command button to the form, and then set the following properties: 
        Name:    cmdOpenForm
    Caption: View Details
    OnClick: [Event Procedure]
  4. Insert the following as the command button's OnClick event procedure:
    Private Sub cmdOpenForm_Click()

   Dim CurNode As Object
   Dim strWhere As String
   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        ' CustomerID keys are of the form ALFKI
         strWhere = "[CustomerID] = '" & CurNode.Key & "'"
         DoCmd.OpenForm "Customers", , , strWhere
      ' All OrderID keys are 6 characters long.
      Case 6        ' OrderID keys are of the form o12345
         strWhere = "[OrderID] = " & Mid(CurNode.Key, 2)
         DoCmd.OpenForm "Orders", , , strWhere
      ' Anything else must be an Order Detail record.
      Case Is > 6   ' OrderID keys are of the form o12345p12
         ' 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")
         strWhere = "[ProductID] = " & Mid(CurNode.Key, i + 1)
         DoCmd.OpenForm "Products", , , strWhere
   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.Number & vbCr & Err.Description
   End Select
   Exit Sub

End Sub
  5. Save the form, and then switch to Form view. Select any node on any level in the TreeView control, and then click the View Details button to open the associated form to the correct record.
If an editable field on the form that has been opened contains data that is displayed in your TreeView control, you can use code to update the Text property of the selected TreeView node if data changes on the form. For example, if your TreeView control displays the Company Name field from the Customers table, you can add code to the AfterUpdate event of the Company Name field on the Customers form to update your TreeView node: 
Forms![frmCustList]![axTreeView].SelectedItem.Text = Forms![Customers]![Company Name]
This method is faster than clearing and refilling the entire TreeView control when only one record has changed.

Also, if a user can change a field that you are using in the Key property of a node in your TreeView control, you must update that Key property. You only have to update the parent node, and the change is automatically propagated to all child nodes: 
Forms![frmCustList]![axTreeView].SelectedItem.Key = Forms![Customers]![CustomerID]

Including the TreeView Control with a Program for Distribution

The TreeView control is contained in the Comctl32.ocx file, which Microsoft Office 2000 Developer (MOD) sets up in your Windows/System32 folder. You must include this file when you redistribute a program that contains the TreeView control. Comctl32.ocx itself requires an additional file, Comcat.dll, which must also be included in your distribution.

You must use the Packaging and Deployment Wizard to prepare a program for distribution.

For additional information about the Packaging and Deployment Wizard, click the article numbers below to view the articles in the Microsoft Knowledge Base:

240956 MOD2000: Package and Deployment Wizard Now Used to Create a Distributable Application

236143 MOD2000: How to Start the Package and Deployment Wizard

Differences in Event Models Between TreeView and Data Outline Controls

The Data Outline and TreeView controls each support different event models. As a result, you may have to rewrite portions of your code when you migrate from the Data Outline control to the TreeView control. The following table compares the events in the two controls and identifies events for which 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

It is faster to fill TreeView nodes from an array than to fill them from a Recordset object. However, it is common to use the TreeView control to display data from a table or a query, so Recordset objects are used frequently. You can improve the speed with which your TreeView fills with data 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 with data, it may take some time to open a form or to display TreeView control data when you are working with large recordsets. In contrast, the Data Outline control is automatically bound to your data, which makes it faster to see data from large tables or queries.

Limitations of the TreeView Control Versus the Data Outline Control

The Data Outline control and the TreeView control are very different from one another in many respects. Because the TreeView control can display hierarchical data, it is the best choice among the ODE controls to replace your Data Outline control. However, there are some features of the Data Outline control that the TreeView control cannot emulate. For example:
  • The TreeView control can display only 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 or more items of data in a single node. For example, if your TreeView node displays CustomerID & " " & CompanyName, the alignment of the data will be inconsistent if the size of the CustomerID field varies. You might 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.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kberrmsg kbhowto kbinfo kbProgramming kbusage KB209927
©2004 Microsoft Corporation. All rights reserved.