In order to display the name of an employee instead of the ID number, you could make the
record source of your form a query joining the Orders table and the Employee table using the
Employee ID to join on.
This will work fine if all the user wants to do is browse the records and view them, but if
the user wants to edit a record or add a new record, they will encounter problems. If they go
to choose a new employee for a specific order, they need to see the employee name (and to be
restricted to that list of names), but the relevant ID number for that employee will need to be
inserted into the orders table to maintain the integrity of the database.
This is where you can use a Combo Box control on your form. This is the control that you
frequently see that provides a drop-down list for the user to select a value from. It is used
very frequently when the user changes a field value or inserts a new record because it can be
set so that only the values in the list can be used, which maintains data integrity within the
database.
View the form in Design mode and click the Design tab on the ribbon.
When the Form Design ribbon appears, click Controls in the Controls section. A menu of
icons will pop up. From the menu of icons, drag the Combo Box icon onto your form.
The Combo box wizard then appears to assist you in setting up your combo box. If you do
not wish to use it, click Cancel, and then change the properties manually if desired.
As we are using a relational field, the wizard will not cover this, so click Cancel to exit the
wizard.
Right-click the combo box control and select Properties from the pop-up. You need to
make a number of changes in the Properties window for the combo box to work correctly
with the relational field. The following property values will need to be changed:
Control Source This needs to be set to Employee ID, which is the relational field
supplying the actual ID number. This is based on the RecordSource property that you
set earlier on the form. Click the drop-down, not on the three-dot icon, and you will see
a drop-down showing the fields available based on the RecordSource property for the
form. Choose Employee ID from the list.
Column Count This defines the number of columns that will be in the drop-down.
This needs to be set to 2. The first column will be the ID number of the employee and
the second column will be the employee name.
Column Widths Type in the value0;4. This will automatically appear as 0 cm;4 cm
in the property window. This defines the widths of the two columns within the combo
box. Because the first column is the ID number and is totally meaningless to the user,
we do not want this to be visible, so the column width is set to zero. We want the user
to be able to see the actual names column for selection, so the width of that is set to
4cm.
Row Source This property defines where the combo box will get the list of values for
the drop-down. Enter this property as:
SELECT id, [first name] & " " & [last name] FROM employees;Chapter 9: Forms and Reports 101