There is an event called "NotInList" which occurs when the user enters a value in the text box portion of a combo box that isn't in the combo box list. The LimitToList property must also be set to Yes for the NotInList event to occur. You can run a macro or code form that event that creates a customized error message like the one below.

In your case, you'll want to write some code in the OnNotInList event procedure. Notice that there are two parameters in this procedure; New Data and Response.

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

End Sub

The Response parameter is used by MS Access to determine weather or not to display the default error message. There are three values the Response parameter can have.

Response = acDataErrDisplay

Response = acDataErrContinue

Response = acDataErrAdded

 acDataErrDisplay This is the default value, so you wont see it in anyone's code. This value tells MS Access to displays the default message to the user. After the user clicks "OK" on the error message dialog box, the user must still select an item from the current list of choices.

acDataErrContinue If you put this line in you code MS Access will NOT display it's generic error message. You can then create some simple code to display an error message that is more instructive. For Example,

Private Sub States_NotInList(NewData As String, Response as Integer)

Response = acDataErrContinue

MsgBox "The state you chose, " & NewData & ", is invalid. Please select a valid state from the list provided.", vbInformation

End Sub

Notice in the example above that we used the NewData parameter in the message box. This trick is possible, because MS Access automatically stores the value that the user tried to type into the combo box into the NewData parameter.

acDataErrAdded This value is used to inform MS Access that you have programmatically added the item that the user wanted to the record source of the combo box.

Extra:

Can I add the item that the user wants to the combo box's list?

Yes. By using the acDataErrContinue and the acDataErrAdded values that we learned about above, we can add new item to the list. To discover exactly how to do this, we must know if the combo box's record source is based of 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("The city of " & 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