By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

Recordset snapshot that allows editing without saving changes

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Feb 21 '14 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,628
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.
Feb 22 '14 #2

zmbd
Expert Mod 5K+
P: 5,397
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.
Feb 23 '14 #3

Expert 100+
P: 1,221
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
Feb 23 '14 #4

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Feb 23 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Feb 24 '14 #6

zmbd
Expert Mod 5K+
P: 5,397
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.
Feb 24 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,941
It actually became useful to me when I added my Cancel and Save buttons, so I'll skip that discussion for now. Thanks again.
Feb 24 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.