Tutorial 5 - 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 DBGrid. When manipulating a group of items in True DBGrid, 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 DBGrid control (TDBGrid1), and two command buttons (Command1 and Command2).
Set the DatabaseName property of Data1 to TDBGDemo.MDB, and the RecordSource property to the following SQL statement:

select * from composer, opus, composer inner join opus on composer.last = opus.last

This will create a Recordset containing all records from Composer joined with Opus having the same values of the data field Last.
Set the DataSource properties of TDBGrid1 to Data1.
Set the Caption properties of Command1 and Command2 to "Select" and "Clear", respectively.

We can easily select and deselect rows in True DBGrid 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 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 grid. The grid 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 TrueDBGrid80.SelBookmarks Set SelBks = TDBGrid1.SelBookmarks Dim Criteria$ 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 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 grid. The grid 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 TrueDBGrid80.SelBookmarks

    Set SelBks = TDBGrid1.SelBookmarks

 

    Dim Criteria$

    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 TrueDBGrid80.SelBookmarks Set SelBks = TDBGrid1.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 TrueDBGrid80.SelBookmarks

    Set SelBks = TDBGrid1.SelBookmarks

 

    While SelBks.Count <> 0

        SelBks.Remove 0

    Wend

End Sub

Run the program and observe the following:

TDBGrid1 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 grid 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. Alternatively, clicking anywhere on a grid cell will also clear the selected rows.

This concludes Tutorial 5.