The ComboBox control is commonly used as a lookup table, as demonstrated in the Data Entry application earlier in this chapter. To populate the list of the data bound CornboBox control, you must set its RowSource property to a Data control, connected to the database and table from which the data will come.
The Data Entry application (in the DEntry folder on the CD) contains a hidden Data control (named Data2) that is connected to the Publishers table of the BIBLIO database. The data-bound ComboBox control’s ListField property is the name of the field that will be used to populate the list. In the Data Entry application, this property is set to the Name field of the Publishers table. When the program starts, the data-bound ComboBox control is populated automatically with the names of the publishers in the database, as shown in Figure 17.22.
The data-bound ComboBox control has the usual DataSource and DataField properties, just like the other data-bound controls. In the Data Entry application, we want the data-bound ComboBox control to operate as follows:
- When the program starts, the’ control is populated with the names of all publishers.
- When the user navigates through the Titles table, the Combobox control picks up the publisher’s ID from the Titles table, looks up this value in the Publishers table, and displays the Name field of the matching record in the edit box.
- When appending a new record, the user specifies the book’s publisher by selecting a name in (the ComboBox control, but the control reports the matching ID to the first Data control.
This sounds like complicated behavior, but it’s all built into the data-bound ComboBox control. You’ve already seen how the first behavior can be implemented with the help of the Rowsource and ListField properties. The other two behaviors are typical of it data-bound control. W~want to use the edit box of the data-bound ComboBox control as if it were a regular textbox, bound to the PubID field of the Titles table. To achieve this, follow these steps:
- Select the data-bound ComboBox control, and set its DataSource property to Data1 (the Data control that sees the Titles table).
- Set the DataField property to the name of the field you want to update in the Database, which is the PubID field.
The PubID field is also the field that should be displayed on the data-bound ComboBox control. But the user now sees IDs in the Combo Box, not their names.
The data-bound ComboBox has one more property, Bound Column, which is the name of the field in its own RowSource that links the field displayed in the list with the actual field to which it is bound. The value of the BoundColumn property must be an index field so that the JET engine can locate it quickly. Set the BoundColumn property to the PubID field and run the application again. This time you’ll see the name of the current book’ s publisher in the ComboBox. If you select another publisher’s name in the list, the new publisher’s ID will be stored in the current record.
With the help of the DataSource, DataField, and BoundColumn properties, you can use the data-bound ComboBox control as a lookup table in your applications to let the user select meaningful field values, and at the same time, store key values in the database. All this without a single line of code! You will notice that the names of the publishers are not sorted. The data-bound Combobox control doesn’t) provide a Sorted property that would sort its entries automatically. You should probably create a new, sorted RecordSet and use it to populate the data-bound CoinboBox control.
