A
Arvi Laanemets
Hi
This thread follows threads I started in forums
microsoft.public.access.forms, and microsoft.sqlserver.programming:
1. 'Overriding constraint violation message' at 21.04.2010 12:00;
2. 'Overriding constraint restriction message, when deleting record(s) from
form' at 29.04.2010 10:36.
The problem (Occurs in Access 2000 mdb as stated by MS and in Access 2007
mdb as I myself discovered):
When record operation is aborted from SQL Server, Access gets the full
information about error, but this info gets unaccessible at moment when
OnError event is triggered. The standard error message is messy, and
confusing for user.
My solution:
In Access standard module I have a function:
---------------------------------------------------
Public Function WasODBCError(DataErr As Integer, WasDirty As Boolean, WasNew
As Boolean) As Boolean
If DataErr = 3146 Then
' An ODBC Call error occurred.
If WasDirty And WasNew Then
' Entering of a new record was interrupted.
' Possible causes:
' 1) Primary key violation;
' 2) Unique key violation.
MsgBox "The record, you tried to add, wasn't saved," & vbCrLf &
"because you tried to repeat an unique value or an unique combination of
values!"
ElseIf WasDirty Then
' Editing of an existing record was interrupted.
' Possible causes:
' 1) Primary key violation;
' 2) Unique key violation.
MsgBox "Changes weren't saved," & vbCrLf & "because you tried to
repeat an unique value or an unique combination of values!"
Else
' A record deletion was tried.
' Possible causes:
' 1) Foreign key violation.
MsgBox "The record wasn't deleted," & vbCrLf & "because it was
linked to records in other tables!"
End If
WasODBCError = True
Else
WasODBCError = False
End If
End Function
---------------------------------------------------
For every form/subform which has an ODBC datasource, I have a.n OnError
event:
---------------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = IIf(WasODBCError(DataErr, Me.Dirty, Me.NewRecord),
acDataErrContinue, Response)
End Sub
---------------------------------------------------
Some explanations:
With one exception all Primary keys in my tables (and referred in remarks
for function WasODBCError) are based on single numeric Identity column. Most
of tables have additionally one unique key, which is based on one text
column in same table and optionally on a foreign key column.
An Example with a couple of tables:
tblAreas (Fields: AreaID Numeric(18,0) IDENTITY, AreaName Varcher(20), ... ,
Keys: PK=AreaID, UK=AreaName)
tblBuildings (Fields: BuildingID numeric(18,0) IDENTITY, AreaID
numeric(18,0), BuildingName varchar(30), ... , Keys: PK=BuildingID, UK =
BuildingName+AreaID, FK=AreaID>tblAreas.AreaID)
I scrapped my solution from thread 'Overriding constraint restriction
message, when deleting record(s) from form', and a MS solution from
http://support.microsoft.com/default.aspx?scid=kb;en-us;206175, because:
1. Too much glogal variables and code to manage for my taste - especially
considering that I have 9 subforms on my Main form currently;
2. In MS solution were 2 bugs. The first one made impossible to edit later
fields, which were left blank originally (and probably to clear nonblank
fields later too), and was easy to mend. The second was caused with attempt
in BeforeUpdate event to move to last record when a new record was added,
and I did foresee, that the only working solution will be another set of
global variables and another portion of code in BeforeUpdate and Current
events for every subform. It was TOO MUCH!
PS. When you look at MS solution for adding/updating closely, then you see
that they have WRONG HEADER for this article! The article is about ONERROR
event, but the header speaks about ONOPEN event
) I don't think there
are many of people, who did find this article purpousely.
Albeit MS solution wasn't good enough, it helped me immensely to understand
the situation better. Thanks to Stefan for pointing it out to me.
Arvi Laanemets
This thread follows threads I started in forums
microsoft.public.access.forms, and microsoft.sqlserver.programming:
1. 'Overriding constraint violation message' at 21.04.2010 12:00;
2. 'Overriding constraint restriction message, when deleting record(s) from
form' at 29.04.2010 10:36.
The problem (Occurs in Access 2000 mdb as stated by MS and in Access 2007
mdb as I myself discovered):
When record operation is aborted from SQL Server, Access gets the full
information about error, but this info gets unaccessible at moment when
OnError event is triggered. The standard error message is messy, and
confusing for user.
My solution:
In Access standard module I have a function:
---------------------------------------------------
Public Function WasODBCError(DataErr As Integer, WasDirty As Boolean, WasNew
As Boolean) As Boolean
If DataErr = 3146 Then
' An ODBC Call error occurred.
If WasDirty And WasNew Then
' Entering of a new record was interrupted.
' Possible causes:
' 1) Primary key violation;
' 2) Unique key violation.
MsgBox "The record, you tried to add, wasn't saved," & vbCrLf &
"because you tried to repeat an unique value or an unique combination of
values!"
ElseIf WasDirty Then
' Editing of an existing record was interrupted.
' Possible causes:
' 1) Primary key violation;
' 2) Unique key violation.
MsgBox "Changes weren't saved," & vbCrLf & "because you tried to
repeat an unique value or an unique combination of values!"
Else
' A record deletion was tried.
' Possible causes:
' 1) Foreign key violation.
MsgBox "The record wasn't deleted," & vbCrLf & "because it was
linked to records in other tables!"
End If
WasODBCError = True
Else
WasODBCError = False
End If
End Function
---------------------------------------------------
For every form/subform which has an ODBC datasource, I have a.n OnError
event:
---------------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = IIf(WasODBCError(DataErr, Me.Dirty, Me.NewRecord),
acDataErrContinue, Response)
End Sub
---------------------------------------------------
Some explanations:
With one exception all Primary keys in my tables (and referred in remarks
for function WasODBCError) are based on single numeric Identity column. Most
of tables have additionally one unique key, which is based on one text
column in same table and optionally on a foreign key column.
An Example with a couple of tables:
tblAreas (Fields: AreaID Numeric(18,0) IDENTITY, AreaName Varcher(20), ... ,
Keys: PK=AreaID, UK=AreaName)
tblBuildings (Fields: BuildingID numeric(18,0) IDENTITY, AreaID
numeric(18,0), BuildingName varchar(30), ... , Keys: PK=BuildingID, UK =
BuildingName+AreaID, FK=AreaID>tblAreas.AreaID)
I scrapped my solution from thread 'Overriding constraint restriction
message, when deleting record(s) from form', and a MS solution from
http://support.microsoft.com/default.aspx?scid=kb;en-us;206175, because:
1. Too much glogal variables and code to manage for my taste - especially
considering that I have 9 subforms on my Main form currently;
2. In MS solution were 2 bugs. The first one made impossible to edit later
fields, which were left blank originally (and probably to clear nonblank
fields later too), and was easy to mend. The second was caused with attempt
in BeforeUpdate event to move to last record when a new record was added,
and I did foresee, that the only working solution will be another set of
global variables and another portion of code in BeforeUpdate and Current
events for every subform. It was TOO MUCH!
PS. When you look at MS solution for adding/updating closely, then you see
that they have WRONG HEADER for this article! The article is about ONERROR
event, but the header speaks about ONOPEN event
) I don't think thereare many of people, who did find this article purpousely.
Albeit MS solution wasn't good enough, it helped me immensely to understand
the situation better. Thanks to Stefan for pointing it out to me.
Arvi Laanemets