ACC2000: Form Error Event Does Not Return Native SQL Server Errors in an Access Project

When you use the form Error event within a Microsoft Access project (.adp) file, the DataErr argument returns error 2757 for all data-related errors encountered on the form. Because the DataErr argument always returns error 2757, it is impossible to programmatically determine what error has occurred on the form.

This behavior occurs because Microsoft Access uses an OLE layer to communicate with SQL Server. The error returned to the form Error event is a generic OLE error. The native SQL Server errors are not passed back through this layer to the form.

You can use ActiveX Data Objects (ADO) to programmatically retrieve some error information, although you can only retrieve the error description, and not its actual number. You can parse the error description to determine the kind of error that SQL Server returned, and then take some action based on that. You can do this by using the ADO recordset RecordChangeComplete event with your form's recordset. The following steps demonstrate how to do this with the Customers form in the NorthwindCS sample project.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

1. Open the sample project NorthwindCS.adp.
2. Open the Customers form in Design view.
3. On the View menu, click Code to open the module of the form in the Visual Basic Editor.
4. Add the following code to the module:

   Option Compare Database
   Option Explicit
   
   Private WithEvents rs As ADODB.Recordset
   
   Private Sub Form_Open(Cancel As Integer)
       Set rs = Me.Recordset
   End Sub
   
   Private Sub Form_Error(DataErr As Integer, Response As Integer)
       'Disable the error that Access displays
       If DataErr = 2757 Then
           'SQL Server returned an error, we will ignore the
           'the error and handle it with the recordset event sink
           Response = acDataErrContinue
       Else
       
           'write your custom code here to handle non
           'SQL Server errors
       End If
   End Sub
   
   Private Sub rs_RecordChangeComplete(ByVal adReason As ADODB.EventReasonEnum, _
                                       ByVal cRecords As Long, ByVal pError As ADODB.Error, _
                                       adStatus As ADODB.EventStatusEnum, _
                                       ByVal pRecordset As ADODB.Recordset)
                                       
                                       
                                        
       Dim strError As String
       Dim strNewError As String
       Dim intPosition As Integer
       Dim strFieldName As String
                                       
       'Check the status of the update to see if
       'errors occurred
       If adStatus = adStatusErrorsOccurred Then
           strError = pError.Description
           If InStr(strError, "Cannot insert the value NULL into column") > 0 Then
               'Tried to insert a null into a non-nullable field
               intPosition = InStr(strError, "'")
               strFieldName = Mid(strError, intPosition + 1, _
                   InStr(intPosition + 1, strError, "'") - (intPosition + 1))
               
               strNewError = "'" & strFieldName & "' may not be null. " & _
                             "Please enter a value for '" & strFieldName & "'."
               MsgBox strNewError, vbExclamation
               
           ElseIf InStr(strError, "Non-nullable column cannot be updated to Null") > 0 Then
               strNewError = "This column may not be null."
               MsgBox strNewError, vbExclamation
               
           ElseIf _
               InStr(strError, "DELETE statement conflicted with column reference constraint") > 0 Then
               'Tried to violate referential integrity
               
               strNewError = "You cannot delete this record. It contains related records in another table."
               MsgBox strNewError, vbExclamation
               
               
           ElseIf _
               InStr(strError, "Violation of Primary Key Constraint") > 0 Or _
               InStr(strError, "Violation of Unique Key Constraint") > 0 Or _
               InStr(strError, "Cannot insert duplicate key row") > 0 Then
           
               strNewError = "You entered a duplicate value into a uniquely indexed field." & _
                             " Please enter another value."
               MsgBox strNewError, vbExclamation
               
           ElseIf _
               InStr(strError, "UPDATE statement conflicted with column check constraint") > 0 Then
           
               intPosition = InStr(strError, "column '") + 8
               strFieldName = Mid(strError, intPosition, _
                       InStrRev(strError, "'") - intPosition)
               strNewError = "You violated a check constraint on '" & strFieldName & "'."
               MsgBox strNewError, vbExclamation
           
           Else
               MsgBox strError, vbExclamation
           End If
       End If
   End Sub
   					

5. On the File menu, click Close and Return to Microsoft Access.
6. Save the form, and then close it.
7. Open the Customers form in Form view.
8. Move to a new record on the form.
9. Enter a value for the CompanyName field. Do not enter a value for the CustomerID field.
10. On the Records menu, click Save Record.

Note that you receive the following custom error message:

Microsoft has confirmed that this is a problem in Microsoft Access 2000. This problem was corrected in Microsoft Access 2002.

Steps to Reproduce the Behavior

1. CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.
   
   
2. Open the sample project NorthwindCS.adp.
3. Open the Customers form in Design view.
4. On the View menu, click Code to open the module of the form in the Visual Basic Editor.
5. Add the following code to the module:

   Private Sub Form_Error(DataErr As Integer, Response As Integer)
       Dim strError As String
       strError = AccessError(DataErr)
       MsgBox strError, , "Error " & DataErr
   End Sub
   					

6. On the File menu, click Close and Return to Microsoft Access.
7. Save the form, and then close it.
8. Open the form in Form view.
9. Move to a new record on the form.
10. Enter a value for the CompanyName field. Do not enter a value for the CustomerID field.
11. On the Records menu, click Save Record.

Note that you receive the following errors: This is followed by the actual error returned from Microsoft SQL Server: