Not in List event

There are many examples of how to handle new entries to Combo Boxes, a.k.a. Not_In_List events.  However, the one that is not so easily found is how to handle entries that include more than just what’s in the Combo Box.  For example, you’re entering an Order from a new Client and you need more than just the Client’s name… you want to enter their address, phone number, etc.   To do that we just need to add/open a Form and add our information…

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboClientID_NotInList

        Dim intAnswer As Integer

        intAnswer = MsgBox("Would you like to add this Client to the list?", vbYesNo + vbQuestion, "Not in List")

        If intAnswer = vbYes Then
            DoCmd.RunCommand acCmdUndo
            DoCmd.OpenForm "sfrClientProfile", , , , acFormAdd, acDialog
            Response = acDataErrAdded
        Else
            DoCmd.RunCommand acCmdUndo
            Response = acDataErrContinue
        End If

Exit_cboClientID_NotInList:
        Exit Sub

Err_cboClientID_NotInList:
        MsgBox Err.Description
        Resume Exit_cboClientID_NotInList

End Sub

Note: Copy/paste everything between the Private Sub and End Sub and don’t forget to change the Form name to match your own.

 849 total views,  1 views today