BeginTrans/CommitTrans in form with subforms

[Dale Fye]

I've not tried this before, but in a new application I'm working on, I have a
form with a bunch of subforms. Whenever I move from the main form to the
subforms, and back, the changes to the controls on the form are saved.

What I'm thinking about is:
1. When I go to a new record on the main form, I begin a transaction.
2. If I change data on the main form or in one of the subforms, I enable
two buttons ("Cancel", and "Save") and disable the navigation buttons.
3. The Save and Cancel buttons would remain enabled until one or the other
is clicked, at which time the transactions would be committed or rolled back,
the other navigation buttons would be enabled, and Cancel and Save would be
disabled.

Does this make sense?

I've been looking for some code for these methods, but most if the code I've
found is embedded at the beginning and end of a bunch of SQL statements.

Does anyone know whether this will work with linked Sharepoint lists?

 

[Albert D. Kallal]

What I'm thinking about is:
1. When I go to a new record on the main form, I begin a transaction.
2. If I change data on the main form or in one of the subforms, I enable
two buttons ("Cancel", and "Save") and disable the navigation buttons.
3. The Save and Cancel buttons would remain enabled until one or the
other
is clicked, at which time the transactions would be committed or rolled
back,
the other navigation buttons would be enabled, and Cancel and Save would
be
disabled.

Does this make sense?

It makes sense, but transactions in access ONLY apply to reocrdsets and/or
action quieries you execute in code. In other words, when you start a
transaction, it does NOT effect forms. Of course if you were to start a
transaction, and then start editing data in forms all day, and then shutdown
ms-access...what would happen to those "pending" edits? So, keep in mind
transactions don't work for forms. Kind of wish they did..but, it not the
case.

Note that you *can* create a recordset in code, and then "bind" to the form.
And, you could do the same for a sub-form. However, when you do this, you
can't use the link master/child settings (so, your code have to manage
that). Also, what are you going to do if the user navagates to the next
reocrd without do a save?

I've been looking for some code for these methods, but most if the code
I've
found is embedded at the beginning and end of a bunch of SQL statements.

correct, as mentioned, the transactions are NOT related to forms.

Does anyone know whether this will work with linked Sharepoint lists?

No, nor does it work with sql server either.

So, one could perhaps "cobble" together some code that creates the reocrdset
that is loaded up with the correct main record, and then bind that
reocrdsset to the form. You would then also have to setup the child forms
reocrdset. There is not really a easy smooth way to do this...

 

[Dale Fye]

Nice thought, anyway.

Don't want to mess with creating and maintaining the 6 or 7 recordsets that
would be necessary for this form and it's subforms. I'll probably just copy
the data from the "current record" into a set of local tables (temp tables
that I link to in an external database). Write a couple of queries that
delete from and insert into those tables, then use those tables as the
recordsource for my form and subforms. Then, in the Save_Click event, just
Update/Insert data from the local ones into the primary tables. It will
take a dozen or so queries, but will provide the functionality that the user
wants, which is the ability to cancel all of the changes to the current
record (on the main form or subforms), with only limited modification to the
current code.

The way I prevent the user from going to another record is to use custom
navigation buttons, and disable them when I enable the Save and Cancel
buttons. I've been doing this for years, cannot remember who I got the idea
from (Doug Steele or Allen Browne maybe). I've got a subform that has all
the normal navigation buttons, plus a few others, that I just through into
the footer of many of my other forms. I use the subforms timer event to
check to see whether the parent form is dirty, and if so, enable\disable the
navigation, Save, and Cancel buttons. This works like a charm.