Figure 1-33. Datasheet returned by the union query

It is easy to see why is entered in the displayed column (the second column)—that's the value you want the user to see. But why also place it in the first column? Actually, any value would work in the first column, as long as it doesn't match one of the actual values that might show up in that column. We used the same value for simplicity. This first column is used by the VBA code only for setting and reading the value selected by the user. The VBA code in the Current event of the form takes care of selecting the correct row in the combo box when a record becomes current, and the code in the AfterUpdate event of the combo box enters the appropriate value into the ArtistID field when a selection is made.

You may wonder why we didn't use a combo box bound to the ArtistID field in the form. You might think that we could have used our union query to add a row with a null value in the first column and in the displayed column. Unfortunately, this simple solution just won't work. When a combo box is set to null or even to "" it will always show a blank, even if there is a null (or "") value in a row in its bound column. The value would not show up for records where the ArtistID was null—instead, the combo box would just be blank. To work around this column, we needed to use an unbound combo box and VBA code.

The combination of using the Current event of the form and the AfterUpdate event of a control is a common pattern when programming Access forms. Both events are needed to keep the user interface of a form in sync with data as the user edits the data and scrolls through the form. This pattern is often used with bound controls too—not just with unbound controls, as demonstrated in this example.

TIP

With simple text boxes, you can use the Format property of the text box to control how nulls are displayed. For example, a text box bound to a date field could have this Format setting:

Short Date;;;""

This will automatically display the specified message for null dates. The four optional parts of the Format setting respectively control positive, negative, zero, and null values. But this technique won't work for a combo box.

See Also

To fill a combo box programmatically, see Recipe 7.5 in Chapter 7. To optimize your combo box performance, see Recipe 8.3 in Chapter 8.

1.13. Use a Query to Show the Relationship Between Employees and Supervisors

Problem

You have a table that includes information on every employee in the company, including management. You'd like to be able to store information on who supervises each employee in this same table and then be able to create a query to show this hierarchical relationship.

Solution

You can display an employee-supervisor hierarchical relationship, also known as a recursive relationship, in Access with a select query that uses a self-join to join another copy of a table to itself. This solution shows how to create the table that will store the necessary recursive information and then how to create the self-join query to list each employee and his or her supervisor.

To create the employee table and a query that displays the recursive employee-supervisor relationship, follow these steps:

Create the employee table. This table should contain both an EmployeeID field and a SupervisorID field. These fields must have the same field size. In the sample database, tblEmployees contains the EmployeeID and SupervisorID fields.