Access Cookbook

Create a Combo Box That Allows a User to Select N/A

Problem

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 all sorts of “garbage” entries, just <N/A> (or some other special code).

Solution

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:

1. Create an unbound combo box that draws its records from a table. In the sample database, we created a combo box called cboArtistID on the form frmAlbums. To duplicate the combo box in the sample database, create a combo box with the properties shown in Table 1-6.

   Table 1-6. Properties for the cboArtistID combo box

   Property	Value
   Name	cboArtistID
   ControlSource
   RowSourceType	Table/Query
   RowSource
   ColumnCount	2
   ColumnHeads	No
   ColumnWidths	0 in;2 in
   BoundColumn	1
   ListRows	8
   ListWidth	2 in
   LimitToList	Yes