To create a combo box with an entry on a form of your own, follow these steps:

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.

The other properties for this control don't matter. We purposely left RowSource blank; you will fill this in after you create the union query. The ColumnWidths entries of "0 in;2 in" will make the first column, which will hold the ArtistID, hidden from the user. Only the second column, with the ArtistName (or ), will show.

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

Create a new query that will supply the values for the combo box control. Click on Close when you are prompted to add a table. Switch to SQL view by selecting Query → SQL Specific → Union. For the frmAlbums sample form, enter:

SELECT ArtistID, ArtistName

FROM tblArtists

UNION

SELECT "",""

FROM tblArtists

ORDER BY ArtistName;

Save the query and close it. In this example, we saved the query as qryArtists.

Open the form again in design view, and select the name of the query you created in Steps 2 through 3 in the RowSource property of the combo box.

Select [Event Procedure] in the combo box AfterUpdate property, click the "..." button, and enter the following code:

Private Sub cboArtistID_AfterUpdate( )

If cboArtistID = "" Then

ArtistID = Null

Else

ArtistID = cboArtistID

End If

End Sub

Select [Event Procedure] in the form's OnCurrent property, click the "..." button, and enter the following code:

Private Sub Form_Current( )

If IsNull(ArtistID) Then

cboArtistID = ""

Else

cboArtistID = ArtistID

End If

End Sub

Run the form. You should now be able to select from the list of values for the combo box. Null values will be entered in the ArtistID field in the table for those items, and as you scroll through the form they will show up as .

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 (see Figure 1-32). Select this item from the list and a null value will be entered into the underlying ArtistID long integer field.

Figure 1-32. The Artist combo box with an item

Discussion

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 "",""

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