By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,101 Members | 1,426 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to Close a Main Form Without Saving Changes of any Subforms

NeoPa
Expert Mod 15k+
P: 31,473
Introduction :

This article relates to the long-standing (Since at least Access 2.0.) problem whereby a Form, used within a Subform of another (main) Form, will have any temporary and transient design changes made during the time the main Form is open and in use, saved away automatically to become the new design of the Form. This is rarely desirable, yet is something Access does for you without any way to specify you don't want it to. Typically, a Form designer wants the Form left just as they've chosen to save it themselves. Transient changes are intended only for the lifetime of the instance currently in use and should not be saved away to become the new design of the Form.

Examples :

Examples of the sort of changes that are typical in such scenarios include :
  1. The size of the window on opening the Form in Design view (More an irritation for the designer).
  2. The sort order of the data.
  3. Filtering.
  4. Sizes and positions of any controls within the Form.
  5. Formats of controls used to illustrate specific situations.
  6. Etc.
In truth, the number of possible changes that may occur while the Form is in use is only limited to the number and type of the controls used within it, and the number of changeable properties for each of those controls. That, and the properties of the Form itself.

Solution :

I have found a solution, that seems to work, which is to drop the Form itself from the Subform control at the point where the main Form is closed. As it is not associated at the time Access would otherwise generously save it for you (even though you don't require it), no design changes are saved away and you can comfortably use it again in the knowledge that it is exactly as it was last saved in Design view.

Supporting Code :

The following code can be used generically to close any Form or Report object in the knowledge that no design changes are made to any of the related objects, either accidentally or via Access' misguided generosity.

Expand|Select|Wrap|Line Numbers
  1. 'CloseMe() closes the Form or Report.  No data or design changes are saved here.
  2. Public Sub CloseMe(objMe As Object)
  3.     Dim intType As Integer
  4.     Dim ctlVar As Control
  5.  
  6.     With objMe
  7.         Select Case True
  8.         Case TypeOf objMe Is Form
  9.             intType = acForm
  10.             Call .Undo
  11.             For Each ctlVar In .Controls
  12.                 With ctlVar
  13.                     If .ControlType = acSubform Then
  14.                         Call .Form.Undo
  15.                         .SourceObject = ""
  16.                     End If
  17.                 End With
  18.             Next ctlVar
  19.         Case TypeOf objMe Is Report
  20.             intType = acReport
  21.         Case Else
  22.             Exit Sub
  23.         End Select
  24.         Call DoCmd.Close(ObjectType:=intType _
  25.                        , ObjectName:=.Name _
  26.                        , Save:=acSaveNo)
  27.     End With
  28. End Sub
  • Line #8 Detects if the object is a Form.
  • Lines #9 - #15 Clears any unsaved data changes and unloads Forms from all Subforms.
  • Lines #16 - #17 More straightforward for Reports. Simply flags as Report for later.
  • Lines #18 - #19 Ignores any other type of object passed.
  • Lines #21 - #23 Closes main Form without saving any design changes.
Sep 15 '13 #1
Share this Article
Share on Google+