prevent undo in form datasheet (1 Viewer)

[grzzlo]

I have a "txtAmount" textbox with an AfterUpdate event that runs an update query on another record based on the new value of txtAmount. I need to be sure that the user can never change the value in txtAmount without this update query firing.

But if the user changes the amount, saves the record, and then hits ctrl-Z (or the undo button at the top of the screen) it will set "Amount" back to what it used to be without triggering any event as far as I can tell.

So my question is this: is there a way to either prevent the undo or to have the undo trigger an event that I can use to run another update query?

The Form_Undo event appears to be useless because it only seems to fire when the user cancels changes to a dirty record (e.g., hitting escape key), not when undo is called after the record has saved.

I was thinking maybe there would be some way to save the record such that the change couldn't be undone, but I can't figure it out. RunCommand acCmdSaveRecord doesn't work.

Anyone have any ideas?

 

[missinglinq]

...I need to be sure that the user can never change the value in txtAmount without this update query firing...

...if the user changes the amount, saves the record, and then hits ctrl-Z (or the undo button at the top of the screen) it will set "Amount" back to what it used to be...

I think you're looking at this wrong way; if the user makes a mistake he should be able to correct the mistake! What I think you need to be thinking about is how to reverse the the change brought about by the running of the Update Query. What exactly is this Query doing to 'another' Record? Perhaps it should be run from the Form_BeforeUpdate event which doesn't fire unless the first Record changes.

We could help you more, perhaps, if we knew more about what you're actually doing, here.

Linq ;0)>

 

[grzzlo]

Hi missinglinq,

Thanks for the quick reply. Let's see if I can explain myself a little better...

You're right that ideally the user should be able hit undo and the update query would fire to reverse the changes. But the "OnUndo" event doesn't work in this instance, so I'm not sure how to accomplish that. Better to prevent undo altogether then allow the value to change without firing the necessary update query. In this case, it really wouldn't be a big loss if I took away the undo option, if only I knew how. I don't think any of my users are making use of it anyway.

The form is part of a double-entry bookkeeping system. Every transaction usually has two parts, or "splits" (it can have more, but we're not worried about more complicated transactions now). For example, if I buy a pen for $10 with cash, then I enter one transaction with two splits -- one split is for the Cash account with an amount of -$10 and one split is for the Office Supplies expense account with an amount of $10.

The form in question displays all transactions for a given account. If it's showing the transactions for the Cash account, then we'll see only one line for our pen purchase with an amount of -$10. If the user changes that amount to -$8, a query will fire to set the other split associated with this transaction to $8. Then, after the record is saved, if the user hits undo, the amount changes back to -$10 and I need to fire another query to set the other split back to $10. Or I simply need to prevent the undo. The user can still correct the mistake by setting the value back to -$10 manually.

I believe running the code from Form_BeforeUpdate would still leave me with the same problem. As I mentioned, the problem occurs after the first record gets saved (which would trigger Form_BeforeUpdate) and then the user hits undo, reversing the most recently saved changes.

I'm happy to offer more explanation if needed. I can include a sample database or sample code if desired...

Thanks in advance for any advice

 

[missinglinq]

...it really wouldn't be a big loss if I took away the undo option, if only I knew how...

...if I only knew how? You mean how to keep the <Ctrl> + <Z> Shortcut from doing its thing?

Set the Form's KeyPreview Property to YES

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If Shift = acCtrlMask And KeyCode = vbKeyZ Then
 MsgBox "Sorry, but the <Ctrl> + <Z> Shortcut Cannot  Be Used on This Form!"
 KeyCode = 0
End If
End Sub

...I don't think any of my users are making use of it anyway...

If this is true, why start this thread in the first place?

Linq ;0)>

 

[grzzlo]

Thanks missinglinq, that gets me half way there. I still need to be able to prevent my users from hitting the undo button at the top of the screen (or selecting undo from the edit menu on earlier versions).

Ideally I'd like to be able to trigger an event when changes are undone or save the record such that changes can't be undone rather than preventing the user from hitting the undo button.

 

[grzzlo]

Hmmm... I was sure someone else must have come across this problem before me. I can imagine many scenarios in which it would be problematic to have the user hitting undo and changing the value of a previously saved record without being able to run code to handle that change.

Am I alone in this problem?

 

[ledroid]

I had the exact same problem when I falt here searching for a clue. Nothing found elsewhere so I made an ugly solution after trying zilions things:

Update directly your record in the database to prevent undo to revert an update on the form. Exemple:

DoCmd.SetWarnings (False)
DoCmd.RunSQL ("UPDATE membre SET amount=" & myForm.Form.amount.Value _
   & " WHERE idMembre=" & myForm.Form.IdMembre.Value & ";")
DoCmd.SetWarnings (True)

By the time, maybe you found a cleaner solution, so please post-it, or at least it could be useful to someone else falling there (ouch!)