In a Tab Control moving thru Recordset, dirty/undo questions

[AvantGuy]

[I'm still an Access neophyte, so feel free to treat me as such ]

I'm seeking advice on how to architect my in-progress, bound form design containing a tabControl. Whichever page of the tabControl is active, the user will move through the Recordset and may make edits to some records and only view others. Via a single operation, they may wish to save all edits or undo all edits*. A bound form default behavior seems to be to save an update whenever an edited control loses focus. Because in my case dirty status must be determined at the Recordset level (controls-dirty is out of the question, seems to me, because the controls on a given tabPage are shared among all records), I'm finding several potential alternatives to handle Undo, such as:

• ​​an in-code temp array to compare against the recordset for changes ​​​​
• ​​​​use of clone recordse​​t​ for comparisons​ ​
• a TRANSACTION​​

Have I discovered all the relevant alternatives?
Any suggestions as to the pitfalls and merits of each of these?

* Additionally, I may implement a per record save if such would not conflict with the batch save/undo; this is not yet in the spec and I consider it optional at this time.
​

Thanks, Bob

 

[Ranman256]

a bound recSet updates immediatly. No undo.
Why would someone update several records then want to undo them all.
It kinda says they dont know what theyre doing.

you could use a form not connected to the main tables,
user select a record, it is copied to a 'history' table (the old rec)
then user edits a local copy, and 'saves' it, and a query updates the main table.

The 'undo' would use the history tbl to put them back.

 

[June7]

Data entry/edit is committed to table when:
1. close table/query/form
2. move to another record
3. run code to save

Transaction method might be able to facilitate what you want to do - undo all edits in a work session. I have only ever used Transaction once. For a start review https://support.office.com/en-us/article/TRANSACTION-Statement-C34383F3-573D-4432-8DDF-DA16B3111439. However, I don't understand the remark about not supported for linked tables because it is in a split db I use Transaction.

 

[AvantGuy]

...It kinda says they dont know what theyre doing.

It's more likely 'tis I the one who knows not what he's doing.

you could use a form not connected to the main tables,
user select a record, it is copied to a 'history' table (the old rec)
then user edits a local copy, and 'saves' it, and a query updates the main table.

The 'undo' would use the history tbl to put them back.

Yet another way to get to that double buffering idea...(?)

My understanding of the convention for undo is a second recordset, rather than a second table. I've gone for 3NF and my queries involve joins of many tables. This led me to look at the clone recordset idea or the VBA array (which I've think means I need to change to unbound form). If I stay with a bound form it seems use of TRANSACTION would be indicated.

 

[AvantGuy]

...Transaction method might be able to facilitate what you want to do - undo all edits in a work session....

I'm beginning to think that the TRANSACTION is the most idiomatic way of getting the Undo, assuming bound forms where all the controls are paged, as in my case with the Tab Control.

My research revealed the clone recordset method and the in-code array alternatives, but I believe this must be for unbound forms. Lots of own-code effort for that, it seems.

 

[MajP]

A clone recordset will absolutely not work. It still touches the same tables. It is not static. You change in the clone it will change in the table. You can do this with transactions, but that is not really the purpose of a transaction. If this is a multiuser database you will lock a large portion of the database for other users while a person has this long running transaction open. I do not understand why you would edit multiple records and then choose to undo them all, but if that makes sense then OK. I would think the only two options are a temp table or a disconnected ado recordset.

 

[MajP]

Also when you have a transaction open the user will have very limited options what can be done on those records. No ability to filter, sort etc. Transactions are meant when you need to ensure multiple things happen if not none happen. You need to withdraw money from table A and pay for something in table b. If A fails then B cannot happen. Here is a good article
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction

 

[AvantGuy]

A clone recordset will absolutely not work. It still touches the same tables. It is not static. You change in the clone it will change in the table.

...an insight I missed during my readings.

You can do this with transactions, but that is not really the purpose of a transaction. If this is a multiuser database you will lock a large portion of the database for other users...

This app will not require concurrency. But my habit is to avoid methods that obviate future features, so I shall take heed re Transaction.

...I would think the only two options are a temp table or a disconnected ado recordset.

Temp table, then, also as Ranman256 also alluded to.

I shall ponder my options, including limiting dirty to single records, forcing user to save or undo before stepping off current; lots of smoke by me but no fire. Thanks MajP, et al.