undo changes to form when closing

hey guys,

for my forms, there are 3 ways of closing it.

1) click "OK" button
2) click "Cancel" button
3) click on the x button on the top right.

what should happen is

1) click "OK" button - save changes - dirty = false
2) click "Cancel" button - undo changes
3) click on the x button on the top right. undo changes

i need your advice on the architecture i should be using.

Current Architecture

1) click "OK" button - dirty = false code in click event
2) click "Cancel" button - me.undo code in click event
3) click on the x button on the top right - i don't know how to undo the changes. me.undo doesn't work if i put it in the form unload event. i believe when the form unload event fires, the record is saved already

Thought of Architecture

1) click "OK" button - set module string variable mstrOkCancel to "Ok" --> then DoCmd.Close acForm
2) click "Cancel" button - set module string variable mstrOkCancel to "Cancel" --> then DoCmd.Close acForm
3) click on the x button on the top right - on the form LOAD, will set module string variable mstrOkCancel to "Cancel" --> then DoCmd.Close acForm. so that means the default is "Cancel"

in the form UNLOAD event:
Select Case mstrOkCancel
   Case "OK"
      code to save record
   Case "Cancel"
      code to undo record changes
End Select

Problems with Architecture

for points 2) and 3), if i put the code to undo the record changes in the UNLOAD event, Me.Undo doesn't work anymore. i'm not sure how to undo the changes.

what is the proper way for me to implement this architecture guys? thanks so much in advance!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I really think that based on all your questions here, that Access is not the development platform you should be using...
In just looking at this Q, ...this all seems way too complicated if all you want to do is close a form...
Perhaps you have over-thought this, ...or again, perhaps Access is not the correct development platform for what you are trying to do...

Your question is confusing:
You say you want three ways to close the form, but in your examples, i cant see how you are actually closing the from?

1) click "OK" button - save changes - dirty = false, *Then docmd.close
2) click "Cancel" button - undo changes, *Then Docmd.Close
3) click on the x button on the top right. undo changes. *You cannot change the way the "X" works. (and once again, ....perhaps Access is not the correct tool here)

Not that despite what you might think,there IS a difference between closing the form with docmd.Close an clicking the "X".

I am sure an expert can get you what you want here, ...but to me this all seems more complicated that it need to be...

Jeffrey CoachmanMIS LiasonCommented:
developingprogrammerAuthor Commented:
thanks Jeff for your help here!

hrmm i think i'm asking quite little from Access here already - in the sense i'm using the default behaviour of the x in the top right hand corner, and i just want to undo the changes when i click on the x in the corner.

ok i think my question can be clearer if i just put it this way

Question: how can i undo changes in the form when the user clicks the x in the top right corner?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
<how can i undo changes in the form when the user clicks the x in the top right corner? >
You cant.
,...as I stated, ...you cannot change the way the "X" works directly...

I am sure if you do something a bit heavy-handed, you may get this to work...
But *in Access* the "X" is simply designed to close the form... (as it does in every other office application)
It may as you to save, but almost never will it "Undo" edits.

If you want to close the form some other way, then create your own button(s)



As we all have stated before, the moment you start to "Fight" the default behaviors in Access, (Trying to *Force* the "X" to something it was not intended to do) you run the risk of creating more issues down the road...
(Some other unforeseen combination of circumstances, (that you will have to create custom code to deal with as well, ...ad infinitum)

So I politely ask again, ...are you quite sure that Access is the best tool here?

developingprogrammerAuthor Commented:
i see, thanks Jeff i guess what i needed to hear was like what you said -

1) clicking x saves the records
2) i cannot change this

as it turns out Access is the only tool i can use ha.

ok if that's the case then i will have to disable the x button and put the code in the button click instead. no more X for the users ha = P
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried using the Form's BeforeUpdate event to handle this? By the time you hit the Unload event your data has been saved, so there's nothing to "undo".
You could make it so that your form doesn't have a close icon on the upper right that way the user has to click on a button to close the form.  To do this, set the CloseButton property to No.
The ONE thing you need to understand about forms is that the FORM's BeforeUpdate event is the LAST event to fire before a record is saved.  That means that you can drive yourself crazy, put your code in a gazillion places, and still miss things unless you understand this one important event.

If you want to control IF a record gets saved, the BeforeUpdate event is the answer.  On forms where I want the user to do it my way (almost never), I give them a Save button.  On those forms, I define a SaveOK variable.  I set it to False in the Form's Current event (another event you should make your friend).  Then I set it to True in the click event of the Save button.  Finally we get to the BeforeUpdate event and I don't care how I got here.  I only get here if the form is dirty BTW.  The event code begins with:
If bSaveOK = True Then
    Cancel = True
    If Msgbox("Are you sure you want to exit without saving?", vbYesNo = vbYes Then
    End If
    Exit Sub
End If

Open in new window

If they didn't press the save, I give them one last chance to do it before I discard all their changes.

FYI, The Form's BeforeUpdate event is also the place to check for empty controls if you want to require values under certain conditions so you can't use the database engine to do it for you.  It is also the event to use when you need to validate dependencies between controls such as dateA must be greater than dateB.

Cancelling the BeforeUpdate event prevents the record from being saved but does not discard changes.

Using Me.Undo backs out all form changes and I never do this without asking first.  It is only polite.  I try not to bombard my users with pesky messages because they become desensitized and just blow by all of them so I pick my places and hope they'll actually read the message before pressing a button.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:

I think the author is already doing it this way, but as I suggested earlier they need to use the BeforeUpdate event to do this.
developingprogrammerAuthor Commented:
WHAO PatHartman!!! that is a SUPERB chunk of guidance you gave me!!!! omg! it's like everything is suddenly so clear now - you know that kind of feeling? whao whao whao, seriously fantastic man!!

ok it's bedtime for me now but i want to re-read what you wrote to let it sink in, but it's really a fantastic piece of guidance as to how events work together to do what i need, how the individual events work (like you sharing with me Cancel cancels the save but doesn't discard the changes) and when the do what in which event. really, really fantastic PatHartman - you have no idea how much this has triggered off in my head. it's like NOW i know how to use forms properly!! = ))

LSMConsulting thanks for you help too! you beat PatHartman to it ha and yes the beforeupdate event is something i need to use a lot more often = ))

thanks IrogSinta! yup i was playing around with forms a lot and discovered how to "magically" remove the x at the top right ha = ) really good advice! = ))

thanks everyone! will post one more wrap up comment before closing this question tomorrow = )
developingprogrammerAuthor Commented:
ha PatHartman - was re-reading your comment a bit and the funny thing i thought about was --> i WAS putting my code literally "in a gazillion places", and was still missing things!! that was the moment of epiphany for me! ha = ) this is really the "whao suddenly the air becomes so clear moment" ha = ) thanks so much for that!!
developingprogrammerAuthor Commented:
sorry for the multiple posts - but yes PatHartman, a form is ALL about saving records, validations, knowing when to roll back changes, when to save changes.

previously i was just messing around with forms with no unified understanding to it. everything was... hrmm out of control? yes absolutely ha.

you know the date A is more than date B - that triggered a huge epiphany in me too cause i was using all sorts of non-unified ways to validate this. oh my goodness. just the mess i've created with my lack of knowledge and skills ha. amazing how i even wanted to take on such a big project with so little skills ha. but without trying we'd never know right? = )

LSMConsulting i just saw your later comment = )

yup i was doing it in some sort of similar but very, very, VERY un-unified form. it's quite embarrassing and beginnerish actually ha. but with your advice and PatHartman's advice, i think tonight i've finally gained enough exp points to "levelled up". ok clicking on the new skills tab and choosing "creating forms" as the new skill for level 14 haha = )

thanks guys!!
I'm glad my post gave you an epiphany.  Some people never see the light.  you'll see their posts all over the web.  "can't control the form, grumble, grumble, grumble",  "Access is a piece of crap, grumble, grumble, grumble."  "Only way to do it is with unbound forms.  Grumble, grumble, grumble."  And on and on.  All because they never understood how to use the form's BeforeUpdate event to control IF record gets saved.  Notice I didn't say WHEN.  I said IF.  When, is not really in your hands (although you can force early saves) but IF is totally within your control.

As to removing the form "x", I don't do it unless my client specifically requests custom navigation buttons.  I generally give them the choice.  The Access way is free, the custom way will cost a few minutes per form.  it's not expensive, but it's not free either.  But, you can't get rid of the Access "x" so you always have the situation of the user bailing early.  Now that you understand how to use the BeforeUpdate event, you can control the process but you will need to add additional code to trap the form Unload event and understand the Allen Brown article that boag posted.
developingprogrammerAuthor Commented:
hi PatHartman, thanks for your help! ok i went through the Allen Browne article and understand it.

but what do you mean by i need to add additional code to trap the form Unload event? could you give me an example of what you'd add in there? so sorry if this sounds so amatuerish! and yes i totally agree with you that so many people grumble and BLAME when they should really be grumbling and blaming their own skill level ha. i think unless i know a product inside out, i have no reason to criticise it cause I could be the limiting factor ha = )

hrmm i downloaded Allen Browne's database and played around with it, but so sorry Pat i just can't figure out what i should add in the unload event. if you could guide me that would be great!! = ))
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
figure out what i should add in the unload event.
You shouldn't add anything in the Unload event, if you want to control whether the data is saved. That should be done in the Before Update event.

The Unload event fires after the form has Closed, but before it's removed from the screen. You can cancel the Unload event, but by the time Unload has fired, the form's data has already been saved. One reason to use the Unload event would be to prevent the user from closing out the form - for example, if you have a form which must be open and valid before you allow a user to open a report.
I'm pretty sure the problem is restricted to when YOU issue the close command rather than when Access does it because someone clicked the "x".

So, in the case where you add your own close button (because you removed the system "x"), just add code to check if the record is dirty and save it if it is.  THEN issue the close.

Here is the code suggested by Allen:
If Me.Dirty Then
        Me.Dirty = False
    End If
    DoCmd.Close acForm, Me.Name

Open in new window

I know some people recommend the me.Dirty = False trick but I've never actually run into the bug they say they are trying to avoid.  If you use this code, add a comment to the effect that Me.Dirty = False actually SAVES the record.  When I first came across the statement, I thought it was cancelling the save.  I don't like any code that is not clear.

DoCmd.RunCmd acCmdSaveRecord -- is pretty clear - so that's what I'll use until I can reproduce the error.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
i see LSMConsulting - yup if the report is based on a parameterised query which draws parameters from a form's controls. ok cool! thanks so much!

Pat thanks for your help once again! you mentioned in your previous post
but you will need to add additional code to trap the form Unload event

so i was trying to figure out what code you were refering to that you would put into the unload event. when i re-read your comment a few times, all i could think about was that i would put the Allen Browne code into the click event of the save button, not the unload event. hrmm if you don't mind could you share with me what code you would personally put in the unload event? thanks and so sorry if i'm asking a silly question!
There are times when you want to attempt to prevent the user from closing a form if there are pending changes.  Using another global variable helps with that.  So define gCloseOK in the beginning of the form's class module.  In the Current event set it to true.  At the beginning (as the first statement) of the BeforeUpdate event set it to False.  If the user says Yes to one of your cancel the update? questions set the flag to Yes since you used undo to back out the change.  

In the Form's AfterUpdate event set it to True again because you know the record has been successfully saved.  So, this flag is always True unless an update was cancelled.  

Then in the Unload event of the form, check the gCloseOK and if it is False, cancel the unload event.  This keeps the form open until the user either fixes the problem so the update is successful or cancels the update.  The user can get past this and it is OK because the update will not happen but this makes it clear that the update isn't getting applied.

So, the combination of these 4 events - Current, BeforeUpdate, AfterUpdate, and Unload can be used to accomplish your goal.
developingprogrammerAuthor Commented:
whao great insights Pat into using these 4 events and how they work together. i'll definitely keep it in mind. thanks!! = ))
developingprogrammerAuthor Commented:
hi Pat! i just posted a question regarding this topic which you helped me earlier on. essentially the Cancel = True code that you shared with me to put in the BeforeUpdate event to cancel the update when the users hit "x", that code doesn't work - or rather there is a msgbox that pops up.

if i change that code to me.undo, it works. could you share with me why? if you could reply to that new question (found here http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28320866.html) that would be great! thanks Pat!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.