How to UNDO all changes made a record with subrecords
- Thread starter Ugurovic
- Start date
Aha, that's the hard part.
The most basic code you will need to have in place is something like this:
Your custom record navigation buttons' click event: Load the data into each fields for a given row. (Take a look at Steven Leban's website for a sample of navigation bar)
A command button's click event: Commit the changes in fields to a given row.
You would have to trap for validation and bad data within the save button.
If you want it to work automatically, be prepared to write even *more* code; adding entry in parent bound form's Current event to update the unbound subform, using LostFocus event to ensure that data get saved for examples. If you want continuous subform or datasheet, you're SOL because they won't work correctly unbound.
The most basic code you will need to have in place is something like this:
Your custom record navigation buttons' click event: Load the data into each fields for a given row. (Take a look at Steven Leban's website for a sample of navigation bar)
A command button's click event: Commit the changes in fields to a given row.
You would have to trap for validation and bad data within the save button.
If you want it to work automatically, be prepared to write even *more* code; adding entry in parent bound form's Current event to update the unbound subform, using LostFocus event to ensure that data get saved for examples. If you want continuous subform or datasheet, you're SOL because they won't work correctly unbound.
LPurvis
AWF VIP
- Local time
- Today, 10:25
- Joined
- Jun 16, 2008
- Messages
- 1,269
There's a distinction to make.
Are you talking about an unbound form - in that not only is the form's recordsource emty but all the controls have no controlsource?
If so then why do you want that?
The ultimate goal should always be the result required - not the functionality implementation used to achieve it.
If the subform is to display data in continuous (or datasheet) view then you simply cannot have the form operate in this "entirely" unbound scenario.
A continuous form *must* be bound to a source of some type.
You are, however, entirely free to make that source either not connection to your live data - or not connected to any real data at all.
If you look in the Unbound example - the two forms opened by "Orders Table" and "Orders Recordset" display two methods of fetching subform data for display and edit - but they do not write that data back to the underlying tables unless explicitly told to do so.
That Unbound form example MDB could have been a lot more complicated. It's deliberately left like that to give users the idea of the methods without obfuscating the issues with any kind of complexity. I may, one day, create a more "real world" example - but not any time soon. (Waaay too busy).
If you want to explain your precise need - then we can offer appropriate technical responses to that need.
Cheers.
Are you talking about an unbound form - in that not only is the form's recordsource emty but all the controls have no controlsource?
If so then why do you want that?
The ultimate goal should always be the result required - not the functionality implementation used to achieve it.
If the subform is to display data in continuous (or datasheet) view then you simply cannot have the form operate in this "entirely" unbound scenario.
A continuous form *must* be bound to a source of some type.
You are, however, entirely free to make that source either not connection to your live data - or not connected to any real data at all.
If you look in the Unbound example - the two forms opened by "Orders Table" and "Orders Recordset" display two methods of fetching subform data for display and edit - but they do not write that data back to the underlying tables unless explicitly told to do so.
That Unbound form example MDB could have been a lot more complicated. It's deliberately left like that to give users the idea of the methods without obfuscating the issues with any kind of complexity. I may, one day, create a more "real world" example - but not any time soon. (Waaay too busy).
If you want to explain your precise need - then we can offer appropriate technical responses to that need.
Cheers.
Not off the top of head; I know I've looked for a custom navigation bar before and decided that Leban's example was the best one.
Yes, it is that hard and exactly why everyone else has been encouraging to flow with Access's behavior and use temporary tables which you can then wrap up in transaction before posting it to the backend.
Yes, it is that hard and exactly why everyone else has been encouraging to flow with Access's behavior and use temporary tables which you can then wrap up in transaction before posting it to the backend.
In bound forms:
Use global arrays(x,y) (for each form) where you temporarly dump the current record data in on change event.
Now when you edit some fields in the subform, and you go back to your headform (setfocus w. mouse or code) access will save the record you changed, but the old value is still stored in your global array(s).
When you press undo you execute an sql update (Use at least one unique field!) with the array data. Then make each array empty again.
Also empty the arrays on your headform save.
That should do the trick.
ps, if you use continous forms, append a data row to the array (from that form), and use a for each update event to undo.
Be sure you got an unique id from that (these) record(s) into your array(s)!
If your db is on a networrk, users will see the changes until you press undo.
Use global arrays(x,y) (for each form) where you temporarly dump the current record data in on change event.
Now when you edit some fields in the subform, and you go back to your headform (setfocus w. mouse or code) access will save the record you changed, but the old value is still stored in your global array(s).
When you press undo you execute an sql update (Use at least one unique field!) with the array data. Then make each array empty again.
Also empty the arrays on your headform save.
That should do the trick.
ps, if you use continous forms, append a data row to the array (from that form), and use a for each update event to undo.
Be sure you got an unique id from that (these) record(s) into your array(s)!
If your db is on a networrk, users will see the changes until you press undo.
Last edited:
LPurvis
AWF VIP
- Local time
- Today, 10:25
- Joined
- Jun 16, 2008
- Messages
- 1,269
That's certainly another take on it for you Ugurovic - but I'm afraid I can't recommend that it's the best option.
While holding a copy of what the data was (which is effectively what you'd be doing with such an array - and a global array at that - and one for each form? :-s) might seem functionally equivalent in the end, in the mean time you've written to the database unnecessarily, then decide to write to it again - just to get it back to where it was, and this assumes that no other user has written to it inbetween.
In a database driven application you should aim to write to the database as infrequently as possible. (And ideally not read from it unnecessarily either - though that's less critical).
I'd pretty much always look to disconnected recordsets before arrays.
They're slightly heavier on the memory - but more feature packed and inline with your data.
On the Nav buttons front - why are you wanting custom navigation buttons?
I've glanced quickly at Stephen Lebans solution. In principle it's amazingly similar to a demo of such Nav buttons I made once. (And seeing that his are on his website has pretty much helped me decide to not bother putting my demo on my examples page... I don't like to do duplicate stuff
Anyway you can download and see for yourself here - and the implementation (as with Stephen's) couldn't be simpler really. You just drop the Nav buttons form onto any bound form as a subform. No other code required.
So although his may look complex - you don't need to worry about that. Just drop the thing on and go.
(Bear in mind his will be tested - mine isn't at all, just threw it together for someone as a demo a little while back. Had I remembered about Stephen's there's no way I'd have gone to the bother
Either way - they'd presumably not help you in an unbound scenario.
Are you hoping for "unbound Nav buttons"?
Possible - but would require more setup and coding. (Truth be told I don't really like Nav buttons anyway lol).
Cheers.
While holding a copy of what the data was (which is effectively what you'd be doing with such an array - and a global array at that - and one for each form? :-s) might seem functionally equivalent in the end, in the mean time you've written to the database unnecessarily, then decide to write to it again - just to get it back to where it was, and this assumes that no other user has written to it inbetween.
In a database driven application you should aim to write to the database as infrequently as possible. (And ideally not read from it unnecessarily either - though that's less critical).
I'd pretty much always look to disconnected recordsets before arrays.
They're slightly heavier on the memory - but more feature packed and inline with your data.
On the Nav buttons front - why are you wanting custom navigation buttons?
I've glanced quickly at Stephen Lebans solution. In principle it's amazingly similar to a demo of such Nav buttons I made once. (And seeing that his are on his website has pretty much helped me decide to not bother putting my demo on my examples page... I don't like to do duplicate stuff
Anyway you can download and see for yourself here - and the implementation (as with Stephen's) couldn't be simpler really. You just drop the Nav buttons form onto any bound form as a subform. No other code required.
So although his may look complex - you don't need to worry about that. Just drop the thing on and go.
(Bear in mind his will be tested - mine isn't at all, just threw it together for someone as a demo a little while back. Had I remembered about Stephen's there's no way I'd have gone to the bother
Either way - they'd presumably not help you in an unbound scenario.
Are you hoping for "unbound Nav buttons"?
Possible - but would require more setup and coding. (Truth be told I don't really like Nav buttons anyway lol).
Cheers.
Lpurvis,
Thanks for your advice, but your solution also do not cover completely my question to undo changes to subrecords. Once changes made, how to undo and get the previous value of the record. Yes my subform and mainform is bounded, and I think it will stay like that.
Your solution is just a good example to view subrecords. My problem is also, why getting this navbuttons, can't I just undo changes made to subrecords via mainform? Via a SQL. The problem is also that I work with continous forms.
I have found some code on the net, the problem is that it undo's the changes made to mainform AND to the LAST record of the continous subform records. When there is 2 records, and you change 1st and 2nd, 1st record gets changed, 2nd one gets an undo, is there maybe a possibility to build this code for the other records too? Here under is the code with the commentary
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub ResetRecord_Click()
On Error Resume Next
Application.Echo (False)
Me!DAnalysis.SetFocus
DoCmd.RunCommand (acCmdUndo)
Me!ResetRecord.SetFocus
Application.Echo (True)
End Sub
The name of my button on the main form is "ResetRecord". When you click the ResetRecord button, I turn off Echo, so you don't see any changes happening. Then I set the focus to the subform...in my case, the subform is named DAnalysis. Then, I run the command acCmdUndo...which undoes the changes done to the form that has the focus...in this case, to my subform "DAnalysis". Finally, I reset focus to the "ResetRecord" button and turn the Echo back on.
Source: http://www.xtremevbtalk.com/archive/index.php/t-72414.html posted by J-man.
==> names has to be changed to subform and button name, that is clear...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Maybe somebody can have a look at this code and extend it to all other records of the subform.
In attachment you find my file. When record is selected, press modify-> change it-> press 'Close' -> Changes must be undo, the code must be under that button.
That was a lot of typing.
Kind regards
Thank you
Ugurovic
Thanks for your advice, but your solution also do not cover completely my question to undo changes to subrecords. Once changes made, how to undo and get the previous value of the record. Yes my subform and mainform is bounded, and I think it will stay like that.
Your solution is just a good example to view subrecords. My problem is also, why getting this navbuttons, can't I just undo changes made to subrecords via mainform? Via a SQL. The problem is also that I work with continous forms.
I have found some code on the net, the problem is that it undo's the changes made to mainform AND to the LAST record of the continous subform records. When there is 2 records, and you change 1st and 2nd, 1st record gets changed, 2nd one gets an undo, is there maybe a possibility to build this code for the other records too? Here under is the code with the commentary
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub ResetRecord_Click()
On Error Resume Next
Application.Echo (False)
Me!DAnalysis.SetFocus
DoCmd.RunCommand (acCmdUndo)
Me!ResetRecord.SetFocus
Application.Echo (True)
End Sub
The name of my button on the main form is "ResetRecord". When you click the ResetRecord button, I turn off Echo, so you don't see any changes happening. Then I set the focus to the subform...in my case, the subform is named DAnalysis. Then, I run the command acCmdUndo...which undoes the changes done to the form that has the focus...in this case, to my subform "DAnalysis". Finally, I reset focus to the "ResetRecord" button and turn the Echo back on.
Source: http://www.xtremevbtalk.com/archive/index.php/t-72414.html posted by J-man.
==> names has to be changed to subform and button name, that is clear...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Maybe somebody can have a look at this code and extend it to all other records of the subform.
In attachment you find my file. When record is selected, press modify-> change it-> press 'Close' -> Changes must be undo, the code must be under that button.
That was a lot of typing.
Kind regards
Thank you
Ugurovic
That's certainly another take on it for you Ugurovic - but I'm afraid I can't recommend that it's the best option.
While holding a copy of what the data was (which is effectively what you'd be doing with such an array - and a global array at that - and one for each form? :-s) might seem functionally equivalent in the end, in the mean time you've written to the database unnecessarily, then decide to write to it again - just to get it back to where it was, and this assumes that no other user has written to it inbetween.
In a database driven application you should aim to write to the database as infrequently as possible. (And ideally not read from it unnecessarily either - though that's less critical).
I'd pretty much always look to disconnected recordsets before arrays.
They're slightly heavier on the memory - but more feature packed and inline with your data.
....
Cheers.
I know its not the best way to do it, i use this method in access to undo. Users can not edit records until the user saved or clicked on undo on the headform (custom build function) So when an other users access the same record and presses the edit record button i've made they get a msgbox that sais a user is still editing this record.
You use the array(s) only when you edit an existing record, not when viewing or inputting a new record (on undo you then need a sql delete).
in a continous form you can edit more than one record (and use undo) by adding a new row to the array (you've made for that form) when you press undo you can update the tbl with the data in the array ( for each row (LBOUND to UBOUND))
it only writes once to the database when you changed a record in a subfrm. (which is idd unnecessarily)
the memoryload of the array(s) is not that big, because the array(s) only contain changed data records of the current headrecord and the subrecords (you can even say to only input/update changed fields if you want, but you need to code more in VBA then
)when you go to an other headrecord the array must be set empty again.
just some functions you have to write and put them under the correct event
I'm just trying to give an other solution that does the thing he wants, it just overwrites the newdata back to the olddata trough SQL!
LPurvis
AWF VIP
- Local time
- Today, 10:25
- Joined
- Jun 16, 2008
- Messages
- 1,269
There's no need for all that.
My example doesn't just *display* the subform records.
I've uploaded a slightly more efficient version just now (it looks just the same).
Download it from the same place (make sure it's the new one and that your browser hasn't cached the old download - the file will be dated today).
In the "Table" method, if you see the local version of the Orders table is loaded with the data from the live Orders table when requested. This is used to show to the user. They edit this list if they so choose.
When they click Save (and *only* then) the code held in the local copy of the table is written back to the live table. One write - and only if requested - only on those rows which have been updated.
This could be made much more singing and dancing with lots more code.
We could store the updated rows in an array, collection, recordset, string variable - anything! But the idea is to just show the concept.
You load data temporarily into the subform. And write it back en mass if required to do so.
The recordset method operates differently - but it would again simple enough to make this more comprehensive - with better checking and so on.
But it is not just about showing data - and it is more efficient for only writing that data once - and then only if required to do so.
My example doesn't just *display* the subform records.
I've uploaded a slightly more efficient version just now (it looks just the same).
Download it from the same place (make sure it's the new one and that your browser hasn't cached the old download - the file will be dated today).
In the "Table" method, if you see the local version of the Orders table is loaded with the data from the live Orders table when requested. This is used to show to the user. They edit this list if they so choose.
When they click Save (and *only* then) the code held in the local copy of the table is written back to the live table. One write - and only if requested - only on those rows which have been updated.
This could be made much more singing and dancing with lots more code.
We could store the updated rows in an array, collection, recordset, string variable - anything! But the idea is to just show the concept.
You load data temporarily into the subform. And write it back en mass if required to do so.
The recordset method operates differently - but it would again simple enough to make this more comprehensive - with better checking and so on.
But it is not just about showing data - and it is more efficient for only writing that data once - and then only if required to do so.
Similar threads
