Recordset snapshot that allows editing without saving changes

[Seth Schrock]

I know that I can do this using a temp table, but I'm hoping for another solution that would be easier. I want to be able to open a form (in continuous view) with the current data and play with the data without the changes being saved. This includes being able to change records. I would then have Cancel and Save buttons. The cancel button would just close the form without making any changes to the data. The save button would replace the existing data with the information that is in the form. I tried using the Snapshot recordset type in the form properties, but that doesn't allow you to make any changes to the controls. Like I said, I know that I could load the data into a temp table so that all my changes, while being "saved" wouldn't change the main table and then Cancel would just empty the temp table and Save would copy the changes to the main table.

One note... With my idea, I would only be editing records, not adding or deleting. Not sure if this makes any difference.

I just want to be able to take a picture of my data, edit the picture and then choose whether or not to save my changes without being prompted for each record, but instead as a whole recordset. Hope this makes sense.

[ADezii]

I have a radical approach which in Theory I do believe should work although I have never tried it myself and I would venture to say that no one else has either. Here goes my Logic.

1. Create an ADODB Disconnected Recordset.
2. Assign this Recordset to the Recordset Property of your Form.
3. Make as many changes as you like.
4. Should you wish to Save (Save) your most recent change(s), Reconnect the Recordset to the Data Source.
5. To discard (Cancel) ALL changes, simply leave the Recordset disconnected and Close the Connection Object.
6. Again, strictly theoretical at this point.

[zmbd]

Use a transaction workspace on the bound form.
Been doing this since ACC2003 based upon the ACC2000 model as described here: ACC2000: How to Control Bound Form Transactions in Access Databases

Of course, change all of the DAO3.6 references to the newer jet/ace type for ACC2007/2010 (Microsoft Office Acces 14 or something like that... I'm on the iPad writing now so no office!)

Works slicker than whale-poop-on-ice. (^_^)
HOWEVER, I HIGHLY ADVISE USING A COPY OR TEST DATABASE the first time thru with this... boy did I make a mess of things the first try... and I even used the Northwinds.MDB file.

[jimatqsi]

Seth,
Is this in a single-user environment? This may not be workable in a multi-user environment without locking users out of the affected rows during the "play" time.

Jim

[Seth Schrock]

This is a multi-user environment. Record locking shouldn't be a problem. Thanks-for the heads-up. I will look at both of these solutions tomorrow at work.

[Seth Schrock]

Thanks Z. That works perfectly. The instructions were very easy to follow and I've got it implemented as given in the example. Now I'll just tweak it a little so that the user can click Save or Cancel buttons and have it then do the roll-back or the commit and not prompt on closing.

Just curious, in the example given in your link, couldn't you get rid of the boolFrmSaved variable and just replace it with the boolFrmDirty variable? I didn't see anywhere in the code that the functions needed to be separated. The Form_Unload event used them separately, but I would think that instead of testing for If Me.Saved ..., just test for If Me.Dirtied. Not that it makes a huge difference, but I was just wondering if there was a reason that I was missing.

[zmbd]

Seth, glad this worked for you. I've never actually tried to improve on this code so to answer your second question IDK. (^_^) - If you want an indepth anlysis of the code you should start a new thread with link back to this one if you desire.

[Seth Schrock]

It actually became useful to me when I added my Cancel and Save buttons, so I'll skip that discussion for now. Thanks again.