Lesson 3: Joining Records from Two Tables

In this lesson, you will see how to display records from two different tables that correspond to each other. This is a particularly useful technique in database programming. In this lesson, we will display personal information about students from the Person table which corresponds to academic information about the same students in the Student table. You will perform the following tasks:

Adding Another VAccess Control

Joining VAccess Controls

Adding and Displaying the Personal Data Form

Testing the Application

Adding Another VAccess Control

To add another VAccess control:

Place a second VAccess control on the frmVAccessForm and leave its Name property as VAccess2.

Set the three file location properties as follows:


Property	Value
DdfPath	C:\PVSW\DEMODATA
TableName	Person
Location	C:\PVSW\DEMODATA\Person.mkd

Joining VAccess Controls

To join two VAccess controls:

With the Property Pages dialog box still open, click Join to open the following dialog box:

Figure 6-12-Join Controls Dialog Box

The information displayed in the dialog box shows the table from which you are joining (Person), the selected index of that table (0-1D), the VAccess control to which you are joining (VAccess1), and the fields available in that control.

To join two fields, one from each VAccess control, the data types must be the same (i.e., both strings, both integers, etc.). In this case, the names of the fields you will join are also the same (ID to ID), but this does not have to be the case.

In the list box on the left is the index of Person table (ID). The data in that field is equivalent to the ID field of the VAccess1 VAccess control, which is the third item in the list box on the right.

Join the ID field of the VAccess2 control to the ID field of the VAccess1 control by double-clicking on ID in the Fields list.

The ID field then appears under the Equals column, showing that it is joined to the ID field of the VAccess2 control. The dialog box should appear like the one in Figure 6-13-.

Figure 6-13-Joined Fields of Two VAccess Controls

Click OK. In the Property Pages dialog box, the following text should appear in the text box next to the Join button:

VAccess1:ID

This text indicates that the selected VAccess2 control will be joined to the VAccess1 control via the ID fields.

Click OK to complete the join.

Adding and Displaying the Personal Data Form

Now you will add another form. This form will display personal data on the students in read-only format.

To create a read-only form with joined fields:

Add another form to the project. Change its Name property to frmPersonalData and its Caption property to Personal Data.

To have the PersonalData form displayed when the user clicks on the Browse Personal Data button on the frmStudentBrowser form, go to that form and double-click on that button. Place the following code in the Click event:

frmPersonalData.Show 
VAccessForm.VAccess2.Refresh

Now add the text boxes and labels that you see in the following figure, leaving a space for the Male/Female option buttons that you will add later.

Figure 6-14-The Personal Data Read-Only Form

Set the VAccessName property of each control to VAccess2, and select the following VAFieldNames, respectively:

Last_Name

First_Name

Perm_Street

Perm_City

Perm_State

Perm_Zip

In the Property Pages of each text box, click on the Styles tab. Select the ReadOnly check box and click OK.

Figure 6-15-Making a Text Box Read-Only

Create a Visual Basic Frame control on the form to hold the Male/Female option controls.

Add two VAOptionButtons within the frame and add Male and Female labels.

In the Property Pages of both option buttons, select VAccess2 as the VAccessName and Sex as the VAFieldName.

To make the Female button display the opposite information from the Male button, change VAValueTrue to 0 and VAValueFalse to 1.

To make the Male/Female buttons read-only, set the Enabled property on the Frame to False.

When the user selects a different student ID in the frmStudentBrowser form, that student's information will be displayed in the Personal Data form.

Figure 6-16-The Personal Data Form After Compiling

Testing the Application

You are now ready to save your work and test the application again. Run the program and test all the features of your application.