Undo changes (1 Viewer)

[zelarra821]

Hello people.

I wanted to know if it is possible to undo changes when you change fields.

If you are in a field, you change part or all of the content, but then cancel, what you just entered is not saved. Now, this does not happen if, by doing the same thing, you then change fields. Is it possible to go back somehow?

Thank you.

 

[MarkK]

You can try and hit the <Escape> key, or he the key combination <Ctrl>+<z>

 

[MajP]

a control has a OldValue. So until the record is saved you can have a control revert back to its previous value.

 

[zelarra821]

Sure, but that means duplicating all the fields and for all the forms you have. Can't you save the values in some VBA variable and then retrieve it? Maybe it's simpler, but I don't know how to do it.

 

[MajP]

Public Function Rollback()
  Dim ctrl As Access.Control
  Set ctrl = Screen.ActiveControl
  ctrl.Value = ctrl.OldValue
End Function

I have this set up in a doubleclick event. I highlight all the controls and in on double click I put
=Rollback()

 

[Isaac]

previous value is so useful!! I give Access credit for its concepts of New, Dirty, Next, and previous value and how they can all be used to create beautifully functional interfaces

 

[The_Doc_Man]

Sure, but that means duplicating all the fields and for all the forms you have. Can't you save the values in some VBA variable and then retrieve it? Maybe it's simpler, but I don't know how to do it.

MajP's answer is how you do it, but it appears you don't understand how a bound field works in Access.

When you navigate your form to a particular record, you start with the Form_Current event, in which Access loads the record's bound fields to TWO places - each bound control's .VALUE property and .OLDVALUE property. What you do on the form can change the .VALUE propery but no manual operation on that form touches .OLDVALUE at any time. So you don't need a separate variable, it is a control property of a bound control.

If you SAVE the record with whatever changes you made, each bound control takes its .VALUE content and puts it in the record, so you just synched the record to the controls on the form. After the SAVE finishes, there will be a new Form_Current event, but due to the SAVE, the form and record already match so this event appears to do nothing.

If you UNDO the record as a whole, each bound controls takes its .OLDVALUE content and puts it in the .VALUE content - which then matches the underlying record - so you just in effect resynced the form to the record. It looks like a Form_Current event just occurred, but it didn't. A new Form_Current event does not occur, but after an UNDO, you couldn't tell the difference anyway.

MajP's suggestion is to use a double-click event to do a selective UNDO operation. This does not affect the form as a whole so you would again not see a Form_Current event as a direct result of this proposed solution. The <ESC> key and <CTRL/Z> key have the effect of a selective UNDO as well. Again, use of these keys does not trigger a new Form_Current event.

NOTE that unbound controls DO NOT have a .OLDVALUE so this discussion does not apply to unbound controls. There, you WOULD have to keep variables for any unbound controls in order to reverse form/keyboard interactions.

 

[Pat Hartman]

I prefer to allow double-click to be used for other things since Access takes care of backing out changes.
The first time you press esc on a dirty form, the current control is reset to its .OldValue property. The second time you press esc on a dirty form, all the entire form is reset to the .OldValue properties.

Once a record is saved, the .OldValue is lost and all you have available is the saved value. If you want to back out changes to saved records, you need to create an audit process that logs all the record changes. Then you have to create a restore process to bring them back.

 

[MajP]

To be clear I am not suggesting to use the double click event. I only demonstrated using that event. I have no idea what kind of user interface the OP would need to make this useable.
Bottom line you want to be able to undo a specific control and not all controls. So, some how you have to identify which control you want to roll back. You could have a pop up or combobox to pick which to roll back. You could put an event on the right click or have a popup shortcut menu to to this. You could get complicated and log what controls you have visited. Then you could have a single undo button and start undoing in reverse order of which controls where visited. This would be more similar to other applications where you just roll back your entries.

 

[Pat Hartman]

@MajP is correct. If you want to go back and undo any previously modified control, you will need code in some event for EVERY control. The esc method works only for the current control. if you have changed several controls and decide you need to undo the change for a different control than the current one, esc won't work. If you back up three controls and hit esc there, all your changes are undone, not just the one you wanted to back out. If this is the flexibility you want then the double-click is probably the best event to give up for the cause.

 

[Cronk]

How about VBA Transaction methods, BeginTrans, CommitTrans and Rollback

 

[MajP]

How about VBA Transaction methods, BeginTrans, CommitTrans and Rollback

And how would you do that on one control/field?

 

[Isaac]

@MajP is correct. If you want to go back and undo any previously modified control, you will need code in some event for EVERY control. The esc method works only for the current control. if you have changed several controls and decide you need to undo the change for a different control than the current one, esc won't work. If you back up three controls and hit esc there, all your changes are undone, not just the one you wanted to back out. If this is the flexibility you want then the double-click is probably the best event to give up for the cause.

Well or you can just write a loop to loop through the controls and see if their text boxes and see if the value is the same as the old value and if not change it back

 

[MajP]

Well or you can just write a loop to loop through the controls and see if their text boxes and see if the value is the same as the old value and if not change it back

Again the question is not about reversing all controls since that is trivial. The question is reversing a specific visited control.

 

[Pat Hartman]

Well or you can just write a loop to loop through the controls and see if their text boxes and see if the value is the same as the old value and if not change it back

Why write code when esc, esc does what the loop is doing. The point of the double-click code posted by MajP is to allow any single previously dirtied control to be backed out individually. If you want to undo all of them, no code is required.

 

[zelarra821]

Hi guys. Excuse me for answering now.

Once a record is saved, the .OldValue is lost and all you have available is the saved value.

This is the problem. If I modify a field of a record, and go to another field of a different record, it is automatically saved and I can no longer go back with the escape.

I just wanted to ask if there was any possible solution.

The database I'm making is for someone else and they've asked me about it.

I intuited the answer (if it were possible, you would have to write a lot of code and that for each of the forms you have), but I wanted to have the opinion of people who know much more than me.

In my personal databases, I have never considered this.

I will tell you that you have to adapt to the way Access works.

Thank you so much.

 

[arnelgp]

if you change something on one record and go to another record, your best solution is using post #11.
it's an All Save or All Undo.
Using Transaction on single table | Access World Forums (access-programmers.co.uk)

 

[Pat Hartman]

This is the problem. If I modify a field of a record, and go to another field of a different record, it is automatically saved and I can no longer go back with the escape.

I just wanted to ask if there was any possible solution.

There is always a solution. It depends on how important the problem is and how much time/money you have to throw at the solution. I alluded to the solution in the reply you quoted from. If you want to be able to revert to a previous version of a saved record, the ONLY method is to create a system that creates historical versions. Then you need the complicated "revert" functionality to "restore" the past. I wouldn't even consider reverting back to anything other than the most recent save. To go further back opens up a whole other rash of problems. Again, you can do it given enough time and money. But, I don't even provide the ability to revert "one back" after a save in ANY application. No client has been willing to pay for the solution.

#11 isn't the kind of solution you are looking for. It still requires the user to consciously decide to not save. It isn't a reversion once they have moved on.

Don't think that an adequate solution is to always prompt on save. All that does is to train the users to ignore your warning messages and click yes to everything.

The best protection you have is to validate the data BEFORE it is saved. Do this in the form's BeforeUpdate event. Think of this event as the flapper at the bottom of a funnel. Every record that gets saved MUST pass through this event and it is the LAST event that runs before the record gets saved - REGARDLESS of what/who prompted the save. That way. you have the ultimate say over whether or not the data will be saved. You use the "Cancel = True" command to tell Access to not save the current record. You of course display an appropriate error message. I've written a lot about this and even posted a sample database. Ask for a link if you want it. Validate everything that you have the slightest ability to validate. For example, you can't prevent a user from entering an invalid date but you can, in many cases, prevent him from entering an illogical one. DOD can't be < DOB. Neither can be < today. Unless you are working with history, a DOB more than ~ 100 years ago, is likely to be a typo so you might want to prompt the user to confirm. For text fields, always set the AllowZeroLength String attribute to No. This will protect you from having a "blank" value when you want to make the field required. So, in an employee table, LastName is always required. That falls apart if you have allowed ZLS to be true.

A compromise is to create a historical record each time a record is saved. For simplicity, when I implement this feature, I do it in the AfterUpdate event. That means, if you want to implement this feature for an existing database, you will create the history tables and then immediately populate them with a copy of the current version of each record. If you don't have a DateChanged, then use Now() to record the datetime changed. Going forward, this will use Now() to show the data the record was changed. To "revert", you won't actually write any code, you'll just create a view only form that shows the last saved version. Let the user pick and choose each old value to bring back.

 

[Cronk]

And how would you do that on one control/field?

Maybe unbound form with update queries

 

[ebs17]

If I modify a field of a record, and go to another field of a different record, it is automatically saved and I can no longer go back with the escape.

I would like to question what the reason for an unintentional modification is.

Are the fingers faster than the head, the change is unwanted?
You can lock the form against accidental editing. Then switching to permitted editing would be a very conscious decision.

Does the user not know exactly what to enter, tries something and then changes his mind later? In addition to the above note about validating the input, you should help the user as much as possible to do the right thing, e.g. provide selection lists with only valid values.
An undo or an error message means that the user did something wrong. Nobody likes to be told something like this. So you help him to work fluently and correctly.