Overriding constraint restriction message, when deleting record(s) from form

[Arvi Laanemets]

This posting follows my post "Overriding constraint restriction message"
from 21.04.2010 12:00.

Overriding constraint restriction message when changing a record, or when
adding a new record, now works - thanks link
(http://support.microsoft.com/default.aspx?scid=kb;en-us;206175) provided by
Stefan. But solution provided there didn't help when records were deleted
(My forms are continous, so there may be several records selected for
deleting, and some of them may be allowed to delete, and some not).

I tried to write for this case a function similar to the one provided by
link above, and called from OnDelete event of the form. The function seems
to work, but apparently I have problem with OnDelete event - I can't clear
deleted records from form.

The function and event are here:

------------------------------------------------------------------------
Public Function DeleRecODBC(SRO_form As Form) As Boolean
'***************************************************************
'Function: DeleRecODBC
'
'Purpose: Deletes marked records from a form based on a linked ODBC table
' when deletion isn't restricted, and traps any ODBC errors
otherwise.
'
'Arguments: SRO_Form, which refers to the form.
'
'
'Returns: always True, because exists possibility,
' that some records are deleted, and some not.
'***************************************************************

Dim errStored As Error
Dim rc As DAO.Recordset
Dim iSelTop As Integer
Dim iSelHeight As Integer
Dim i As Integer
Dim rcError As Boolean

iSelTop = SRO_form.SelTop
iSelHeight = SRO_form.SelHeight

Set rc = SRO_form.Recordset.Clone
For i = iSelTop + iSelHeight - 2 To iSelTop - 1 Step -1
' To avoid problems with absolute position when deleting records,
' the deleting is started fom marked record with highesd absolute
position
rcError = False
rc.AbsolutePosition = i
On Error GoTo DeleRecODBCErr
rc.Delete
Continue_DeleRecODBCErr:
Next i

DeleRecODBC = True
' As the result may be mixed (some records were deleted, and some not),
' the return value is irrelevant.
Exit Function

DeleRecODBCErr:
' The function failed because of an ODBC error.
For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 547
' Foreign key constraint error.
MsgBox "Deleting was restricted by foreign key!"
rcError = True
Exit For
Case Else
End Select
Next errStored

Resume Continue_DeleRecODBCErr

End Function
------------------------------------------------------------------------
The function is called when form's OnDelete event occurs:

------------------------------------------------------------------------
Private Sub Form_Delete(Cancel As Integer)
If DeleRecODBC(Me) Then
Cancel = -1
' Records wich were allowed to be deleted are gone from source
database now.
' But on the form they exists - how remove deleted records from form
now?
'Me.Requery
' Requery returns an error "Operation is not supported in
transactions".
'Me.Refresh
' Refresh does something and freezes the app for a couple of
minutes,
' and deleted records remain on the form.
End If
End Sub

 

[Dirk Goldgar]

This posting follows my post "Overriding constraint restriction message"
from 21.04.2010 12:00.

Overriding constraint restriction message when changing a record, or when
adding a new record, now works - thanks link
(http://support.microsoft.com/default.aspx?scid=kb;en-us;206175) provided
by Stefan. But solution provided there didn't help when records were
deleted (My forms are continous, so there may be several records selected
for deleting, and some of them may be allowed to delete, and some not).

I tried to write for this case a function similar to the one provided by
link above, and called from OnDelete event of the form. The function seems
to work, but apparently I have problem with OnDelete event - I can't clear
deleted records from form.

The function and event are here:

------------------------------------------------------------------------
Public Function DeleRecODBC(SRO_form As Form) As Boolean
'***************************************************************
'Function: DeleRecODBC
'
'Purpose: Deletes marked records from a form based on a linked ODBC
table
' when deletion isn't restricted, and traps any ODBC errors
otherwise.
'
'Arguments: SRO_Form, which refers to the form.
'
'
'Returns: always True, because exists possibility,
' that some records are deleted, and some not.
'***************************************************************

Dim errStored As Error
Dim rc As DAO.Recordset
Dim iSelTop As Integer
Dim iSelHeight As Integer
Dim i As Integer
Dim rcError As Boolean

iSelTop = SRO_form.SelTop
iSelHeight = SRO_form.SelHeight

Set rc = SRO_form.Recordset.Clone
For i = iSelTop + iSelHeight - 2 To iSelTop - 1 Step -1
' To avoid problems with absolute position when deleting records,
' the deleting is started fom marked record with highesd absolute
position
rcError = False
rc.AbsolutePosition = i
On Error GoTo DeleRecODBCErr
rc.Delete
Continue_DeleRecODBCErr:
Next i

DeleRecODBC = True
' As the result may be mixed (some records were deleted, and some not),
' the return value is irrelevant.
Exit Function

DeleRecODBCErr:
' The function failed because of an ODBC error.
For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 547
' Foreign key constraint error.
MsgBox "Deleting was restricted by foreign key!"
rcError = True
Exit For
Case Else
End Select
Next errStored

Resume Continue_DeleRecODBCErr

End Function
------------------------------------------------------------------------
The function is called when form's OnDelete event occurs:

------------------------------------------------------------------------
Private Sub Form_Delete(Cancel As Integer)
If DeleRecODBC(Me) Then
Cancel = -1
' Records wich were allowed to be deleted are gone from source
database now.
' But on the form they exists - how remove deleted records from
form now?
'Me.Requery
' Requery returns an error "Operation is not supported in
transactions".
'Me.Refresh
' Refresh does something and freezes the app for a couple of
minutes,
' and deleted records remain on the form.
End If
End Sub

Arvi -

I haven't been following this, but have you tried using the AfterDelConfirm
event to requery the form?

 

[Arvi Laanemets]

Hi

Arvi -

I haven't been following this, but have you tried using the
AfterDelConfirm event to requery the form?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

BeforeDeleteConfirm and AfterDeleteConfirm events aren't started at all.

Form is based on an ODBC datasource (a table in MS SQL Server database). All
restrictions on editing, adding, and deleting records are set by constraints
in MS SQL database.

There is an Access bug, that in OnDelete event nullifies all error codes
returned from ODBC datasource, and returns err.Number 3146 instead. The only
way to catch info returned from datasource, is to replace those form
operations with code operating on recordset clone, and catch possible
errors from there.

MS declares, that this is a bug in Access2000, but I have it now in
Access2007 too. And they had a solution when records are added or deleted
(follow the link in 1st posting), but not when record(s) are deleted.


Arvi Laanemets

 

[Dirk Goldgar]

BeforeDeleteConfirm and AfterDeleteConfirm events aren't started at all.

Hmm, this is tricky. If Access doesn't delete any records -- because you
cancel each Delete event in order to do the deleting yourself -- then
there's nothing to confirm, so those events don't fire. But AfterDelConfirm
is are the only event that would normally fire to let you know that the
delete process has finished and the transaction in which it was wrapped has
been committed.

The simplest solution, it seems to me, would be to provide a Delete button
on the form that you code yourself. Let the code for that button determine
which records are to be deleted, perform the delete while trapping the ODBC
errors, and afterward requery the form. You would have to set the form's
AllowDeletions property to remove any possibility that the built-in Access
delete function isn't available.

The only other solution that has occurred to me so far is to use the form's
Timer event, firing on a short interval, to determine when deletes have made
it necessary to requery the form. There are several ways to approach this,
but the idea would be either to use the Delete event to do your own
deletions (via the recordsetclone), or just to build a list (by key) of the
records to be deleted. When you've processed the last one (which you would
have determined in advance by examining SelTop and SelHeight), you would set
a flag that a Timer event procedure would recognize as meaning either "it's
time to requery the form" or "it's time to delete the list of records and
requery the form", depending on your specific approach. This is a
cumbersome solution, but I think it could be made to work.

 

[BruceL]

Form is based on an ODBC datasource (a table in MS SQL Server database). All

restrictions on editing, adding, and deleting records are set by constraints
in MS SQL database.

Might be a good case for letting MS SQL handle the deletes in a stored
procedure. Avoid the ODBC error handling totally.

 

[Arvi Laanemets]

OK, it looks like I got some solution - but not the ideal one.

1. In a standard modules declarations section, I create global variables for
every form whith is linked with ODBC datasource:

--------------------------------------------------
Global gbl_FormName_Deletion As Boolean
--------------------------------------------------

2. I initialize those global variables in Open event for my Main form (I use
an unbound multipage form as main form):

--------------------------------------------------
Public Sub Init_Globals()
' Access global variable initialization
gbl_FormName1_Deletion = False
gbl_FormName2_Deletion = False
....
End Sub
--------------------------------------------------

3. In standard module I have a function:

--------------------------------------------------
Public Function DeleRecODBC(SRO_form As Form) As Boolean
'***************************************************************
'Function: DeleRecODBC
'
'Purpose: Deletes marked records from a form based on a linked ODBC table
' when deletion isn't restricted, and traps any ODBC errors
otherwise.
'
'Arguments: SRO_Form, which refers to the form.
'
'
'Returns: True, when at least one record was deleted, False otherwise.
'***************************************************************


Dim errStored As Error
Dim rc As DAO.Recordset
Dim iSelTop As Integer
Dim iSelHeight As Integer
Dim i As Integer

DeleRecODBC = False
iSelTop = SRO_form.SelTop
iSelHeight = SRO_form.SelHeight

Set rc = SRO_form.Recordset.Clone
For i = iSelTop + iSelHeight - 2 To iSelTop - 1 Step -1
rc.AbsolutePosition = i
On Error GoTo DeleRecODBCErr
rc.Delete
' When no error was returned,
' then at least one record was deleted
DeleRecODBC = True

Continue_DeleRecODBCErr:
Next i
Exit Function

DeleRecODBCErr:
' The function failed because of an ODBC error.
For Each errStored In DBEngine.Errors
Select Case errStored.Number
Case 547
' Foreign key constraint error.
MsgBox "Deletion was restricted!"
Exit For
Case Else
End Select
Next errStored

Resume Continue_DeleRecODBCErr

End Function
--------------------------------------------------

4. This function is called from OnDelete event for any subform having an
ODBC datasource. When function returns True, according global variable is
set to True to indicate, that there was a successfull deletion on this form:

--------------------------------------------------
Private Sub Form_Delete(Cancel As Integer)
If DeleRecODBC(Me) Then
gbl_ThisFormName_Deletion = True
End If
Cancel = True
End Sub
--------------------------------------------------

5. Form's Current event checks according global variable, requeries the form
whenever discovers, that there was a successfull deletion lately, and resets
the global variable:

--------------------------------------------------
Private Sub Form_Current()
...
If gbl_ThisFormName_Deletion = True Then
gbl_ThisFormName_Deletion = False
Me.Requery
End If
End Sub
--------------------------------------------------

The only bad moment that remains unsolved is, that when user deletes some
record(s), the form isn't refreshed until user moves to another record.
Until then deleted records remain on form and are selected - so when user
gets confused, he/she can try to delete them again immediately. The OnDelete
event is activated again, but when there was a successfull deletion, then
really not same records are selected anymore, and when those aren't linked
to some table jet, those get deleted too.


Arvi Laanemets

 

[Arvi Laanemets]

The only bad moment that remains unsolved is, that when user deletes some
record(s), the form isn't refreshed until user moves to another record.
Until then deleted records remain on form and are selected - so when user
gets confused, he/she can try to delete them again immediately. The
OnDelete event is activated again, but when there was a successfull
deletion, then really not same records are selected anymore, and when
those aren't linked to some table jet, those get deleted too.

I modified OnDelete event so, that the user at least gets informed, that
there was a successfull deletion. And when I tested the solution, I
discovered, that when several records are selected, the OnDelete event is
activated separately for every selected record. I preferred to do all actual
deleting in first call. So OnDelete event for form will be like:


---------------------------------------------------
Private Sub Form_Delete(Cancel As Integer)
If gbl_ThiFormName_Deletion = False Then
If DeleRecODBC(Me) Then
gbl_ThiFormName_Deletion = True
MsgBox "There was a deletion - select some other record!"
End If
End If
Cancel = True
End Sub