Article ID: 185384
Article Last Modified on 1/23/2007
ODBC--Call failedThe 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 = acDataErrContinuein 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.
Option Explicit
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
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
124395 ACC: Cannot Trap ODBC Errors with Form OnError Property Code
Additional query words: pra trapping
Keywords: kbbug kbnofix kbprogramming KB185384