Download the sample database

To limit the entries in your combo box to those that you provide, set the LimitToList property for the combo box to Yes. To add an additional <N/A> row to the row source, use a sorted union query. (Use <N/A> instead of N/A so that the entry sorts to the top of the list in the combo box.) To complete the implementation, you'll make the combo box unbound and then use a bit of VBA code to move values between the underlying table and the combo box.

The sample database, CreateComboBoxWithNA.accdb, has two tables that contain information about a small music collection (tblAlbums and tblArtists), a form that demonstrates the technique you'll learn about here (frmAlbums), and a Union query that does the work for you (qryArtists).

Take a moment to study the tables in the sample database.

To manually create a combo box with an <N/A> entry, follow these steps:

1. Create a new query to supply the values for the combo box control. Click Create, and in the Other group, select Query Design. Close the Show Table dialog box without selecting a table, and then in the Query Type group, select Union. Type the following Union query, which retrieves data from all the rows in tblArtists, and adds a new row with the value <N/A> in both columns.
   
   
   VB

   Copy

   SELECT ArtistID, ArtistName
   FROM tblArtists
   
   UNION 
   
   SELECT "<N/A>","<N/A>"
   FROM tblArtists
   ORDER BY ArtistName;
   

2. The query returns results similar to those in Figure 2. Save and close the query after you run it (it already exists as qryArtists in the sample database).
   
   
   
   Figure 2. Results from the Union query
   
   
3. Open a form in Design view, and create a new ComboBox control. (The sample database includes the completed form, frmAlbums.) Use the following list to specify properties for the control, and use the name of the query that you just created as the RowSource property. Make sure to leave the ControlSource property empty; that is, leave the combo box unbound. Note that by setting the ColumnWidths property to 0in;2in, only the second column (the artist name) is visible--the ArtistID field is effectively hidden.
   
   

   Property	Value
   ControlSource
   RowSourceType	Table/Query
   RowSource	qryArtists
   ColumnCount	2
   ColumnHeads	No
   ColumnWidths	0in;2in
   BoundColumn	1
   LimitToList	Yes

4. Select the new combo box and then select the Event tab in the Properties window. In the After Update property value, select [Event Procedure], and then click the ellipsis button (…) to the right of the property to open the code editor.
   
   
5. Modify the AfterUpdate event handler so that it is similar to the following code. Replace the field and control names to match your particular declarations.
   
   
   VB

   Copy

   Private Sub cboArtistID_AfterUpdate()
       If cboArtistID = "<N/A>" Then
           ArtistID = Null
       Else
           ArtistID = cboArtistID
       End If
   End Sub
   

6. In the form designer, click the box to the left of the horizontal ruler to select the form. In the Properties window, in the On Current event property, select [Event Procedure]. Click the ellipsis button to the right of the property to create the event handler for the OnCurrent event, and then modify the event handler so that it is similar to the following code. Replace the field and control names to match your particular declarations).
   
   
   VB

   Copy

   Private Sub Form_Current()
       If IsNull(ArtistID) Then
           cboArtistID = "<N/A>"
       Else
           cboArtistID = ArtistID
       End If
   End Sub
   

7. Run the form. You should now be able to select <N/A> from the list of values in the Artist combo box. Selecting N/A enters a Null value into the underlying data, in the ArtistID field.
   
   

To experiment with these ideas, open the sample database, CreateComboBoxWithNA.accdb, and then open frmAlbums. You can use this form to edit existing albums or to add new albums to tblAlbums. For example, add an album named Woodstock, which is a compilation of artists. When you view the list in the Artist combo box you will see <N/A> at the top of the list. If you select it, the form enters a null value into the underlying ArtistID long integer field.

The keys to this solution are the union query and the unbound combo box. Although you typically use a union query to splice the data from two tables together, the union query in this example combines the values from a table with literal values that you provide in the query. In particular, you use the second Select statement in the union query.

UNION 
SELECT "<N/A>","<N/A>"
FROM tblArtists

Notice that this SELECT statement selects two constants from the tblArtists table. The specified constants are not actually stored in that table or in any other table, but you must refer to some existing table in the SELECT statement. The code example uses the tblArtists table, because that table is already referenced in the query.

This part of the query creates a single row that contains the value <N/A> in both the bound and the displayed columns, and then combines that row with the first half of the union query that contains the actual artist information.

The ORDER BY clause for the query tells Access to sort the entries by ArtistName. Since “<” 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 similar to Figure 2 that has one row with two constants followed by the rows from tblArtists.

It is easy to understand why the query includes <N/A> in the displayed column (the second column) since that is the value that you want the user to see. It is not as easy to understand why the query includes <N/A> in the first column as well.

The truth is that any value would work in the first column, as long as it did not match one of the actual values displayed in that column. In other words, this example only uses the <N/A> value for simplicity. The VBA code uses <N/A> in the first column so that it can read and set the value that the user selects. The code in the form's Current selects 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 the user selects a value in the combo box.

You might wonder why this example did not use a combo box bound to the ArtistID field in the form. You might think that it could have used the union query to add a row with a null value in the first column and <N/A> in the displayed column. Unfortunately, that solution does not work. When a combo box is set to null or even to "" it always shows a blank, even if there is a null (or "") value in a row in its bound column. The <N/A> value would not be displayed in with a null ArtistID value. Instead, the combo box would appear empty. Thus, you must use an unbound combo box and VBA code.

When you program Access forms, you will often combine the Current event of the form and the AfterUpdate event of a bound or unbound control. The form needs both events to keep the user interface in sync with data as the user edits the data and scrolls through the form.

• Access 2007 Sample: Creating Combo Boxes Allowing Users to Select N/A
  
  
• Access Developer Portal
  
  
• Access 2007 Resource Center
  
  
• Visual Basic Programmer's Guide
  
  
• Union Operation
  
  
• Another Take on Adding Null Rows using a Combo Box (from the book, Mastering Access 2002, Premium Edition)
  
  
• Form.Current Event
  
  
• ComboBox.AfterUpdate event