FYI:
This message is for the benefit of MS Access Community. I found that
this prblem has been encounterd by many but there is hardly any place
where a complete solution is posted. So I thought I should give back
to the community by posting our findings.
Thanks you all for all your help till now by posting problems and
their solutions.
~Abhijit
http://www.ececs.uc.edu/~joshiabh/Ab...ML_General.htm
Problem: 1. How to handle Primary Key violations in a bound form
without causing and ODBC error.
2. Cannot Trap Specific ODBC Errors on OnOpen Property of a Form
Members involved in the research: Joe, Jason, Loren, Abhijit
Special Thanks to Joe for researching and intelligent implementations
of these ideas!
Findings:
1. How to handle Primary Key violations in a bound form without
causing and ODBC error.
The best way we found to handle Primary Key violations in a bound form
without causing and ODBC error is to prevent user from inserting or
changing invalid data into the fields.
In our case for example, we have state and zone as composite primary
key.
The way we can stop use from entering a duplicate combination of state
and zone is as follows:
Private Sub Form_BeforeUpdate_GOOD(Cancel As Integer)
'If Me.txtFeatureCosts.OldValue <> Me.txtFeatureCosts.Value Then
If Me.comboStates.OldValue <> Me.comboStates.Value Or _
Me.comboZones.OldValue <> Me.comboZones.Value Or _
Me.NewRecord Then
If Not IsNull(DLookup("zone", "table_name", "State = """ &
comboStates & """ AND Zone = """ & comboZones & """")) Then
MsgBox "This State Zone Combination Already Exists." &
vbNewLine & "Please try a different State/Zone Combination and try
again.", vbCritical + vbOKOnly, "Duplicate State/Zone Detected"
Cancel = True
End If
End If
End Sub
OR another method is to create a recordsetclone of the query or table
on which the form is based and find if user is trying to make any
changes to the primary keys.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rc As Recordset
Set rc = Me.RecordsetClone
rc.Bookmark = Me.Bookmark
If rc.Fields(Me.comboStates.ControlSource) <> Me.comboStates.Value Or
_
rc.Fields(Me.comboZones.ControlSource) <> Me.comboZones.Value Or _
Me.NewRecord Then
'If Me.NewRecord Then
If Not IsNull(DLookup("zone", "table_name", "State = """ &
comboStates & """ AND Zone = """ & comboZones & """")) Then
MsgBox "This State Zone Combination Already Exists." &
vbNewLine & "Please try a different State/Zone Combination and try
again.", vbCritical + vbOKOnly, "Duplicate State/Zone Detected"
Cancel = True
End If
End If
Set rc = Nothing
End Sub
2. Catching the ODBC error:
This solution is priovided by Microsoft for Access 2000. Joe changed
it to suit Access 97.
Access 97 has RecordsetClone Propery whereas Access 2000 has
Recordset.Clone Propery. We hhad to make that change in the code in
the following article.
http://support.microsoft.com/default...NoWebContent=1
Basically the ODBC errors caused by Primary Key violations with linked
tables are not directly possible to catch. We have to simulate that
violation on a local recordset and catch the error thathas been caused
and give out the customised error message. But for this we have to
make a complete recordsetClone of the whole table or Query on which
the form is based on. This is an inefficient and at times impractical
solution because the whole table is replicated on the client side,
which is not desirable. I am posting the solution here. If you have
any questions please let me know.
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.RecordsetClone
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
MsgBox " No action -- standard ODBC--Call failed error."
Case 2627
MsgBox " Error caused by duplicate value in primary key."
MsgBox "You tried to enter a duplicate value " & _
"in the Primary Key."
Case 3621
MsgBox " No action -- standard ODBC command aborted error."
Case 547
MsgBox " Foreign key constraint error."
MsgBox "You violated a foreign key constraint."
Case Else
MsgBox errStored.Description
MsgBox errStored.Number
' An error not accounted for in the Select Case
' statement.
On Error GoTo 0
Resume
End Select
Next errStored
Dim MyError As Error
MsgBox Errors.count & " Of Errors Found "
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " Of Errors Found " & .Description
End With
Next MyError
SaveRecODBC = False
Resume Exit_SaveRecODBCErr
End Function
