If the list of values is extracted from another dataset, then instead of the DBListBox and DBComboBox controls you should use the specific DBLookupListBox or DBLookupCombo-Box components. These components are used every time you want to select for a field a record of another dataset.
For example, if you build a standard form for taking orders, the orders dataset will generally have a field hosting a number indicating the customer who made the order. Working directly with the customer number is not the most natural way; most users will prefer to work with customer names. However, in the database, the names of the customers are stored in a different table, to avoid duplicating the customer data for each order by the same customer. To get around such a situation, with local databases or small lookup tables, you can use a DBLookupComboBox control. (This technique doesn't port very well to client/server architecture with large lookup tables, as discussed in the next chapter.)
The DBLookupComboBox component can be connected to two data sources at the same time, one source containing the actual data and a second containing the display data. Basically, I've built a standard form using the ORDERS.DB tables of the DBDEMOS database, with several DBEdit controls (you can as well use the Database Form Wizard to build this plain form). The example actually uses a Query component selecting most fields of the orders table.
At this point we want to remove the standard DBEdit component connected to the customer number and replace it with a DBLookupComboBox component (and a DBText component for understanding what exactly is going on). The lookup component (and the DBText) is connected with the DataSource for the order and with the CustNo field. To let the lookup component show the information extracted from another table, CUSTOMER.DB, we need to add another table component referring to it, and new data source connected to the table.
For the program to work, you need to set several properties of the DBLookupComboBoxl component. Here is a list of the relevant values:
object DBLookupComboBoxl: TDBLookupComboBox DataField = 'CustNo' DataSource = DataSourceOrders KeyField = 'CustNo' ListField = 'Company;CustNo' ListSource = DataSourceCustomer DropDownWidth = 300 end
The first two properties determine the main connection, as usual. The other three properties determine the secondary source (ListSource), the field used for the join (KeyField), and the information to display (ListField). Besides entering the name of a single field, you can provide multiple fields, as I've done in the example. Only the first field is displayed as combo box text, but if you set a large value for the DropDownWidth property, the pull-down list of the combo box will include multiple columns of data. You can see this output in Figure 13.4.
The output of the Cust-Lookup example, with the DBLookupComboBox showing multiple fields in its drop-down list
If you set the index of the table connected with the DBLookupComboBox to the Company field, the drop-down list will show the companies in alphabetical order instead of customer-number order. This is what I've done in the example.
What about the code of this program? Well, there is none. Everything works just by setting the correct properties. The three joined data sources do not need custom code. This demonstrates that using master/detail and lookup connections can be very fast to set up and very efficient. The only real drawback is that these techniques, particularly the lookup, cannot be used when the number of records becomes too large, particularly in a networked or client/server environment. Moving hundreds of thousands of records just to make a nice-looking lookup combo box probably won't be very effective.
In Delphi 6, both the TDBLookupComboBox and TDBLookupListBox controls have a Null-ValueKey property, which indicates the shortcut that can be used to set the value to null, by calling the Clear method of the corresponding field.
Was this article helpful?