The code discussed in the ListView scenarios 1 through 4, except for the procedure to populate the TreeView controls, is shown below. For code to populate the TreeView control, see "TreeView Control Scenario: Binding the TreeView to the Biblio.mdb Database." For a working example of this code, see the sample application DataTree.vbp which is listed in the Samples directory.
' General Declarations
Private mDbBiblio As Database ' Database variable.
Private Sub Form_Load()
' Open the Biblio.mdb and set the object variable
' to the database.
Set mDbBiblio = DBEngine.Workspaces(0). _
OpenDatabase("Biblio.mdb")
' Code to populate the TreeView control
' isn't shown here.
End Sub
Private Sub tvwDB_NodeClick(ByVal Node As Node)
' Check the Tag for "Publisher." If so, then
' call the MakeColumns procedure and then the
' GetTitles function.
If Node.Tag = "Publisher" Then
MakeColumns
GetTitles Val(Node.Key)
End If
End Sub
Private Sub MakeColumns()
' Clear the ColumnHeaders collection.
lvwDB.ColumnHeaders.Clear
' Add four ColumnHeaders.
lvwDB.ColumnHeaders.Add , , "Title", 2000
lvwDB.ColumnHeaders.Add , , "Author"
lvwDB.ColumnHeaders.Add , , "Year", 350
lvwDB.ColumnHeaders.Add , , "ISBN"
End Sub
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
Private Function GetAuthor(ISBN)
' Declare DAO object variables.
Dim rsTitleAuthor As Recordset
Dim rsAuthors As Recordset
' Set object variables to recordsets.
Set rsTitleAuthor = mDbBiblio. _
OpenRecordset("Title Author", dbOpenDynaset)
Set rsAuthors = mDbBiblio. _
OpenRecordset("Authors", dbOpenDynaset)
' Create query string.
Dim strQuery As String
strQuery = "ISBN = " & "'" & ISBN & "'"
rsTitleAuthor.FindFirst strQuery
' If there is no author, return "n/a."
' Otherwise, return the name of the author.
If rsTitleAuthor.NoMatch Then
GetAuthor = "n/a"
Exit Function
Else
' Presume we have found the right recordset.
' Then reset the string query with Au_ID
' field value and search "Authors" table.
strQuery = "Au_ID = " & rsTitleAuthor!AU_ID
rsAuthors.FindFirst strQuery
' Return the name of the author from the Author
' field.
GetAuthor = rsAuthors!Author
End If
End Function