Tutorial 6 - Selecting Multiple Rows Using Bookmarks

In this tutorial, you will learn how to select and highlight records that satisfy specified criteria. A group of similar items is generally implemented as a collection in True DBList. When manipulating a group of items in True DBList, use techniques similar to those described here. In this case, a row or record is represented by a bookmark and a group of selected rows is represented by a SelBookmarks collection.

To make the project a bit more interesting, when setting up the RecordSource property of the Data control, you will also learn how to use an SQL statement to create a join between two tables in a database.

Start a new project.
Place the following controls on the form (Form1) as shown in the figure: a Data control (Data1), a True DBList control (TDBList1), and two command buttons (Command1 and Command2).

Set the DatabaseName (Data control) property of Data1 to TDBLDemo.mdb, and the RecordSource (Data control) property to the following SQL statement:

Example Title	Copy Code
select * from composer, opus, composer inner join opus on composer.last = opus.last

This will create a RecordSource (Data control) containing all records from Composer joined with Opus having the same values of the data field Last.

Set the RowSource property of TDBList1 to Data1 and the MultiSelect property to 1 - Simple.
Set the Caption properties of Command1 and Command2 to Select and Clear, respectively.

You can easily select and deselect rows in True DBList by manipulating the SelBookmarks collection. To select rows, place the following code in the Click event of Command1:

Example Title	Copy Code
Private Sub Command1_Click() ' This routine loops through the Recordset to find and ' highlight all records with Country = "Germany" ' We shall use a clone so that we do not move the actual ' record position of the Data control Dim dclone As DAO.Recordset Set dclone = Data1.Recordset.Clone() ' In case there is a large Recordset to search through Screen.MousePointer = vbHourglass ' For each matching record, add the bookmark to the ' SelBookmarks collection of the list. The list will ' highlight the corresponding rows. Note that the bookmarks ' of a clone are compatible with the original set. ' This is ONLY true of clones. Dim SelBks As TrueDBList80.SelBookmarks Set SelBks = TDBList1.SelBookmarks Dim Criteria As String Criteria = "Country = '" & "Germany'" Dclone.FindFirst Criteria While Not dclone.NoMatch SelBks.Add dclone.Bookmark dclone.FindNext Criteria Wend ' Restore regular mouse pointer Screen.MousePointer = vbDefault End Sub

Example Title

Copy Code

Private Sub Command1_Click()

' This routine loops through the Recordset to find and

' highlight all records with Country = "Germany"

   

' We shall use a clone so that we do not move the actual

' record position of the Data control

    Dim dclone As DAO.Recordset

    Set dclone = Data1.Recordset.Clone()

 

' In case there is a large Recordset to search through

    Screen.MousePointer = vbHourglass

   

' For each matching record, add the bookmark to the

' SelBookmarks collection of the list.  The list will

' highlight the corresponding rows.  Note that the bookmarks

' of a clone are compatible with the original set.

' This is ONLY true of clones.

    Dim SelBks As TrueDBList80.SelBookmarks

    Set SelBks = TDBList1.SelBookmarks

 

    Dim Criteria As String

    Criteria = "Country = '" & "Germany'"

    Dclone.FindFirst Criteria

    While Not dclone.NoMatch

        SelBks.Add dclone.Bookmark

        dclone.FindNext Criteria

    Wend

   

' Restore regular mouse pointer

    Screen.MousePointer = vbDefault

End Sub

To deselect rows, place the following code in the Click event of Command2:

Example Title	Copy Code
Private Sub Command2_Click() ' Clear all selected rows by removing the selected records ' from the SelBookmarks collection. Dim SelBks As TrueDBList80.SelBookmarks Set SelBks = TDBList1.SelBookmarks While SelBks.Count <> 0 SelBks.Remove 0 Wend End Sub

Example Title

Copy Code

Private Sub Command2_Click()

' Clear all selected rows by removing the selected records

' from the SelBookmarks collection.

 

    Dim SelBks As TrueDBList80.SelBookmarks

    Set SelBks = TDBList1.SelBookmarks

 

    While SelBks.Count <> 0

        SelBks.Remove 0

    Wend

End Sub

Run the program and observe the following:

The True DBList control retrieves the database schema information from the Data control and automatically configures itself to display all of the fields in the joined database tables. This is again similar to the behavior of the list in Tutorial 1.
Click the Select command button and observe that all records with the Country field equal to Germany will be highlighted.

To deselect the highlighted records, click the Clear command button.

Multiple selection is only supported by True DBList, not True DBCombo.

To end the program, press the End button on the Visual Basic toolbar.You have successfully completed Tutorial 6.