Draneer Code Library

Combo Box - Handling Not In List

In this sample, a combo (cboUnit) has Limit To List set to Yes. The combo uses Value List (vl), Units, and the system must trap not in list items and allow the user to add them to the vl.

=========================================================================================================
Private Sub cboUnit_NotInList(NewData As String, Response As Integer)
  Dim Msg As String
  '---- Handle selectively adding a new value list item.
  On Error GoTo Handle_NotInList_Error
  '---- Exit this subroutine if the combo box was cleared.
  If NewData = "" Then Exit Sub
  '---- Confirm that the user wants to add the new customer.
  Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
  Msg = Msg & "Do you want to add it?"
  If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    '---- If the user chose not to add a customer, set the Response _
          argument to suppress an error message and undo changes.
    Response = acDataErrContinue
    '---- Display a customized message.
    MsgBox "Please try again."
  Else
    '---- If the user chose to add a new value to the value list.
    filt = "insert into tblValueListValues(fldValueListName,fldValueListValue ) " & _
           "values('Units','" & NewData & "')"
    DoCmd.SetWarnings False
    DoCmd.RunSQL filt
    DoCmd.SetWarnings True
    '---- Set Response argument to indicate that new data is being added.
    Response = acDataErrAdded
  End If

Exit_NotInList:
  Exit Sub
Handle_NotInList_Error:
  '---- An unexpected error occurred, display the normal error message.
  MsgBox Err.Description
  '---- Set the Response argument to suppress an error message and undo _
        changes.
  Response = acDataErrContinue

End Sub
=========================================================================================================

Back to ...
Code Library Menu