Link to home
Start Free TrialLog in
Avatar of looper8
looper8

asked on

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?
Avatar of harfang
harfang
Flag of Switzerland image

Hi,

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,
(°v°)
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
      .AddNew
      !Allergy = NewData
      .Update
      .Close
  End With
     
  Set rst = Nothing

End Sub
Avatar of looper8
looper8

ASKER

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

Try something like this:



Sub SomeSub()

On Error GoTo ReportError
DoCmd.SetWarnings False ' suppress warnings
 

' Your code here

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


ReportError:
 
  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
SOLUTION
Avatar of r_johnston
r_johnston

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of looper8

ASKER

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!
(°v°)