|[ Team LiB ]|
Recipe 1.12 Create a Combo Box That Allows a User to Select N/A
You'd like to be able to create a combo box that looks up items in a table and is limited to this list of items, but with the additional choice of <N/A>, which can be used to enter a null value for the field. You don't want your users to be able to enter any invalid entries, just <N/A> (or some other special code).
You can set the LimitToList property for the combo box to Yes to limit entries to those that your combo box provides and use a sorted union query to add an additional <N/A> row to the row source for the combo box. We suggest using <N/A> rather than simply N/A to force the entry to sort to the top of the combo box list. To make this work right, you'll need to make the combo box unbound and use a bit of VBA code to move values between the underlying table and the combo box.
To create a combo box with an <N/A> entry on a form of your own, follow these steps:
To see how this works using the sample database, open the frmAlbums form in the 01-12.MDB database. You can use this form to edit or add new albums to tblAlbums. Add a new album that has no single artist. For example, enter a record for Woodstock, which is a compilation of multiple artists. When you pull down the Artist combo box you will see, at the top of the list, the choice <N/A> (see Figure 1-32). Select this item from the list and a null value will be entered into the underlying ArtistID long integer field.
The key to this solution is using a union query and an unbound combo box. You use a union query—which was discussed in the Solution in Recipe 1.11—to splice together the data from two tables. This union query is different from the usual variety because it combines the values in one table with values that you are providing in the query. This is accomplished by the union query's second SELECT statement, shown here:
UNION SELECT "<N/A>","<N/A>" FROM tblArtists
Notice that this SELECT statement selects two constants from a table. These constants aren't actually stored in the tblArtists table (or anywhere else, for that matter), but you need to refer to some existing table in the SELECT statement—we used tblArtists, since that table is already referenced in the query. This part of the query creates a single row that contains <N/A> in both the bound and displayed columns and combines it with the first half of the union query. Finally, the ORDER BY clause for the query tells Access to sort the entries by ArtistName, but because < comes before any letter in the alphabet, the <N/A> entry will sort to the top. If you run this query outside of the form, it will return a datasheet with a row made up of two constants and combined with the rows from tblArtists, as shown in Figure 1-33.
It is easy to see why <N/A> 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 <N/A> 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 <N/A> 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 <N/A> 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.
1.12.4 See Also
|[ Team LiB ]|