Trapping 2237

Posted on 2006-04-10
Last Modified: 2012-05-05
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?
Question by:looper8
    LVL 58

    Expert Comment


    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,
    LVL 26

    Expert Comment

    by:Alan Warren
    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
    LVL 1

    Author Comment

    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.
    LVL 26

    Expert Comment

    by:Alan Warren
    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

    Assisted Solution

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

    LVL 58

    Accepted Solution

    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.
    LVL 1

    Author Comment

    Thanks.  Having seen what acDataErrContinue does (i.e. it will let the user go elsewhere) I can live with that.  
    LVL 58

    Expert Comment

    Yes, just the annoying combo droping down, but it's not too bad.
    Success with you application!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now