'Serving the Microsoft® Access
Community since 1997'

This topic has been covered by so many authors, including Microsoft themselves, and yet I continue to see postings on forums asking "how do I do it". Well, in that case I might as well write my own article. It it stops one person from asking ...

Scenario: We have a form with a combo box on it. The user clicks on the drop down arrow and lo-and-behold, the item they want is not in the list. Now what? The user decides to go ahead and type in the missing value. They then press tab and one of two things happens. (1) they are told that the item is not in the list and they must pick another or they must first go to a separate 'Add New Item' form, or (2) they are asked to verify that they want to add a new item to the list, and when they click yes the new item is added.

Which method do you think the user wants?

(NOTE: this article was written based on research using Access 2000. It should work on other versions.)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Let's build one of these 'add an item on the fly' type combo boxes. To start, I'll create a simple test database with one table and one form. In real life you will, of course have more tables.

Step 1. Create a new database and add a table called tblColours, with 2 fields; an autonumber field called lngColourID and a text (string) field called strColour.

Step 2. Create a new form and use the combo box wizard to create a combo box that uses the table tblColours as its data source. (make sure you 'hide key column')

(article continues after sponsor spot)

Step 3. Open the properties for the combo box and change its name to something logical, like cboColour.

Step 4. Make sure the property 'Limit To List' is Yes (it should be).

Step 5. On the Events tab of the combo box's properties, choose the 'On Not in List' property and create an event for it.

Step 6. Enter the following VBA code.

Private Sub cboColour_NotInList(NewData As String, Response As Integer)

    Dim strMsg as string
    strMsg="That value is not in the list. Are you sure you want to add it?"
		
    ' Prompt the user to verify they wish to add the new value.
    If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbYes Then
        ' Set the Response argument to indicate that data is being added
        Response = acDataErrAdded
        ' Add the value in the NewData argument to the source table
        CurrentDb.Execute ("INSERT INTO tblColours ( strColour ) SELECT '" & NewData & "' AS Expr1;")
    Else
        ' If user chooses Cancel, Set the Response argument to indicate to cancel the addition
        ' and suppress the error message
        Response = acDataErrContinue
        ' Undo the change
        Me.cboColour.Undo
    End If

End Sub

Test, and if all is okay, you are done.

If you would like the sample database that I created to test this (Access 2000 version only) please visit this page.

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.