Linking Two Tables Using the DataCombo and DataList Controls

See Also

The distinguishing characteristic of the DataCombo and DataList controls is the ability to access two different tables and link data from the first table to a field in the second. This is accomplished by using two data sources (such as the ADO Data control or the Data Environment).

Relational Tables and "Unfriendly" Values

In a relational database, information that is used repeatedly is not stored in its entirety in multiple places. Instead the bulk of the information is stored in a recordset comprised of many fields; among these fields is an ID field that uniquely identifies the recordset. For example, the Biblio database supplied with Visual Basic stores the names of several publishing companies in a table named "Publishers." The table contains many fields, such as address, city, zip code, and phone number. But for the sake of simplicity, consider the Name and PubID fields as the two essential fields in the table. The Name field stores the name of a publisher, while the PubID field stores a comparatively "unfriendly" value such as a number or code. But this unfriendly value is more important because it uniquely identifies the publisher, and serves as a link to the entire recordset. And it's that value that is stored in multiple recordsets in a second table.

The second table is named "Titles," and each recordset contains information such as Title, year of publication, and ISBN. Included among the fields is one named "PubID." This field is named exactly the same as the corresponding field in the Publishers table because it stores the value that links the title to a specific publisher.

This efficient scheme presents a small problem: Given a database application that allows users to insert new titles, the user must somehow input integers that identify the publisher. This is fine if the user has memorized each publisher's unique ID, but it would be easier for people to see the name of the publisher instead, and have the application store the associated value in the database. The DataList and DataCombo controls solve this problem easily.

Two Data Sources, Three Fields, No Coding

The DataList and DataCombo controls use two sources to surmount the problem. While displaying only the name of the publisher (from the Publishers table), the DataList or DataCombo control writes only the value of the PubID field to the Titles table. Through the Properties window, set the RowSource to the data source that will supply the data to be written (the Publishers table). Then set the DataSource property to the data source that will be written to (the Titles table). Finally, set the DataField, ListField, and BoundColumn properties. The figure below shows how the two data sources (in the form of two Data controls) and the three fields are assigned to a DataCombo control:

In brief, the ListField property determines which field is actually displayed by the control. In this case, it is the name of the publisher. The BoundColumn property, on the other hand, determines which field in the Publishers table supplies the actual value to the Titles table. Note that the PubID field in the Publishers table can't (and shouldn't) be edited. Instead, the value found in the PubID field is written to the field specified by the DataField property. In this case, it is the PubID of the Titles table.

The table below summarizes the properties and how to use them.

Property Description
DataSource The name of the data control to which the DataList or DataCombo control is bound.
DataField The name of a field in the recordset specified by the DataSource property. This field will be used to determine which element in the list will be highlighted. If a new selection is made, it is this field that will be updated when you move to a new record.
RowSource The name of the data control that will be used to fill the list.
BoundColumn The name of a field in the recordset specified by the RowSource property. This field must be of the same type as the DataField that will be used to update the list.
ListField The name of a field in the recordset specified by RowSource that will be used to fill the list.

Note   You can also use the DataList and DataCombo controls with a single data control. To do this, set both the DataSource and RowSource properties to the same data control, and set the DataField and BoundColumn properties to the same field in the data control’s recordset. In this case, the list will be filled with ListField values from the same recordset that is updated. If a ListField property is specified, but no BoundColumn property is set, BoundColumn will automatically be set to the ListField field.

For More Information   To try a step-by-step procedure that builds a simple database application with the DataCombo control, see "Creating a Simple DataCombo Application."