Re: Delete event not followed up with BeforeDelConfirm or AfterDel

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Marty" <Marty@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0615CED4-FAA6-476E-81EB-EB4EB28E2A6F@xxxxxxxxxxxxxxxx
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" wrote:

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" <Marty@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E916CBA-1D5D-44B2-9BB4-97D457065C66@xxxxxxxxxxxxxxxx
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.


.