Visual Basic Concepts

  • Article

TreeView Control Scenario: Bind the TreeView to the Biblio.mdb Database

Sample Application: DataTree.vbp

The code examples in this chapter are taken from the DataTree.vbp sample application which is listed in the directory.

It is possible to bind the data from a database to a TreeView control. The following example binds a TreeView control to the Biblio database which is found on the Visual Basic CD. The scenario uses the Publishers table as the first level of tree nodes. If a publisher has one or more book titles, those titles are added to the tree as child nodes of the specific publisher.

Figure 2.42   Data-bound TreeView control

The following objects are used in the code below:

  • Data Access Object Library (3.5)

  • Form object named "frmDataTree"

  • TreeView control named "tvwDB"

  • CommandButton control named "cmdLoad"

To bind the Biblio.mdb Database to the TreeView control

  1. Add a reference to the Data Access Objects (DAO 3.0) to your project.

  2. Create module-level variables for the Database and Node objects.

  3. In the Form Load event, set the Database object variable to the Biblio database using the OpenDatabase statement.

  4. Create the top level node object using the Nodes collection’s Add method.

  5. In a CommandButton's Click event, create two Recordset variables and set them to the Publishers and Titles tables.

  6. Use the "Do Until" statement to create a Node object for each publisher in the table.

  7. For each publisher, check the PubID field in the Titles recordset for matches; add a child node for each match.

Add a Reference to the Data Access Objects (DAO 3.5) to Your Project

To bind a database to the TreeView control, you must first add a reference to the current version of Data Access Objects (DAO).

Create Module-Level Variables for the Database Object and Node Object

Because you will want to access the Biblio.mdb database several times during a single session, it's more efficient to keep a single copy of the database open by creating a module-level Database object. Thereafter, you can access the database without reopening it. In the Declarations section of the form, write:

Private mDbBiblio As Database

If you want the database to be used by other modules, use the Public statement, and rename the variable to reflect its global status, i.e., gDbBiblio.

When creating Node objects, use the Set statement (shown below) with a variable of type Node.

Dim TempNode As Node
Set TempNode = tvwDB.Nodes.Add()

While you can create the variable whenever you add Node objects, it is more efficient to declare a single module-level Node object variable once and use it to create all Node objects. Again in the Declarations section, write:

Private mNode As Node

Set the Database object variable to the Biblio database using the OpenDatabase statement

The Form object's Load event can be used to initialize the Database variable. The code for this would be:

Set mDbBiblio = DBEngine.OpenDatabase("BIBLIO.MDB")

After you have successfully initialized the Database object variable, you can freely access it from anywhere within the code module.

Form Load Event: Create the Top Level Node Object Using the Nodes Collection's Add Method

Now that the Database object variable has been initialized with the Biblio database, you may want to create the first node in the tree and assign it the name of the open database. You must first use the Node collection's Add method to create the first Node object. You should also use the Set statement with the mNode object variable, as shown below:

Set mNode = tvwDB.Nodes.Add() ' Create the first node.
mNode.Text = mDbBiblio.Name

Notice that in the code above, using the Set statement simultaneously created the Node while assigning it to the mNode object variable. Since the mNode variable now contains the newly created Node object, you can assign various properties to the new Node. In the above case, the name of the Database (i.e. the Database object's Name property) has been assigned to the new node's Text property.

CommandButton Click Event: Create Two Recordset Variables and Set Them to the Publishers and Titles Tables

The present scenario assumes that a button called "cmdLoad" exists, and that when the user clicks it, the TreeView control is populated with two tables from the Biblio database. To accomplish this task, you must first declare two DAO object variables in the button's Click event. The first variable, rsPublishers, will contain the Publishers table. The second, rsTitles, will contain the Titles table. The code below declares the two variables then uses the OpenRecordSet method to assign the tables to the variables:

Dim rsPublishers As Recordset
Dim rsTitles As Recordset

Set rsPublishers = mDbBiblio. _
OpenRecordset("Publishers", dbOpenDynaset)
Set rsTitles = mDbBiblio. _
OpenRecordset("titles", dbOpenDynaset)

Use the Do Until Statement to Create a Node Object for Each Publisher in the Table

Now that you have two open recordsets, you can iterate through each recordset, create a Node object, and assign an appropriate value to the object’s Text property. First, you must iterate through the Publishers table and create a Node object for each Publisher in the table.

The simplified code below can be stated as, "Do until the End of the Recordset: create a Node object and assign its Text property the value of the Title field; move to the next record and repeat":

Do Until rsPublishers.EOF
   Set mNode = tvwDB.Nodes.Add(1, tvwChild)
   mNode.Text = rsPublishers!Name
   rsPublishers.MoveNext
Loop

Note that in the Add method above, we used two arguments. The first argument (1) is the Index property of the Node to which we want to add a Child node. That is, we want all the Publisher nodes to be children of the first (root) node (which was created in the Form's Load event). The second argument uses a constant (tvwChild) that specifies the new Node will be a child node of the Node with index "1."

For Each Publisher, Check the PubID Field in the Titles Recordset for Matches; Add a Child Node for each Match

The code above will populate the first level of the TreeView with the contents of the Publishers table. However, we wish to go one level deeper and add child nodes to each Publisher node. Each child node will then represent a book that the Publisher prints.

To accomplish this, while we have the reference to the newly created Publisher node (mNode), we need to iterate through the Titles recordset and check every record's PubID field. If that field corresponds to the PubID field in the Publishers recordset, the book is published by our present publisher. But before we can add a node to mNode, we must first assign a variable (intIndex) the value of mNode's Index property, as shown below:

intIndex = mNode.Index

We can now use this variable in the Add method, which requires the Index property of the Node object to which a child node is being added:

Set mNode = tvwDB.Nodes.Add(intIndex, tvwChild)

The simplified code below can be stated as, "Do until the End of the Recordset: create a child Node object and assign its Text property the value of the Title field; move to the next record and repeat":

Do Until rsTitles.EOF
   If rsPublishers!PubID = rsTitles!PubID Then
      Set mNode = tvwDB.Nodes.Add(intIndex, tvwChild)
      mNode.Text = rsTitles!Title ' Text property.
   End If
Loop

Completing the Code

The code above shows the basic strategy for populating a table with two related tables. The complete code is shown below:

' Be sure to set References to DAO 3.5
' In the Declarations section, declare module-level
' object variables:
Private mDbBiblio As Database
Private mNode As Node

Private Sub Form_Load()
   ' In Form_Load event, set object variable and
   ' create first Node object of TreeView control.

   Set mDbBiblio = DBEngine.Workspaces(0). _
   OpenDatabase("BIBLIO.MDB")

   tvwDB.Sorted = True
   Set mNode = tvwDB.Nodes.Add()
   mNode.Text = "Publishers"
   mNode.Tag = mDbBiblio.Name   ' Set Tag property.
   mNode.Image = "closed"         ' Set Image
                                 ' property.
End Sub

Private Sub cmdLoad_Click()
   ' Declare DAO object variables and assign
   ' recordsets to them.
   Dim rsPublishers As Recordset
   Dim rsTitles As Recordset
   Set rsPublishers = mDbBiblio. _
   OpenRecordset("Publishers", dbOpenDynaset)
   Set rsTitles = mDbBiblio. _
   OpenRecordset("titles", dbOpenDynaset)

   ' Go to the first record.
   rsPublishers.MoveFirst

   Dim intIndex As Integer ' Variable for index.

   ' Do until the last record (EOF): add a Node
   ' object and use the Name field as the
   ' new Node object's text.
   Do Until rsPublishers.EOF
      Set mNode = tvwDB.Nodes.Add(1, tvwChild)
      mNode.Text = rsPublishers!Name
      mNode.Tag = "Publisher"   ' Tag identifies the
                                 ' table.
      ' Assign a unique ID to the Key
      mNode.Key = CInt(rsPublishers!PubID) & " ID"
      ' Set the variable intIndex to the Index
      ' property of the newly created Node. Use this
      ' variable to add child Node objects to the
      ' present Node.
      intIndex = mNode.Index
      ' While on this record, search the Title table
      ' for any occurence of the same PubID in the
      ' Titles recordset. If one is found, add a Node
      ' object to the TreeView control, and set the
      ' new Node object properties with the found
      ' record's Title, ISBN and Author fields.
      Do Until rsTitles.EOF
         If rsPublishers!PubID = rsTitles!PubID Then
            Set mNode = tvwDB.Nodes. _
            Add(intIndex, tvwChild)
            mNode.Text = rsTitles!Title   ' Text.
            mNode.Key = rsTitles!ISBN   ' Unique ID.
            mNode.Tag = "Authors"      ' Table name.
            mNode.Image = "leaf"      ' Image.
         End If
      rsTitles.MoveNext ' Next record in Titles.
      Loop
      ' Reset rsTitles to first Titles record.
      rsTitles.MoveFirst
      ' Move to next Publisher record.
      rsPublishers.MoveNext
   Loop
End Sub

Enhancing the Code

The example can be improved by using a SQL statement to create a smaller "Titles" recordset. The code below creates a recordset of only the records which have the same PubID value:

Set rsTitles = mDbBiblio.OpenRecordset _
("select * from Titles Where PubID = " & _
rsPublishers!PubID)

The code can then iterate through this smaller recordset more efficiently. The modified code is shown below:

Private Sub cmdLoad_Click()
   Dim rsPublishers As Recordset
   Dim rsTitles As Recordset
   Set rsPublishers = mDbBiblio. _
   OpenRecordset("Publishers", dbOpenDynaset)
   Dim intIndex
   Do Until rsPublishers.EOF
      Set mNode = tvwDB.Nodes.Add(1, tvwChild)
      mNode.Text = rsPublishers!Name
      mNode.Tag = "Publisher" ' Identifies the table.
      mNode.Key = rsPublishers!PubID & " ID"
      mNode.Image = "closed"
      intIndex = mNode.Index
      ' While on this record, create a recordset
      ' using a query that finds only titles that have
      ' the same PubID. For each record in the
      ' resulting recordset, add a Node object to the
      ' TreeView control, and set the new Node object
      ' properties with the record's Title, ISBN and
      ' Author fields.
      Set rsTitles = mDbBiblio.OpenRecordset _
      ("select * from Titles Where PubID = " & _
      rsPublishers!PubID)
      Do Until rsTitles.EOF
         Set mNode = tvwDB.Nodes. _
         Add(intIndex, tvwChild)
         mNode.Text = rsTitles!TITLE   ' Text.
         mNode.Key = rsTitles!ISBN      ' Unique ID.
         mNode.Tag = "Authors"      ' Table name.
         mNode.Image = "smlBook"      ' Image.
         ' Move to next record in rsTitles.
         rsTitles.MoveNext
      Loop
      ' Move to next Publishers record.
      rsPublishers.MoveNext
   Loop
End Sub