Combobox - Allow a user to add values to the underlying table

This procedure allows a user to add new items to a lookup table underlying a combo box. When a user keys in a value that is not in the lookup table, a message will pop up asking if they'd like to edit the underlying table. If they answer Yes, a form will pop up to allow them to enter the new data.

1. Create a form for the underlying table. Let's call it Form1.
2. Open the combo's Properties dialog box.
3. Set the LimitToList (Data Tab) property to Yes.
4. Right click in the On Not in List (Event Tab) property and select Build.
Two lines of code will appear, with [color FF0000]your control's name[/color] substituted for the [color FF0000]X[/color].

Private Sub [color FF0000]X[/color]_NotInList(NewData As String, Response As Integer)
End Sub

5. Paste the code below between the two lines of code that appear.
6. Substitute [color FF0000]your control's name[/color] for the [color FF0000]X[/color] in the code in all 4 places.
7. Substitute the name of the form you created in step 1 for Form1.
8. Test! Test! Test!


On Error GoTo Err_[color FF0000]X[/color]_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox("Value not in lookup table. Edit table?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Form1", acNormal, , , acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_[color FF0000]X[/color]_NotInList:
Exit Sub

Err_[color FF0000]X[/color]_NotInList:
MsgBox Err.Description
Resume Exit_[color FF0000]X[/color]_NotInList