Microsoft Access: Make a Reference Form Pop Up So You Can Add a Value When the Value You Want is Not in the Drop-Down List

Yes, there is VBA code involved. You don't have to write it though... you can just copy it.

Scenario: The user is adding a new employee to the database using the form frmEmployees. This new employee will be working in a brand new department, the Relocation department.

The user attempts to use the drop-down (combo box) to select the Relocation department, but since it's a new department, Relocation is not yet in tblDepartments, hence not available in the Department drop-down on frmEmployees.

The following set of instructions will cause a message to pop up whenever the user types a new value into the drop-down box and presses Enter or Tab. This message will ask if they want to add to the list. If they say Yes, the form associated with the table (in our example, associated with tblDepartments), will pop up and allow them to enter the new data.

1. Alter the sample code provided for you here so you replace cboDepartments with the name of your combo box and frmDepartments with the name of your form that you want to pop up.

2. Bring up the property sheet for the combo box and do the following:

3. Make sure the ‘Limit To List’ property is set to ‘Yes’. The ‘Limit To List’ property can be found on the Data tab.

4. Go to the Event tab and click inside the ‘On Not in List’ property and then click the Build button (the Build button has three dots on it), and choose Code Builder.

5. The first and last lines of code will already be there for you. Paste in the remaining lines of the code that you modified in Step 1 in between the two lines that are already there.
   
   We have color-coded the code for you:
   ----The blue lines are the ones that you don't need to copy as they should already be there for you.
   ----The green text should be replaced with the name of your combo box
   ----The pink text should be replaced with the name of the form that will pop up

6. Go to the Menu and choose File > Close and Return to Microsoft Access

7. Test!

Private Sub cboDepartments_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_cboDepartments_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmDepartments", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboDepartments_NotInList:
Exit Sub

Err_cboDepartments_NotInList:
MsgBox Err.Description
Resume Exit_cboDepartments_NotInList

End Sub