A DB Lookup Combo Box is a drop down list from which the user can select a value. Both a combo box and a pop-up menu can help ensure that information entered in the database is accurate. Because users choose an item from a menu or a list, typing mistakes are eliminated and users can't choose information that doesn't belong in the field.
There are two types of DB Lookup Combo Boxes:
Single column - lists values taken from a single column in a table
Multi column - lists values taken from multiple columns in a table
In FlightForm, we'll use a combo box to select the customer information. The menu will display only customers that exist in the database. Users must then select one of these customers, which help to enforce the relationship constraints. Later, we'll define a pop-up menu for employee information.
To place a DB Lookup Combo Box on FlightForm:
| 1. | In the "Form Designer" window, choose "DB Lookup Combo Box" from the "Database Controls" toolbar. It is the tenth button from the top left. |
| 2. | Position the cursor below the EmployeeID column object and click the left mouse button. |
The "DB Lookup Combo Box Properties" dialog box is displayed. In the "Table and Field" panel, Flights should be listed as the table.
| 3. | From the "Data Field" drop down box, select CustomerID. |
Notice the multiple tabs across the "DB Lookup Combo Box Properties" dialog box.
| 4. | Select the "Effects" Tab |
There you will see the "Row Count:" field in the "Drop-Down". This setting indicates how far the list will drop down when the user selects it. For instance, if you only want to display a few values at one time, decrease the number count. Or, if you want to display more values, increase the number count. To display a longer list of companies we'll increase the combo box drop-down count.
| 5. | Increase the "Row Count:" from 16 to 20. |
Now you must define the settings for the table and column, which you want to "look up".
| 6. | Select the "Lookup Settings" tab from the "DB Lookup Combo Box Properties" dialog box. |
| 7. | From the "Lookup Table/View and Field" panel, select "Customer" from "List Source" > "Table Name:". The combo box will display all of the available tables and views. |
| 8. | In the same panel, select "CustomerID" from the "Key Field" drop down box. This displays data from the Customer table and will return the value from the selected column. |
| 9. | Select the "Multi-Column" radio button. |
| 10. | Click the "Select Columns" button to pull up the "Select Column(s)" dialog. |
| 11. | Choose the CustomerFirstName, CustomerLastName, and CustomerCompany columns and add each to the right panel using the arrow button. |
| 12. | Select the OK button. |
We added the customer's name and company because not all customers have a company name.
The data values from the CustomerFirstName, CustomerLastName, and CustomerCompany columns in the Customer table will be displayed in the combo box when the form is run.
The CustomerID value corresponding to the selected CustomerFirstName, CustomerLastName, and CustomerCompany value will be the value that the form uses. The relationship between the tables' Customer and Flights is directly related to the primary and foreign keys that were defined on the CustomerID columns in each table. Remember, CustomerID is a primary key in the Customer table and a foreign key in the Flights table.
We will make one more enhancement to the combo box so the data does not run together.
| 13. | Select the "Effects" tab again from the "DB Lookup Combo Box Properties" dialog. |
| 14. | From the "Show Lines" panel, place a check in the "Vertical" check box. |
| 15. | Click the OK button to return to the "Form Designer". |
There will more data displayed in the combo box, so increase the width of object.
| 16. | Use the handlebars on the DB Lookup Combo Box to drag the control and increase its width to 300 pixels. |
