Visual Basic Concepts

ListView Scenario 4: Using a Function to Return an Author's Name from the Biblio.mdb Database

See Also

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

In the scenario, "ListView Control Scenario 1: Using the ListView Control with the TreeView Control," the ListView control and the TreeView control are showed working in tandem. In that scenario, the TreeView control's NodeClick event is used to call a procedure called "GetTitles" to populate the ListView control. That procedure queries the "Titles" table to create a recordset, then creates a ListItem object for each record it finds.

One piece of information that is not contained in the "Titles" table is the name of the book's author. Instead, the "Titles" table contains a field named "ISBN" that stores the ISBN number of the book. The value of this field is also contained in a second table called "Title Author" which links the ISBN value with a field (Au_ID) that identifies the author.

To retrieve the author's name, the function must

  1. Find the ISBN value in the "Title Author" recordset.

  2. Find the AuthorID value in the Authors recordset.

  3. Return the name of the author.

Setup

The scenario uses the following object:

Find the ISBN Value in the "Title Author" Recordset

The GetAuthor function uses the value of the ISBN field to search the "Title Author" recordset. As in other database operations, first declare an object variable of type RecordSet, then open the "Title Author" table and assign the reference to the object variable. However, since the code uses two recordsets, declare the object variables together, then open the recordsets, as shown.

Dim rsTitleAuthor As Recordset
Dim rsAuthors As Recordset

Set rsTitleAuthor = mDbBiblio. _
OpenRecordset("Title Author", dbOpenDynaset)
Set rsAuthors = mDbBiblio. _
OpenRecordset("Authors", dbOpenDynaset)

With the rsAuthors object variable and the value from the ISBN field, search the "Title Author" table:

Dim strQuery As String
strQuery = "ISBN = " & "'" & ISBN & "'"
rsTitleAuthor.FindFirst strQuery

Find the AuthorID value in the Authors recordset

The FindFirst method will return the first match that meets the criteria in the query. The other field in the "Title Author" table is the "Au_ID" field, which contains the ID of an author in the "Authors" table. Therefore, we can now use the value from the "Au_ID" field in the query, and search the "Authors" table, as shown:

' Reset query string.
strQuery = "Au_ID = " & rsTitleAuthor!AU_ID
' Search again using the FindFirst method.
rsAuthors.FindFirst strQuery

Return the Name of the Author

Finally, the "Authors" table has been entered, and the "Au_ID" field has been searched. Presuming no errors have been made, the current recordset should return the name of the author. The following code instructs the function to return the author's name to the calling code:

GetAuthors = rsAuthors!Author

The Complete GetAuthor Function

The complete function is shown:

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