Switch Form Source in Access Subform But Have it Remember Current Data When Switch Back

Question

I'm trying to use a subform container to switch access subforms which display on a form. I can do this successfully but when I return to a form which I previously had opened it has been reset back to it's initial state. Is there any way I can "pop" forms off of a subreport yet have them stay active in memory so that when I pop them back in it shows the data on screen that was active when they were popped out? Hoping not to build some sort of bookmark routine that sets it back to the original state.

Answer 1

Overlay the subforms each other and hide all except the one currently to display.

Fast, efficient, and almost zero code. Like:

Private Sub DisplaySubform(ByVal SubformName As String)

    Dim C As Control

    For Each C in Me.Controls
        If C.ControlType = acSubform Then
            C.Visible = (C.Name = SubformName)
        End If
    Next

End Sub

Answer 2

Forms in VBA are pre-declared meaning that you don't need to Dim or New them. You can simply just create a new userform and it is immediately available for use:

Option Explicit

Sub test()
    UserForm1.Show ' the DEFAULT instance of userForm1 will be shown
End Sub

You can think of this as getting to use the form for free... but it comes at a cost, which it seems you've already discovered.

You get more control if you Dim and New the from separately, like this:

Option Explicit

Sub test()
    Dim myForm As UserForm1
    Set myForm = New UserForm1
    myForm.Show ' a SPECIFIC instance of userForm1 will be shown
End Sub

There is a world of difference between showing the default instance compared to showing a specific instance - the latter being much preferred.

There is a way to capture the forms data in a separate class, which is a pretty cool pattern, as described by @MatsMug here: rubberduckvba.wordpress.com/2017/10/25/userform1-show

If you want to learn more then I's suggest getting yourself a copy of Rubberduck which is (more than) a VBA-IDE enhancement: https://github.com/rubberduck-vba/Rubberduck/releases