Tutorial 4 - Displaying Master-Detail Relationships

This tutorial demonstrates how you can link multiple True DBList and True DBCombo controls using the ItemChange 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), a True DBCombo control (TDBCombo1), and a True DBList control (TDBList1) on the form (Form1) as shown in this figure.

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 Last FROM Composer

Set the DatabaseName (Data control) property of Data2 to TDBLDemo.mdb and the RecordSource (Data control) property to Opus.
Set the RowSource property of TDBCombo1 and TDBList1 to Data1 and Data2, respectively. Set the ListField and BoundColumn properties of TDBCombo1 to Last and the MaxComboItems property to 9.

Add the following code to the ItemChange event of TDBCombo1:

Example Title	Copy Code
Private Sub TDBCombo1_ItemChange() Dim strSQL As String ' A query is performed by taking the BoundText property ' which corresponds to the "LAST" name field from ' the Data1 control and building an SQL query on the LAST ' name field in the Data2 (Opus) table. strSQL = "SELECT * FROM Opus WHERE Last='" strSQL = strSQL & TDBCombo1.BoundText & "'" Data2.RecordSource = strSQL Data2.Refresh End Sub

Example Title

Copy Code

Private Sub TDBCombo1_ItemChange()

    Dim strSQL As String

 

    ' A query is performed by taking the BoundText property

    ' which corresponds to the "LAST" name field from

    ' the Data1 control and building an SQL query on the LAST

    ' name field in the Data2 (Opus) table.

 

    strSQL = "SELECT * FROM Opus WHERE Last='"

    strSQL = strSQL & TDBCombo1.BoundText & "'"

 

    Data2.RecordSource = strSQL

    Data2.Refresh

End Sub

Run the program and observe the following:

When Form1 is loaded, TDBCombo1 retrieves the database schema information from Data1 and automatically configures itself to display all of the last names in the Composer table. Similarly, TDBList1 displays all fields from the Opus table.
Change the current record position of TDBCombo1 by selecting a different composer. Observe that TDBList1 (the detail list) displays a new set of compositions each time the row changes in TDBCombo1 (the master list).

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