Not_In_List Events...

You want provide Users with a list of values to be entered.  When they make a selection that is not in the list you have two choices, deny the new data or allow the new entry.  Either way, no one likes the Access generated error when the value is not in the list.
The difficult I do immediately, the impossible takes a little bit longer.
Private Sub cboAssociateID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboAssociateID_NotInList

     Dim intAnswer As Integer

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

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

Exit_cboAssociateID_NotInList:
        Exit Sub

Err_cboAssociateID_NotInList:
        MsgBox Err.Description
        Resume Exit_cboAssociateID_NotInList

End Sub
Hmm, but suppose we want to add a value that has two parts like First Name Last Name?  No need to exit where you are, we'll just handle that with the Not_In_List event as well...
Design
Tips (Main)
Home
Naming Conventions
Where are the Options?
Options for Current Database
Object Designers
Trusted Locations
Repair (or Remove) MISSING or Broken References
Export Specifications
Import Specifications
Navigation Pane Options
Setting up a Model Database
Default Buttons and Legend
Set Subdatasheet to [NONE]
Creating a Settings Table
Handling Attachments
Private Sub cboSelect_NotInList(NewData As String, Response As Integer)

    MsgBox NewData & " is not in the list, " & vbCrLf & _
        "please choose an item from the list." _
        , vbExclamation, "Not in List"
    Me.cboSelect.Undo
    Response = acDataErrContinue

End Sub
Private Sub cboTemperID_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String

    strSQL = "Add '" & NewData & "' as new Temper?"

    If MsgBox(strSQL, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
        strSQL = "INSERT INTO tlkpTemperCodes ( tcTemperID ) " & _
                    "SELECT """ & NewData & """ AS TemperID;"
                  CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

        Response = acDataErrAdded
    Else
        DoCmd.RunCommand acCmdUndo
        Response = acDataErrContinue
    End If

End Sub
With a little code...
The above turns into...
If you want to accept new values...
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.