Trapping 2237

I'm trying to trap error 2237 in a combo box, when a user enters an item that isn't in the list.  The following is the After_Update code that powers the combo box.

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PersonID] = " & Me![cmbName]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    cmbCRN.Value = ""
    cmbPostcode.Value = ""

I've tried to trap 2237 in the Before_update and Change events but I still get the generic Access message.  Where should the error handler go?
Who is Participating?
The best I came up with is:

Private Sub cmbName_NotInList(NewData As String, Response As Integer)
    Response = acDataErrContinue
End Sub

No more message, but it still drops down the combo once.

For your mechanism to work, I would bind the name column and remove "Limit To List". You can still use the ID column for your search, e.g.:

    If Me.cmbName.ListIndex < 0 Then
        cmdName = Null
        Exit Sub
    End If
    ' [...]
    rs.FindFirst "PersonID = " & Me.cmbName.Column(1)

Hope this helps.

You can use the combo's NotInList event. The Response argument can be set so that the default error message is not displayed.

The more global solution for this type of problems is to use the Form_Error event to have a chance to create your own messages, or to suppress them.

Hope this helps,
Alan WarrenApplications DeveloperCommented:
The  following adds the new combo value to the database then uses the constant acDataErrAdded to suppress the error.

Alan ":0)

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

  Dim rst As DAO.Recordset
  Set rst = CurrentDb.OpenRecordset("Allergies")
  NewData = Me!cboAllergy.Text
  Response = acDataErrAdded
  With rst
      !Allergy = NewData
  End With
  Set rst = Nothing

End Sub
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

looper8Author Commented:
Thanks.  The thing about the NotInList event is that it seems to have three possible uses: acDataErrContinue (return to the combo box and open the list), acDataErrDisplay (show the standard message), acDataErrAdded (the item has been added to the list).  All I want to do is either nothing at all  - supress the error message, allow the user to move on, or to display a custom error message and allow the user to carry on.
Alan WarrenApplications DeveloperCommented:
Hi looper8

Try something like this:

Sub SomeSub()

On Error GoTo ReportError
DoCmd.SetWarnings False ' suppress warnings

' Your code here

  On Error Resume Next
  DoCmd.Hourglass False
  DoCmd.SetWarnings True
  ' Destroy instantiated objects
  Set objRs = Nothing
  Exit Sub

  Select Case Err.Number
    Case 2237   ' handle error 2237 by doing nothing
      ' Do nothing
   Case Else
      strProgressTxt = strProgressTxt & "Failed!"
      strProgressTxt = strProgressTxt & vbCr & vbCr & "The append action failed for the following reason:"
      strProgressTxt = strProgressTxt & vbCr & "Error in " & Me.Name & ".SomeSub()" _
                            & vbCr & "Error number " & CStr(Err.Number) _
                            & " was generated by " & Err.Source _
                            & vbCr & Err.Description
      ' MsgBox gmsgText, vbExclamation + vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
  End Select
  Resume ExitProcedure

End Sub
   If DataErr = 2237 Then 'Error code for required field messege.
        response = acDataErrContinue 'Tells access to continue processing.
    End If

If you wish to just have the program keep moving along then use this string...

You can also put in your custom msgbox here too...

looper8Author Commented:
Thanks.  Having seen what acDataErrContinue does (i.e. it will let the user go elsewhere) I can live with that.  
Yes, just the annoying combo droping down, but it's not too bad.
Success with you application!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.