Delete event not followed up with BeforeDelConfirm or AfterDelConf

[Marty]

Howdy All,

I'm setting up a form/subform combination where each of the two forms
shows data from two related tables. The problem arises when I select a
record on the Main form and press the Delete key; the logic for the Main form
sees a "Delete" event and then a "Current" event, and then stops! I had been
expecting a BeforeDelConfirm event and an AfterDelConfirm event to fire. I
was going to use the "BeforeDelConfirm" event to remove the related records
shown in the subform.

The option "Confirm Record Changes" is set to true (that is, it is
checked). I do see the proper set of events when I try to delete a single
record on a simple form (that is, with no nested subform). It "feels" like a
problem with the nested forms, but I can't pin down the problem.

Any ideas?

Thank you
Marty

 

[Allen Browne]

If all the Confirm boxes are checked, and we are talking about JET tables in
an MDB database (not an ADP proejct), you should see the BeforeDelConfirm
event after the Delete event.

Is it possible the event is firing, but doing nothing? Try adding this as
the first line in BeforeDelConfirm:
Debug.Print "Form_BeforeDelConfirm at " & Now()

 

[Marty]

Howdy Allen,

Here is how the story plays out. I'm afraid that I painted an
over-simplified picture in my description of the problem. In addition to
having a form and subform to show data from a table and a related table, I'm
trying to wrap all changes to the data in a Transaction. I'm new to this,
and am following the guidelines set out in the Access 2002 Developer's
Handbook (by Getz et al). Whenever the Form_Current event fires, I have it
call ResetData to perform the following steps: (1) commit any transaction
that may be running, (2) run queries to re-populate the form - these queries
run under the workspace that will also contain the transaction and (3) begin
a new transaction.
This poses several challenges to the series of events that normally
follows a Delete. First, repopulating the form will, (even in the absence of
a transaction) terminate the event series. Second, if the program is changed
to avoid re-populating the form, adding a transaction will cause all records
deleted from the related table to be captured as part of the transaction.
You must add logic to the event series to commit the transaction. However,
this must not happen in the Delete event, as that will (for reasons I don't
understand) also terminate the event series. It works if you run the Commit
in the AfterDelConfirm, however. My form appears to be functioning with the
following outline:

Form_Delete
- set up a flag to prevent the Current event from calling ResetData

Form_Current
- call ResetData only if a delete is not in progress

Form_BeforeDelConfirm
- check to see if there are any related records, if not then just use
standard Access delete logic (Cancel = False, exit sub)
- If there are related records:
- - confirm with the user that all records should be deleted
- - if not, set Cancel = True and exit
- - if so
- - - move through recordset deleting records,
- - - Cancel = False
- - - Response = acDataErrContinue (suppresses normal confirmation msg)

Form_AfterDelConfirm
If a transaction is running, commit it.

For what it is worth, I am entertaining the suspicion that running the
Debugger complicates things even further. I have had the event series fail
while running the debugger. Then, after restoring the data and adding your
logging logic, the same series works fine. Hmm. This is not very
convincing, I admit. But your logging suggestion has made it possible to
finish the job. Thank you very much

Marty

 

[Allen Browne]

Marty, I take it you are trying to wrap your own transaction around all the
inserts, edits, and deletes in both a main form and a subform. The goal is
to let the user make any changes to both tables (the recordsource of the
main form and of the subform), and at the end of the process, make the
decision to either commit or rollback.

If that's your goal, I have never managed to do that using normal bound
forms and subforms with LinkMasterFields/LinkSubFields. I haven't
specifically tried the ADH code, but here's my experience.

Starting with Access 2000, it is possible to open a transaction, open a
recordset within that transaction, open a form, and assign your recordset to
the Recordset property of the form. The form is now operating inside your
transaction. You can also open another recordset, and assign it to the
Recordset of the subform. However, as soon as the main form moves record,
Access reloads the subform, based on the LinkMasterFields/LinkSubFields. The
subform is no longer operating inside your transaction. Reloading it with
another Recordset at that point does not meet our original design goal of
having all operations in both the main form and subform inside the
transaction that can be rolled back.

While testing this stuff, I did find that the form was considerably less
stable than a normal bound form would be, i.e. Access would tend to blow up
(shut down by Windows.)

Even worse, I find it horrifying to think about dozens of users who all have
these forms open simultaneously with overlapping transactions that are
potentially open for hours (users get phone calls, or go to lunch without
committing their transaction.) That sounds like a recipe for huge conflict
issues, and probably an invitation for disaster. I really don't believe that
JET is up to that.

So, my suggestion would be to follow the KISS principle. My goal is always
to create software that is 100% reliable, and works consistently in every
case, for every user. While I'm not claiming to achieve that, I certainly
avoid convoluted techniques that only work if both Microsoft and I got
everything perfect, because we don't.

Ah, just noticed that you were aiming for a transaction around a single main
form record and all changes in the subform for that record. That's doable,
but not useful IMHO. The interface is less intuitive than the default
behavior of committing each record. It does not achieve the "commit all"
that some users ask for. And it still has the stablility issue and the
multi-user overlapping transaction issues. I don't see any benefit (beyond
experimentation) for the drawbacks. Just one person's opinion.

 

[Marty]

Thanks Allen,

You are correct. I was trying to wrap changes to the one record shown
(in columnar format) on the main form and changes to multiple records (shown
in datasheet format) on the subform. It seems to be working but it took far
too long to justify the effort, and at that I could not wrap a delete of both
the master record and all the related subrecords. KISS is always good
advice, but it seems especially useful here.

Marty

 

[John Vinson]

I could not wrap a delete of both
the master record and all the related subrecords.

If you have a Relationship defined between the main table and the
related table, with Cascade Deletes enabled, then you need only delete
the main record to delete all related records.

John W. Vinson[MVP]