Tutorial 3 - Linking Multiple True DBGrid Controls

This tutorial demonstrates how you can link multiple True DBGrid controls using the RowColChange event to trigger related actions. This technique is particularly useful for displaying master-detail relationships.

Start a new project.
Place two Data controls (Data1 and Data2) and two True DBGrid controls (TDBGrid1 and TDBGrid2) on the form (Form1) as shown in this figure.
Set the DatabaseName property of Data1 to TDBGDemo.MDB, the RecordSource property to Composer, and the Caption property to "Composers".
Set the DatabaseName property of Data2 to TDBGDemo.MDB, the RecordSource property to Opus, and the Caption property to "Their Compositions".
Set the DataSource properties of TDBGrid1 and TDBGrid2 to Data1 and Data2, respectively.

Add the following code to the RowColChange event of TDBGrid1:

Example Title	Copy Code
Private Sub TDBGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer) ' A query is performed by taking the "LAST" name field from the Data1 control ' and building an SQL query on the LAST name field in the Data2 (compositions) ' file. ' The Second grid will respond automatically when the Data Control causes the ' change. We put up an hourglass so that there's a bit of feedback if Access ' is slow at finishing the query. Dim lastname$ Dim bk1 As String, bk2 As String ' To reliably compare bookmarks, you must first convert them into strings. You ' will also need to test for Null Bookmarks being passed by LastRow. This will ' occur on the initial display of the grid and if the user places the cursor ' on the AddNewRow and then moves off. If IsNull(LastRow) Then bk1 = "" Else bk1 = LastRow End If bk2 = TDBGrid1.Bookmark If bk1 <> bk2 Then Screen.MousePointer = vbHourglass lastname$ = Data1.Recordset("Last") Data2.RecordSource = "SELECT * FROM OPUS WHERE LAST = " _ + Chr$(34) + lastname$ + Chr$(34) Data2.Refresh Screen.MousePointer = vbDefault End If End Sub

Example Title

Copy Code

Private Sub TDBGrid1_RowColChange(LastRow As Variant, ByVal

 LastCol As Integer)

 

' A query is performed by taking the "LAST" name field from the Data1 control

' and building an SQL query on the LAST name field in the Data2 (compositions)

' file.

   

' The Second grid will respond automatically when the Data Control causes the

' change. We put up an hourglass so that there's a bit of feedback if Access

' is slow at finishing the query.

 

Dim lastname$

Dim bk1 As String, bk2 As String

   

' To reliably compare bookmarks, you must first convert them into strings. You

' will also need to test for Null Bookmarks being passed by LastRow. This will

' occur on the initial display of the grid and if the user places the cursor

' on the AddNewRow and then moves off.

 

If IsNull(LastRow) Then

    bk1 = ""

Else

    bk1 = LastRow

End If

   

bk2 = TDBGrid1.Bookmark

 

If bk1 <> bk2 Then

    Screen.MousePointer = vbHourglass

   

    lastname$ = Data1.Recordset("Last")

    Data2.RecordSource = "SELECT * FROM OPUS WHERE LAST = " _

                         + Chr$(34) + lastname$ + Chr$(34)

    Data2.Refresh

 

    Screen.MousePointer = vbDefault

End If

End Sub

Run the program and observe the following:

·When Form1 is loaded, TDBGrid1 and TDBGrid2 retrieve the database schema information from Data1 and automatically configure themselves to display all of the fields in the Composer and Opus tables, respectively.
However, when TDBGrid1 receives focus and sets the first row as the current row, the RowColChange event of TDBGrid1 will be fired. The RecordSource of Data2 will be modified and TDBGrid2 will reconfigure itself to display only compositions by Isaac Albeniz. If you observe carefully, when Form1 is first loaded, TDBGrid2 first displays all records in the Opus table, and then refreshes itself quickly to display only one record.
Change the current record position of Data1 by clicking on different rows of TDBGrid1. Observe that TDBGrid2 (the detail grid) will configure itself to display a new record set every time the row changes in TDBGrid1 (the master grid).

This concludes Tutorial 3.