ListView Scenario 3: Using a Procedure to Retrieve Titles from the Biblio.mdb Database

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

In scenario, "Using the ListView Control with the TreeView Control," the ListView control and the TreeView control are shown working in tandem. The TreeView control's NodeClick event is used to call two procedures, one to create ColumnHeader objects, and a second to populate the ListView control.

This scenario continues by developing the second procedure, called "GetTitles," that populates the ListView control with titles from the Biblio.mdb database.

The following example uses these objects:

• Form object named "frmDataTree"
  
  
• TreeView control named "tvwDB"
  
  
• ListView control named "lvwDB"
  
  
• ImageList control named "imlIcons"
  
  
• ImageList control named "imlSmallIcons"

To create a procedure that populates the ListView control

1. In the GetTitles procedure, clear the ListItems collection with the Clear method.
   
   
2. Use a query to create a "Titles" recordset.
   
   
3. Use the Do Until statement to create a ListItem object for each record in the recordset.

GetTitles Procedure: Clear the ListItems collection with the Clear method

Each time you invoke the GetTitles procedure, you will query the Biblio.mdb database to create a new collection of ListItem objects. The first thing to do, however, is to clear the old collection with the Clear method:

lvwDB.ListItems.Clear ' Clears all ListItems.

Use a query to create a "Titles" recordset

When the GetTitles procedure is invoked, the Key property's value of the Node property is passed to the procedure. Since the Key property contains the unique number that identifies the publisher (the PubID field), this value can be used to search the Titles table for all matches with the same PubID value.

The most efficient method to accomplish this is to create a query that finds only those records which have the same PubID value in the Titles table's PubID field. This query is:

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

Use the Do Until Statement to Create a ListItem Object for Each Record in the Recordset

Once the recordset is created, you can add to the ListItem collection. The following code iterates through this recordset to create a ListItem object for every record, setting the Text and SubItems properties with data from the recordset. The complete procedure is shown here:

Private Sub GetTitles(PubID)
   ' Clear the old titles.
   lvwDB.ListItems.Clear
   ' Declare object variable of type Recordset
   Dim rsTitles As Recordset
   ' While on this record, create a recordset using a 
   ' query that finds only titles that have the same 
   ' PubID. For each record in this recordset, add a 
   ' ListItem object to the ListView control, and set 
   ' the new object's properties with the record's 
   ' Title, ISBN and Author fields.
   Set rsTitles = mDbBiblio.OpenRecordset _
   ("select * from Titles where PubID = " & PubID)

   Do Until rsTitles.EOF
   ' Add ListItem.
         Set mItem = lvwDB.ListItems.Add() 
         mItem.Text = rsTitles!TITLE 
         mItem.SmallIcon = "smlBook" 
         mItem.Icon = "book"   
         mItem.Key = rsTitles!ISBN
         ' Use a function to get the author and set
         ' the SubItems(1) property.
         mItem.SubItems(1) = GetAuthor(rsTitles!ISBN)
         If Not IsNull(rsTitles![Year Published]) Then
            mItem.SubItems(2) =  _
            rsTitles![Year Published]
         End If
         mItem.SubItems(3) = rsTitles!ISBN
         rsTitles.MoveNext
   Loop
End Sub