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?
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?
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(NewDa ta As String, Response As Integer)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("A llergies")
NewData = Me!cboAllergy.Text
Response = acDataErrAdded
With rst
.AddNew
!Allergy = NewData
.Update
.Close
End With
Set rst = Nothing
End Sub
Alan ":0)
Private Sub cboAllergy_NotInList(NewDa
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("A
NewData = Me!cboAllergy.Text
Response = acDataErrAdded
With rst
.AddNew
!Allergy = NewData
.Update
.Close
End With
Set rst = Nothing
End Sub
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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°)
Success with you application!
(°v°)
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°)