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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.