Introduction.
This is all about the Limit to List Property setting of the Combo Box. When this property value is set to Yes, you cannot type a new value, other than what you already have, into the combo box control. The user is forced to select/type an existing item from the drop-down list of the combo box.
Typing a new value in the combo box control will trigger an error message and will force you to select an item from the existing list.
Names of Months or Weeks etc., in a Combobox, are of constant nature, and never change or addition is not required. But, names of new employees or names of Products, clients, and so on need to be added to the combo box list, as and when the need arises.
A new item can only be added to the Source Table directly through a separate Form. The user has to open the source table editing form and add a new record. Even if the user is prepared to do that the new item added to the source table will not appear on the combo box list on the form immediately. The user must close and open the form with the combo box to refresh and make the new item appear on the list. It is not a user-friendly approach and a lot of time is wasted.
An Easy Solution.
But, we can make it very easy for the user with a VBA Program. The user can type a new value into the combo box itself and with his/her permission, we can add the new item in the source table and refresh the combo box instantly.
When a new value is typed into the combo box the Limit to List property setting will run our program from the On Not in List Event Procedure. After getting confirmation from the user we can add the new item to the combo box source table and refresh the combo box.
The VBA Program.
The following program adds ProductID and Product Name into the Products Table and refreshes the cboProd Combo box to make the new item appear in the list immediately:
Private Sub cboProd_NotInList(NewData As String, Response As Integer) Dim strProd As String, strName as String Dim rst As Recordset, db As Database Dim msg as string On Error Goto cboProd_NotInList_Err 'continue without displaying error message Response = acDataErrContinue strProd = NewData msg = "Product ID: " & strProd & " Not found in List!" & vbCr & vbCr & "Add it in Source File...?" If MsgBox(msg, vbDefaultButton2 + vbYesNo + vbQuestion, "cboProd_NotinList()") = vbYes Then 'Get Product Name from User strName="" 'Repeat the loop if user presses enter key without entering a value Do While strName="" strName = InputBox("Product Name: ","cboProd_NotinList()","") Loop 'Add ProductID and Name to the source Table Set db = CurrentDb Set rst = db.OpenRecordset("Products", dbOpenDynaset) With rst .AddNew ![ProductID] = strProd ![PName] = strName .Update .Close End With 'make combobox control source empty 'in preparation to refresh the combobox Me!cboProd = Null 'refresh the combobox Me.cboProd.Requery 'now the combobox list have the new entry 'place the new code in the combobox control source Me![cboProd] = strProd Set rst = Nothing Set db = Nothing Else 'if user refuse to add the new entry into source file 'then display the error message and exit Response = acDataErrDisplay End If cboProd_NotInList_Exit: Exit Sub cboProd_NotInList_Err: MsgBox Err & " : " & Err.Description,,"cboProd_NotInList()" Resume cboProd_NotInList_Exit End Sub
The above code is run from the On Not in List Event Procedure of the cboProd Combo box.