Updating ComboBox When Not in List Is Active.
This discussion focuses on the ComboBox Limit-to-List property. When this Property Value is set to Yes, new values cannot be entered into the ComboBox Control. Allows typing or selecting values from the existing list.
Attempts to enter a new value in the ComboBox control; Access will display a message asking to select an item from the available options.
For lists that never change—such as months of the year or days of the week—this behavior is perfectly acceptable. However, if the list needs to evolve, adding new employees, products, or client names, the default approach becomes inconvenient.
Normally, the ComboBox Source Table is maintained in a separate Form. Even then, the updated value will not appear in the combo box until the Form is closed and reopened, which is both time-consuming and not very user-friendly.
An Easy Solution.
However, we can make this process much easier for the user with a small VBA program. Instead of forcing them to open a separate form, the user can simply type the new value directly into the ComboBox. With the user's permission, the program adds the new entry to the source table and refreshes the ComboBox.
This is possible when types a value that does not exist in the List; the Limit to List property triggers the ComboBox’s On Not In List Event. By writing an Event Procedure for this event, we can prompt the user for confirmation, insert the new record into the source table, and then requery the ComboBox so the new item appears immediately.
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 runs from the On-Not-in-List Event Procedure of the cboProd Combo box.
