Some time we would like to add new items into Combo Box while it does not previously exists.
There are several’s 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