Sending Esc key using Visual Basic

[AshikHusein]

Is there any way of sending an escape key (Esc) on the close event of a form using VBA?

I have a problem that when the form closes if somebody accidentally closes Access, that the entries on the form get saved when they are not supposed to be saved.

I have actually done this through macros by using the sendkey method using the docmd object but this does not seem to be working on the close event of the form.

The code on close event of the form is as follows: (part of it)

If IsNull(IPO_name_str) Then
[acct_num].SetFocus
[acct_num].Value = Null
DoCmd.RunMacro "mcr_send_2_keys_esc" <- Does not work

Else
Set recset = db.OpenRecordset("SELECT * FROM tbl_init_IPO_entry_details WHERE tbl_init_IPO_entry_details.IPO_name = """ & _
IPO_name_str & """", dbOpenDynaset)

recset.Edit
recset("dummy1") = Null
recset.Update
recset.Close
Set recset = Nothing
[acct_num].SetFocus
[acct_num].Value = Null
[IPO_name].Locked = False

DoCmd.RunMacro "mcr_send_2_keys_esc" <- Does not work

End If

All code seems to be working except DoCmd.RunMacro "mcr_send_2_keys_esc"

Would appreciate help fo the above.

 

[scottn]

I think your actual problem may be that the changes to data are committed long before the form's close event fires. You can also discard changes to data from VBA by calling the form's Undo method, but a quick test reveals that calling Undo in the close event has no effect. In fact, I traced the sequence of events all the way back to the current control's exit event, and still it's too late in the process to avert commitment of the changes to the table when the form is closed.

This suggests to me that the solution you're looking for is not one that aborts the storing of changes when the form closes, but one which restores the old data when the form closes, because the new data has long since been written to the table. You could do this fairly easily -- grab a "copy" of the current record in a variant array or something, whenever the user opens the form or navigates to a different record. Then in your close event write that old version of the record back to the table if you determine that such action is appropriate.

 

[ghudson]

I would be more concerned with the user data being saved when they might not want that to happen. You should check using the forms BeforeUpdate event if the current record is Dirty and cancel the forms Close event if true. You should warn the user that their record has been modified but not saved, yet. There are many ways to do this. Searching this forum would show you alot of ways to do it. Check out this link and see how I prevent a user from closing a form and also from closing the database application if they have not checked my check box. You can easily add your own verification process but this should point you in the right direction.
Enable/Disable The Control Box X Button

 

[AshikHusein]

Thank for the input Scottn and Ghudson.

I was just exprimenting a little more. Just to calrify matters a bit more, the bound controls of the form are from two linked tables with a one to many relationship. The usr selects an IPO_name using a combo box (linked to table1) which displays some of the fields of table1 on the form, and the remaing fields are a new record from table2 (the linked field in both table1 and table2 is "IPO_name").

Since the remaining fields of table2 are for a new record, they are blank (for user input). The "IPO_name" control on the form is a bound control for a new record of table2, but selected from the "IPO_name" field of table1

Now when I press the Esc(ape) button and then close the form, the record is not saved as required. But when the Esc(ape) button is sent thru a docmd.runmacro method on the close event of the form it does not work.

So for Scotts response, it does not appear that the data is committed to the table before the form closes. Even if the entries were committed to the table before the form closes, the DoCmd.runmacro (which sends the Esc key) should delete that data before the form closes.

The fact that the all other code executes tends to make me feel that the DoCmd.RunMacro does not seem to be executing on the form_close event.(for some strange reason that I hope to find out).

I hope this was not very confusing.

 

[AshikHusein]

I took Ghudson's code to disable the x buton on the Access window.

LET THEM CLOSE EVERYTHING THE PROPER WAY!!

Thanks again Ghudson and Scottn!

 

[scottn]

Glad you found a solution. But, I think the changes really are getting written out to the table before the close event. You can build a little test form to see what I mean. Bind the form to a table and make a couple text boxes so you can dirty the form. Include a button on the form that has this code for the click event:

MsgBox Me.Dirty

Put the same code behind the form's close event. Now, open the form, dirty it by changing a value in one of the text boxes, and click the button. You'll see the form's Dirty property is True at this time. Now close the form. The Dirty property during the close event will be False (unless your access is working differently than my access ). This fact (coupled with the fact that your escape key macro, and my earlier test using the form's Undo method, both don't work), suggests pretty strongly to me that the changes have been committed by the time the close event occurs, and access considers the form "not dirty".

 

[Rich]

The only way to prevent data from being saved is to use the BeforeUpdate Event, you don't need to check if it's true though, it only fires when the Record is dirtied

The have been numerous posts here on validation, search the archives for examples.