Use NotInList Event to Add a Record to Combo Box

    Some time we would like to add new items into Combo Box while it does not previously exists.
There are severals ways to do that :


1. Using Code to Add a Record to a Table:


 With this you need to set LimitToList property of the combo box  to Yes and writing into its OnNotInList event as below

Private Sub CustomerID_NotInList(NewData As String, _
            Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_CustomerID_NotInList
    If NewData = "" Then Exit Sub
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        Response = acDataErrContinue
        MsgBox "Please try again."
    Else
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)
        ' Ask the user to input a new Customer ID.
        Msg = "Please enter a unique 5-character" & vbCr & "Customer ID."
        NewID = InputBox(Msg)
        Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        ' If the NewID already exists, ask for another new unique
        ' CustomerID
        Do Until Rs.NoMatch
           NewID = InputBox("Customer ID " & NewID & " already exists." & _
                    vbCr & vbCr & Msg, NewID & " Already Exists")
           Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
        Loop
        ' Create a new record.
        Rs.AddNew
        ' Assign the NewID to the CustomerID field.
        Rs![CustomerID] = NewID
        ' Assign the NewData argument to the CompanyName field.
        Rs![CompanyName] = NewData
        ' Save the record.
        Rs.Update
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
    End If
    Exit_CustomerID_NotInList:
       Exit Sub
    Err_CustomerID_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue
 End Sub

1. Using a Form to Add a New Record


You need to reprogram your OnNotInList event of this dropdown list as

Private Sub Des_NotInList(NewData As String, Response As Integer)
    Dim Result
    Dim Msg As String
    Dim CR As String
    CR = Chr$(13)
    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Customers form in data entry
       ' mode as a dialog form, passing the new company name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "frmDestination", , , , acAdd, acDialog, NewData
    Else
       Me.Undo
       Response = acDataErrContinue
       Exit Sub
    End If
    ' Look for the customer the user created in the Customers form.
    Result = DLookup("[DesCode]", "dbo_tblDestination", "[DesCode]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the customer was not created, set the Response argument
       ' to suppress an error message and undo changes.
       Response = acDataErrContinue
       ' Display a customized message.
       MsgBox "Please try again!"
    Else
       ' If the customer was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
End Sub

 And in OnLoad event of the form frmDestination with

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      ' If form's OpenArgs property has a value, assign the contents
      ' of OpenArgs to the CompanyName field. OpenArgs will contain
      ' a company name if this form is opened using the OpenForm
      ' method with an OpenArgs argument, as done in the Orders
      ' form's CustomerID_NotInList event procedure.
      Me![DesCode] = Me.OpenArgs
   End If
End Sub

You can see original post at Microsoft 197526 and allenbrowne.com