Disable Save Prompt when user clicks the Windows 'X'

[Touni102]

I've been looking at this problem, and found many answers for Excel vba, but those answers mention the method "Before_Close", which Forms do not have... So far I found what I need to do to use the DoCmd.Close function with the following:

DoCmd.Close acForm, "Form Name Here", acSaveNo

However, when I put this code in the On Close event for the form, the prompt occurs before the On Close event gets called, so I'm sure I need some other event out there that gets called before the On Close event. Is there something equivalent to Before Close?

[OBP]

Touni, what "prompt" do you mean, Access does not normally prompt the User to save a record, but it does prompt for "Design" changes to Queries, Forms, Reports and VBA.
Access automatically saves any record changes.

[Touni102]

In my form there is code that changes the form contents. When I close the form it asks If I want to save those changes. Also, I don't want to make the form read only because there are places in the form where the user needs to edit.

[CreganTur]

In my form there is code that changes the form contents. When I close the form it asks If I want to save those changes.
I'm guessing you mean that you change some record values? If you're making changes to the record you have to save those changes by either saving the record or moving to the next record, which automatically saves the changes you made.

If you do not do this, then you will always be prompted to save.

[OBP]

Have you tried
DoCmd.Close acForm, "Form Name Here", acSaveYes
Does that still give the prompt?

[Touni102]

This does what I want to do:

DoCmd.Close acForm, "Form Name Here", acSaveNo

acSaveYes, will not give the prompt, but it will automatically save the form. I do not want to save. I can create a close button with this code in the click event, and I know it will work. What I am asking is where must I put the code for this in order for it to work even when I press 'X' on the window? What event gets called when i press the 'X' button?

[CreganTur]

This does what I want to do:

DoCmd.Close acForm, "Form Name Here", acSaveNo

acSaveYes, will not give the prompt, but it will automatically save the form. I do not want to save. I can create a close button with this code in the click event, and I know it will work. What I am asking is where must I put the code for this in order for it to work even when I press 'X' on the window? What event gets called when i press the 'X' button?

Try this:
Me.Dirty = False
DoCmd.Close acForm, "Form Name Here", acSaveNo

It will set the dirty flag on your Form to False, which means that Access will think that no changes have been made, so it won't prompt you to save.

HTH:thumb

[OBP]

You can of course disable the Access Window Close X button, so thta the situation does not arise.

[Touni102]

Try this:
Me.Dirty = False
DoCmd.Close acForm, "Form Name Here", acSaveNo
It will set the dirty flag on your Form to False, which means that Access will think that no changes have been made, so it won't prompt you to save.

HTH:thumb
The problem I have is still "What event to I put this code in for it to run right before the window closes?" My form is trying to save changes to the layout of a subform that is inside my form. (e.g. -- I resize a column of a subform query for a better view for printing, I don't want it to save) It makes sense to set that subform.Dirty to false, but still, where to put the code?



You can of course disable the Access Window Close X button, so thta the situation does not arise.
Never thought of that... Not as elegant of a solution, but if I can't figure this out I guess i'll do that.

[CreganTur]

My form is trying to save changes to the layout of a subform that is inside my form.
Well that explains why you're not getting a solution; you're not giving us all the details. I'm not trying to bust your chops or attack you or anything like that, but you need to give us all the pertinent information so we can help you find the right solution for your needs.

Since it's the subform being dirty that's causing a problem, try this- If you have a close button on your form, put this code there. Otherwise, package it as a part of your OnClose event of your main form:

If Me.Subform.Form.dirty = True Then Me.Subform.Form.Dirty = False

HTH:thumb

[OBP]

Randy, the problem as I see it is that it is a Design Change that the Subform is picking up and wants to save. There aren't any events that will supercede the windows Close, as they don't get a chance to occur to trigger anything, the Windows Close takes precedence.
Also they don't want to Save the design change, but you can't tell it to ignore the change before the form is closed, because it won't show the user the change. So if the only way to "Close the Form" is their Command Button the VBA will always work.

[CreganTur]

So if the only way to "Close the Form" is their Command Button the VBA will always work.

I guess it's really a question of event precendence. Clicking the "X" button will cause any code in the OnClose event to fire... but when does the save prompt from the Dirty event fit into precendence? If it's after the OnClose event, then there shouldn't be any problem with putting "

If Me.Subform.Form.dirty = True Then Me.Subform.Form.Dirty = False " in On Close.
If Dirty takes precendence, then, yes, you are right and the "X" button needs to be disabled.

[OBP]

Randy, I think the OP may be talking about the Access Window's Close X, rather than the Form's X, so I don't think the Form's On Close runs at all under those circumstances.

[CreganTur]

Randy, I think the OP may be talking about the Access Window's Close X, rather than the Form's X, so I don't think the Form's On Close runs at all under those circumstances.

Ahhhh.... that's a very different beast, then.

If that's the case, then we could use the overkill approach and put the Dirty code on the subform's OnChange event. It's massive, massive overkill, but it will solve the problem.


NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Actually, I just tested this, and clicking the Access "X" button will launch the Form's OnClose event.

The only thing that will keep an active Form's OnClose event from firing is an ungraceful exit of the application (that I've found from a couple minute's google-fu, anyway).

[Touni102]

Well that explains why you're not getting a solution; you're not giving us all the details.

Sorry, as I was trying to figure out why it was asking to save, I just found out that when I change the subform columns it wants to save that... I didn't know this until recently. About the On Close event, I used the Form's On Close event and place the code with a breakpoint. After clicking the 'X' on the Form, not the Access window itself, The save prompt came up, and after clicking no the breakpoint was reached.

I can try the suggestion of placing it on the On Change

[OBP]

Randy, you are correct the Window Close does trigger the On Close event, so I don't see why the Code doesn't work form there.

[Touni102]

Randy, you are correct the Window Close does trigger the On Close event, so I don't see why the Code doesn't work form there.
The code does work, but it is triggered after the fact. The prompt gets displayed first, then the code is triggered. I'm looking for an event the gets called before Access decides to display the prompt.

Btw, subforms only have an "On Enter" and "On Exit" event so I tried "On Exit", using Subform1.Form.Dirty = False, but still, the prompt gets displayed BEFORE the code is reached.

[CreganTur]

The code does work, but it is triggered after the fact. The prompt gets displayed first, then the code is triggered. I'm looking for an event the gets called before Access decides to display the prompt.

Well, I guess we've discovered that the Dirty event takes precdence over the OnClose event. Too bad we all learned this the hard way:rofl: