ACC: Can't Trap Specific ODBC Errors in OnOpen Property of Form

ID: Q185384


The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97


SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

If you set the OnError property of a form to an event procedure, you cannot retrieve the description of an ODBC error in that procedure, and you cannot trap a specific ODBC error. When an ODBC error occurs, the only information that is passed to the OnError event procedure is the number of a generic error such as 3146, which corresponds to the error message:

ODBC--Call failed


CAUSE

ODBC error messages normally consist of two components. The first component is error 3146, whose description is:


   ODBC--Call failed 
The server-specific error information is contained in the second component, from which you can retrieve an error number and a description such as:

   [Microsoft][ODBC SQL Server Driver][SQL Server] <Server-specific
   error message> (#<error number>) 
If you set the OnError property of a form to an event procedure, you can trap the number of the first component of the error, but you cannot trap the number of the second component. Unless you include the line

   Response = acDataErrContinue 
in the event procedure, the server-specific information in the second part of the ODBC error appears on the screen after the code has finished running.


RESOLUTION

This resolution assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

You can create a Visual Basic for Applications procedure that uses Data Access Objects (DAO) to update a RecordsetClone based on the form and trap any error message that you receive.

DAO contains an Errors collection that you can use to trap the server- specific information in the second part of the ODBC error. When an ODBC error occurs, the first component is stored in the first element of the Errors collection, and the second component is stored in the second element.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
This example uses the BeforeUpdate event instead of the Error event to trap specific ODBC errors. To create a function that traps specific ODBC errors when the BeforeUpdate event of a form occurs, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.
  1. Open the sample database Northwind.mdb.


  2. Link to the table dbo.authors in the Pubs sample database in Microsoft SQL Server.


  3. Use the AutoForm: Columnar wizard to create a new form based on the Authors table.


  4. Save the form as frmAuthors.


  5. Create a new module, and type the following line in the Declarations section if it doesn't already exist:


  6. 
    Option Explicit 
  7. Type or paste the following procedure into the module:

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.


  8. 
           Public Function SaveRecODBC(SRO_form As Form) As Boolean
           '***************************************************************
           'Function:  SaveREcODBC
           'Purpose:   Updates a form based on a linked ODBC table
           '           and traps any ODBC errors.
           '
           'Arguments: SRO_Form, which refers to the form.
           '
           '
           'Returns:  True if successful or False if an error occurs.
           '***************************************************************
    
              On Error GoTo SaveRecODBCErr
              Dim fld As Field, ctl As Control, prp As Property
              Dim errStored As Error, RecChanged As Boolean
    
              ' Check to see if the record has changed.
              If SRO_form.Dirty Then
                If SRO_form.NewRecord Then
                  SRO_form.RecordsetClone.AddNew
                  For Each ctl In SRO_form.Controls
                    ' Check to see if it is the type of control
                    ' that has a ControlSource.
                    If ctl.ControlType = acTextBox Or _
                        ctl.ControlType = acComboBox Or _
                        ctl.ControlType = acListBox Or _
                        ctl.ControlType = acCheckBox Then
                      ' Verify that a value exists in the ControlSource.
                      If ctl.Properties("ControlSource") <> "" Then
                        ' Loop through the fields collection in the
                        ' RecordsetClone. If you find a field name
                        ' that matches the ControlSource, update the
                        ' field. If not, skip the field. This is
                        ' necessary to account for calculated controls.
    
                        For Each fld In SRO_form.RecordsetClone.Fields
                          ' Find the field and verify
                          ' that it is not Null.
                          ' If it is Null, don't add it.
                          If fld.Name = ctl.Properties("ControlSource") _
                              And Not IsNull(ctl) Then
                            fld.Value = ctl
                            ' Exit the For loop
                            ' if you have a match.
                            Exit For
                          End If
                        Next fld
    
                      End If ' End If ctl.Properties("ControlSource")
    
                    End If ' End If ctl.controltype
    
                  Next ctl
                  SRO_form.RecordsetClone.Update
    
                  Else
                    ' This is not a new record.
                    ' Set the bookmark to synchronize the record in the
                    ' RecordsetClone with the record in the form.
                    SRO_form.RecordsetClone.Bookmark = SRO_form.Bookmark
                    SRO_form.RecordsetClone.Edit
    
                    For Each ctl In SRO_form.Controls
                      ' Check to see if it is the type of control
                      ' that has a ControlSource.
                      If ctl.ControlType = acTextBox Or _
                          ctl.ControlType = acComboBox Or _
                          ctl.ControlType = acListBox Or _
                          ctl.ControlType = acCheckBox Then
    
                        ' Verify that a value exists in the
                        ' ControlSource.
                        If ctl.Properties("ControlSource") <> "" Then
    
                          ' Loop through the fields collection in the
                          ' RecordsetClone. If you find a field name
                          ' that matches the ControlSource, update the
                          ' field. If not, skip the field. This is
                          ' necessary to account for calcualted controls.
    
                           For Each fld In SRO_form.RecordsetClone.Fields
    
                             ' Find the field and make sure that the
                             ' value has changed. If it has not
                             ' changed, do not perform the update.
                             If fld.Name = ctl.Properties("ControlSource") _
                                 And fld.Value <> ctl And _
                                 Not IsNull(fld.Value <> ctl) Then
    
                               fld.Value = ctl
                               ' Exit the For loop if you have a match.
                               Exit For
                             End If
    
                           Next fld
    
                         End If ' End If ctl.Properties("ControlSource")
    
                       End If ' End If ctl.controltype
    
                     Next ctl
    
                     SRO_form.RecordsetClone.Update
    
                   End If ' End If SRO_form.NewRecord
    
                 End If ' End If SRO_form.Dirty
                 ' If function has executed successfully to this point then
                 ' set its value to True and exit.
                 SaveRecODBC = True
    
             Exit_SaveRecODBCErr:
               Exit Function
    
             SaveRecODBCErr:
               ' The function failed because of an ODBC error.
               ' Below are a list of some of the known error numbers.
               ' If you are not receiving an error in this list,
               ' add that error to the Select Case statement.
               For Each errStored In DBEngine.Errors
                 Select Case errStored.Number
                   Case 3146
                     ' No action -- standard ODBC--Call failed error.
                   Case 2627
                     ' Error caused by duplicate value in primary key.
                     MsgBox "You tried to enter a duplicate value " & _
                       "in the Primary Key."
                   Case 3621
                     ' No action -- standard ODBC command aborted error.
                   Case 547
                     ' Foreign key constraint error.
                     MsgBox "You violated a foreign key constraint."
                   Case Else
                     ' An error not accounted for in the Select Case
                     ' statement.
                     MsgBox errStored.Description & " " & errStored.Number
                   End Select
                Next errStored
                SaveRecODBC = False
                Resume Exit_SaveRecODBCErr
    
           End Function 
  9. Set the BeforeUpdate property of the frmAuthors form to the following event procedure:


  10. 
              Sub Form_BeforeUpdate (Cancel As Integer)
    
                 ' If you can save the changes to the record,
                 ' undo the changes on the form.
                 If SaveRecODBC(Me) Then
                    Me.Undo
                    ' If this is a new record, go to the last record on
                    ' the form.
                    If Me.NewRecord Then
                       ' If you are using Microsoft Access 95,
                       ' comment out the RunCommand line
                       ' and remove the apostrophe from
                       ' the following line.
                       ' DoCmd.GoToRecord , , acLast
                       RunCommand acCmdRecordsGoToLast
                    End If
                 Else
                    ' If you can't update the record, cancel
                    ' the BeforeUpdate event.
                    Cancel = -1
                End If
             End Sub 
  11. On the Run menu, click Compile Loaded Modules.


  12. If no errors occur, save the form.


  13. Open the frmAuthors form and add a new record or edit a record. When you make a change to a record, it is saved when you move to a different record. If an ODBC error occurs, you see the custom message that is based on the server-specific error, and the generic "ODBC--call failed message" is trapped.



STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 2.0 and later.


REFERENCES

For more information about other issues involving this problem, please see the following article in the Microsoft Knowledge Base:

Q124395 ACC: Cannot Trap ODBC Errors with Form OnError Property Code

Additional query words: pra trapping

Keywords : kbdta AccCon FmsEvnt PgmOthr KbVBA
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbbug


Last Reviewed: July 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.