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

[NeoPa]

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.

'CloseMe() closes the Form or Report.  No data or design changes are saved here.
Public Sub CloseMe(objMe As Object)
    Dim intType As Integer
    Dim ctlVar As Control
 
    With objMe
        Select Case True
        Case TypeOf objMe Is Form
            intType = acForm
            Call .Undo
            For Each ctlVar In .Controls
                With ctlVar
                    If .ControlType = acSubform Then
                        Call .Form.Undo
                        .SourceObject = ""
                    End If
                End With
            Next ctlVar
        Case TypeOf objMe Is Report
            intType = acReport
        Case Else
            Exit Sub
        End Select
        Call DoCmd.Close(ObjectType:=intType _
                       , ObjectName:=.Name _
                       , Save:=acSaveNo)
    End With
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.