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 your control's name substituted for the X. Private Sub X_NotInList(NewData As String, Response As Integer) End Sub
5. Paste the code below between the two lines of code that appear. 6. Substitute your control's name for the X 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_X_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