如何在切换Access子窗体后返回时保留其当前数据状态

Great question! The good news is you absolutely can retain your subforms' state without resetting them or relying on bookmarks—you just need to stop letting Access destroy and re-create the subform instances every time you switch. Here's how to do it with two reliable, straightforward methods:

Method 1: Use Multiple Hidden Subform Containers (Simplest for Beginners) #

This approach skips complex VBA references and relies on basic form visibility toggles:

• Add a separate subform container control to your main form for every subform you want to switch between (e.g., sfmContainer_Customers, sfmContainer_Orders). Place all containers in the exact same spot on the form so they overlap perfectly.
• Set all containers to Visible = No initially.
• When you need to show a subform for the first time:
  1. Set that container's SourceObject to your subform's name (e.g., Me.sfmContainer_Customers.SourceObject = "frmCustomerSubform").
  2. Toggle its Visible = Yes, and set all other containers to Visible = No.
• For subsequent switches, just flip the Visible property of the containers. Since each subform stays loaded in its own container, all user selections, filters, and scroll positions will be preserved exactly as they left them.

Method 2: Store Subform Instances in Memory (More Flexible) #

If you prefer a single container and want tighter control over subform objects, use this method:

1. In your main form's VBA module, declare private variables to hold references to your subform instances. This keeps them alive in memory while the main form is open:

   Private m_sfmCustomers As Form_frmCustomerSubform
   Private m_sfmOrders As Form_frmOrdersSubform
   

2. Create a helper subroutine to handle switching cleanly:

   Private Sub ShowSubform(subformType As String)
       ' Hide container temporarily to reduce flicker
       Me.sfmMainContainer.Visible = False
       
       Select Case subformType
           Case "Customers"
               If m_sfmCustomers Is Nothing Then
                   ' Create a new instance only if it doesn't exist yet
                   Set m_sfmCustomers = New Form_frmCustomerSubform
                   ' Attach the pre-loaded instance to the container
                   Set Me.sfmMainContainer.Form = m_sfmCustomers
               Else
                   ' Reuse the existing, state-preserved instance
                   Set Me.sfmMainContainer.Form = m_sfmCustomers
               End If
           Case "Orders"
               If m_sfmOrders Is Nothing Then
                   Set m_sfmOrders = New Form_frmOrdersSubform
                   Set Me.sfmMainContainer.Form = m_sfmOrders
               Else
                   Set Me.sfmMainContainer.Form = m_sfmOrders
               End If
       End Select
       
       Me.sfmMainContainer.Visible = True
   End Sub
   

3. Call this subroutine from your navigation buttons, like:

   Private Sub btnShowCustomers_Click()
       ShowSubform "Customers"
   End Sub
   

4. Clean up instances when the main form closes to avoid memory leaks:

   Private Sub Form_Unload(Cancel As Integer)
       Set m_sfmCustomers = Nothing
       Set m_sfmOrders = Nothing
   End Sub
   

Why Your Original Approach Reset the Subform #

When you set a subform container's SourceObject to a new form name (or clear it), Access automatically destroys the old subform instance and creates a brand new one. That's why your state was lost—you were working with a fresh copy every time you switched back. By retaining the instance in memory (either via a dedicated container or a VBA reference), you bypass this destruction/recreation cycle entirely.

内容的提问来源于stack exchange，提问作者AccessMan