• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1534
  • Last Modified:

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?
  • 3
  • 2
  • 2
  • +1
2 Solutions

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 WarrenCommented:
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
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Alan WarrenCommented:
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...

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.
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!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now