Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Before Update Event and the ESC key.

Status
Not open for further replies.

sabloomer

Technical User
Aug 8, 2003
153
US
I am sure that this is on of those things that is too easy, that is why I am having trouble. I have a form with a sub-form to hold sales order info. On the form there is a combo box for "Order Type". This controls what types of items are available to be ordered. If the user changes the “Order Type” combo box I check if there are any records in the sub-form. If there isn’t, I allow the change without any trouble. If there are records in the sub-form I open a message box with YES/NO buttons and ask if they really want to delete the records in the sub-form. I can deal with the YES option. The NO option is causing the problem. I have all of my code in the Before Update event. I can set Cancel = TRUE and it does not write the record, but it doesn’t revert the combo box back to the original value. What is the VBA code that does the same thing as the ESC key? I don’t know what to call that so I am having trouble looking it up.

Thank You,

sabloomer
 
I have 2 ways for you to try to do ESC keys in VBA

1. Sendkeys “{ESC}”

2. Me.Undo

Good luck!
 
sabloomer

First, good thoughts on data integrity.

From reading your post, if the user states they don't want to delete the the items in the subform, you want to change the value of the combo box back to the original value.

For the "No" event, you can use the oldvalue property

Using pseudo code...

if MyAnser then
- delete subform data
else
me.ComboBox.value = me.ComboBox.oldvalue
me.ComboBox.setfocus
end if

Richard
 
Hi sabloomer,

Me.Undo will undo all changes on your form. Setting a single control to its oldvalue will undo just the change to that control.

Updating records from a form is a two stage process:

Firstly when the focus leaves (or tries to leave) an individual control whose value has been changed, but stays on the record, the updated control is written to a temporary area (subject to any action in the control's BeforeUpdate event).

Secondly when the focus moves off (or attempts to move off)a record on which there have been any changes saved as above, the Record's BeforeUpdate event fires before the update is written away properly. (Note that moving off a record always involves moving off a control first). Canceling the record update does just that. It does not undo the individual control updates which happened before - to do that you must Undo or manipulate the individual control(s) you want to. If you Undo, the record will no longer be Dirty and will not show as changed. If you back out individual changes (even all of them) the record will still be considered changed and events will happen accordingly.

Enjoy,
Tony
 
Guys Willi, and Tony. You guys are brilliant, a command so simple (control.oldvalue) that I have been looking for a solution to for ages, including storing temp vals in variables.

This will save so much time thankyou, and this is why I love this forum.

Keep up the good work ;-)



Martin
 
Hi Martin,

I find it very pleasing when someone posts to say they have found something useful in an old thread. Amongst other things, it shows that it was worth posting in the first place. I know the search facility on the site is not always as good as we would like it to be but it also shows that Tek-Tips is a useful repository full of information which can be found.

Thankyou.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top