Add an item to a combo box list, if it is not already there

Q: How can I add an item to a combo box list, if it is not already there?

A. If the combo box fetches its data from a table, you can do it by adding code in the combo box's NotInList event as shown below.

Presume that we have a table tblPersons with an AutoNumber field and a Name field, which populates the combo box cboName. Using DAO, we would solve the problem as follows

Private Sub cboAdd_NotInList(NewData As String, Response As Integer)
  'This procedure executes when the user tries to leave the combo box with an unlisted name

  'Ask the customer what to do
  If MsgBox("You have changed a name." & vbCrLf _
      & "Do you want to add this name?", _
      vbYesNo + vbQuestion) = vbYes Then    'User wants to add the name

    'Launch an insert query
    CurrentDb.Execute "INSERT INTO tblPersons (Name) VALUES ('" & NewData & "')"
    Response = acDataErrAdded       'Cause Access to requery the combo box
  Else
    Response = acDataErrDisplay     'Prevent Access from showing an error message
  End If

End Sub