ACC2000: Cannot Trap Specific ODBC Errors on OnOpen Property of a Form

RESOLUTION

1. Open the sample database Northwind.mdb.
2. Link to the dbo.authors table 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 then type the following line in the Declarations section if that line is not already there:

   Option Explicit
   					

6. Type or paste the following procedure into the module:

   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
      Dim errStored As Error
      Dim rc As DAO.Recordset
   
      ' Check to see if the record has changed.
      If SRO_form.Dirty Then
        Set rc = SRO_Form.Recordset.Clone
        If SRO_form.NewRecord Then
          rc.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 rc.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
          rc.Update
   
          Else
            ' This is not a new record.
            ' Set the bookmark to synchronize the record in the
            ' RecordsetClone with the record in the form.
            rc.Bookmark = SRO_form.Bookmark
            rc.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 rc.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
   
             rc.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.
             On Error Goto 0
             Resume
             
           End Select
        Next errStored
        SaveRecODBC = False
        Resume Exit_SaveRecODBCErr
   
   End Function
   					

7. Set the BeforeUpdate property of the frmAuthors form to the following event procedure:

   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
             RunCommand acCmdRecordsGoToLast
          End If
       Else
          ' If you can't update the record, cancel
          ' the BeforeUpdate event.
          Cancel = -1
      End If
   End Sub
   					

8. On the Debug menu, click Compile Northwind.
9. If no errors occur, save the form.
10. Open the frmAuthors form, and then add a new record or edit a record.
    
    When you make a change to a record, the record 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.