Visual Basic Concepts
The code examples in this chapter are taken from the DataTree.vbp sample application which is listed in the Samples 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:
To bind the Biblio.mdb Database to the TreeView control
To bind a database to the TreeView control, you must first add a reference to the current version of Data Access Objects (DAO).
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
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.
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.
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)
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."
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
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
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