Prevent 'save design changes' for an open form when closing Access

5
votes

I have a split form as my main interface in an Access 2010 application. As normal, users are able to resize the datasheet portion, reorder columns, etc.

However, I don't want them to save such changes when leaving the form. Each time the form is opened the default format should be loaded.

I've taken care of all but one closing method. To avoid them closing using the default close button I've set Border Style = None. Instead I have a Close Form button that uses DoCmd.CLOSE acForm, "Main_form", acSaveNo

But if the user clicks the close button for the Access application, it pops the 'Do you want to save changes to the design of form` dialog like always.

I looked into disabling the application's Close button, but messing with Windows API is beyond my skill (and there should be a way to accomplish this without going to extreme measures).

3
ms-accessvbams-access-2010

3 Answers

4
votes

I found a way to do this. A combination of database options, form format options, and vba can do it.

  1. Go to the 'Current Database' options screen in the main Access options and uncheck 'Enable design changes in Datasheet view'. This will prevent all datasheet view design changes in the database, so you will have to go into design mode for any table changes. Users can still reorder and resize columns within a form, but Access no longer considers that a valid design change and will not prompt to save it no matter how you close the form

  2. Set the form format property 'Save Splitter Bar Position' = No. The form will now clear any change to the bar location when the form is closed. Access got really weird on me about this setting, however. Once I had ever set the option to no, I could no longer use design view or layout view to set a new default bar position; it always reverted to the location where it was when I first tried this setting. Even resetting the option to Yes, saving the design change, and fully exiting the database did not fix this.

  3. So I added an On Load event to reset the split form size when the form opens: Me.SplitFormSize = 9000. The numbers involved are surprisingly high; in the form properties list this is set in inches. Mine was 6.5", which apparently translates to 9000.

With these three changes (along with the steps I detailed in the question) Access no longer prompts to save design changes when the form is closed, even if the user is closing the Access application entirely. The form also snaps the split form bar back to where it should be on load.

1
votes

Since the API is beyond my skill too, here is a left-field workaround.

Duplicate Main_Form and rename it "Main_Form_Template". Create an Autoexec module or edit an existing one and add:

DoCmd.DeleteObject acForm, "Main_Form"
DoCmd.CopyObject , "Main_Form", acForm, "Main_Form_Template"

That should reinstate the standard template for the user each time they open the database even if they were to save the form when closing Access.

0
votes

Turn your close button off on the form.

On the form's property sheet, format tab, about 2/3 of way down. Set Close Button = No

This forces the user to close it via the button you created.