Using DataCombo and DataList controls
The DataCombo and DataList controls are very similar to the standard list box and combo box controls, but have some important differences that make them extremely adaptable and useful in database applications. Both controls can be automatically populated by database fields to which these controls are bound. In addition, they can optionally pass a selected field to a second data control, making them suitable for creating "lookup table" applications.
Possible usage
1. In a relational database, use data from one table to provide values to be entered into a second (related) table. For example, in an inventory database, the names of suppliers are stored in a table, and each supplier has a unique identifier. Another table showing products uses these identifiers to indicate which supplier supplies the product. You can use the DataList control to display the name of the supplier while (invisibly) providing the supplier's identifier to the products table.
2. Allow users to narrow their search by selecting a criterion from a drop-down list. For example, a sales reporting database application could use a DataList control to let the user select a state or a sales region. Once a selection is made, the selection is automatically passed to a second data control, which is responsible for finding sales records for the selected area.
Like their corresponding intrinsic controls, the main difference between the DataList and DataCombo controls is that the DataCombo control provides a text box in which content can be edited.
For a description of the DataList and DataCombo controls' ability to join database tables, see "Joining Two Tables Using the DataCombo and DataList Controls." To create a simple database application that uses linked tables, see "Creating a Simple DataCombo Application".
Noteworthy control properties
Some important properties of the DataList and DataCombo controls include:
Note that the DataFormat property of the DataCombo control is an Extender property. Therefore it is always visible on the property sheet and can be set in code. However, the DataCombo control only formats the topmost item in its list. This may be less important to the end user who sees the formatted top item and can simply select from the unformatted list. Formatted items may also mislead end users into thinking that items are formatted before being entered into the database. For these reasons, it is recommended not to set the DataFormat property when using the DataCombo control.
Details For a step-by-step tutorial that demonstrates the use of the BoundText property, see "Creating a DataGrid with a Connected DataList Control." For a complete list of the properties and methods of these controls, see "DataList Control" and "DataCombo Control".
Connect two tables using DataCombo and DataList controls
The distinguishing feature 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 table. This is done using two data sources (such as the ADOData control or the Data environment).
Relational tables and "unfriendly" values
In a relational database, not all information for reuse is stored in multiple places. Most of this information is stored in a recordset consisting of multiple fields; among these fields is an "identifier" field that uniquely identifies the recordset. For example, the Biblio database provided by Visual Basic stores the names of several publishing companies in a table called Publishers. This table includes many fields, such as address, city, zip code, and phone number. But for the sake of simplicity, only the two essential fields of this table, Name and PubID, are considered. The Name field stores the name of a publisher, while the PubID field stores a relatively "unfriendly" value, such as a number or code. But this unfriendly value is important because it uniquely identifies the publisher and serves as a means of linking the entire record set. Additionally, this value will be stored in multiple recordsets in the second table.
The name of the second table is Titles, and each record set contains information including title, year of publication, International Standard Book Number (ISBN), etc. The name of one of these fields is PubID. The name of this field is the same as the corresponding field in the Publishers table, because this field stores the value that links this title to a specific publisher.
This possible solution presents a small problem: given a database application that allows users to insert new titles, the user must somehow enter an integer that identifies the publisher. If the user can remember the unique identifier of each publisher, then it is still feasible, but if on the one hand the user can see the name of the publisher, and on the other hand the corresponding value is stored in the database, then It will appear more convenient. The DataList and DataCombo controls can easily solve this problem.
Two data sources, three fields, no encoding
The DataList and DataCombo controls use two data sources to solve this problem. While only displaying the name of the publisher (from the Publishers table), the DataList or DataCombo control only writes the value of the PubID field to the Titles table. Through the Properties window, set the RowSource to the data source that provides the data to be written (i.e. the Publishers table). Then set the DataSource property to the data source where the data is to be written (i.e. the Titles table). Finally, set the DataField, ListField, and BoundColumn properties. The following figure demonstrates how to assign two data sources (in the form of two Data controls) and three fields to a DataCombo control:
Briefly, the ListField property determines which field is displayed by the control. In this case it's the name of the publisher. On the other hand, the BoundColumn property determines which field in the Publishers table supplies the actual required value to the Title table. Note that the PubID field in the Publishers table cannot (and should not) be edited. Instead, the value in the PubID field is written to the field specified by the DataField property. In this example, this attribute is the PubID field in the Titles table.
The following table provides an overview of these properties and how to use them.
Note that the DataList and DataCombo controls can also be used with a single data control. To achieve this, you can set 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 populated with ListField values from the same recordset being updated. If a ListField property is specified but the BoundColumn property is not set, the BoundColumn will automatically be set to the ListField field.
Details If you want to create a simple database application step by step using the DataCombo control, see "Creating a Simple DataCombo Application."
->