Combobox - Allow a user to add values to the underlying Value List

Have you ever set the Row Source Type of your ComboBox to a Value List then needed to add the the list in runtime? The following code will use the Not In List event of the ComboBox to give the user a choice to add new item to Value List.

In the example below "YourCombo" needs to be the actual name of your combobox.

Private Sub YourCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_YourCombo_NotInList

Dim ctl As Control
Dim strSQL As String

' Return Control object that points to combo box.
Set ctl = Me!Status
' Prompt user to verify they wish to add new value.
If MsgBox("Item is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to value list
Status.RowSource = Status.RowSource & ";" & NewData
ctl.value = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_YourCombo_NotInList:
Exit Sub

Err_YourCombo_NotInList:
MsgBox Err.Description
Resume Exit_YourCombo_NotInList

End Sub

HTH
RDH