MS Access provides us a event procedure called OnNotInList. By adding some simple code to this event procedure, we can accomplish just want you want. To discover exactly how to do this, we must know if the combo box's record source is based off a Value List or Table/Query. The next two examples will show how to accomplish this in both scenarios.

For the first example, we'll assume that a combo box named City has a table as a records source. The table is also called Cities and has a autonumber field as a primary key. If a user tries to type in a city that is not yet in the combo box, a customized error message will popup asking if the user wished to add the desired city to the table. If the user says "Yes", then the city will be added to the Cities table as a new record.

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

'Suppress the default error message.

Response = acDataErrContinue

' Prompt user to verify if they wish to add a new value.

If MsgBox("The city of " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

' Set Response argument to indicate that data is being added.

'Open a recordset of the Cities Table.

Dim db As Database

Dim rstCity As Recordset

Dim sqlCities As String

Set db = CurrentDb()

sqlCities = "Select * From Cities"

Set rstCity = db.OpenRecordset(sqlCities, dbOpenDynaset)

'Add a new City with the value that is stored in the variable NewData.

rstCity.AddNew

rstCity![City] = NewData

rstCity.Update

'Inform the combo box that the desired item has been added to the list.

Response = acDataErrAdded

rstCity.Close 'Close the recordset

End If

End Sub

For the following example, we will assume that a combo box named Clothing Size uses a Value List as it's records source. Small, Medium, and Large are the current values of the list. If a user tries to type in X-Large into the combo box, a customized error message will popup asking if the user wished to add X-Large to the list of possible clothing sizes.

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

'Suppress the default error message.

Response = acDataErrContinue

'Prompt user to verify if they wish to add a new value.

If MsgBox(" " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

'Add a new Size to the list with the value from the variable NewData.

[Clothing Size].RowSource = [Clothing Size].RowSource & ";" & NewData

'Inform the combo box that the desired item has been added to the list.

Response = acDataErrAdded

End If

End Sub