Ms Access Save record in subform

Question

I have a main form with a tab control containing multiple subforms. I need to be sure that the data in a subform is saved when the user switches tabs. The problem is that DoCmd.RunCommand acCmdSaveRecord seems only applies to the current form so it doesn't save the data in the subform.

I have tried different events on the subform such as deactivate, OnLostFocus etc but they don't fire until another field somewhere else gets the focus.

The ideal solution would seem to be to put something on the OnChange event of the tab control to be sure that all the data is saved. That is my question, how to do I save the record in a subform?

Answer 1

In Access, the default is to save, so unless you have done something pretty complicated to prevent this, moving the focus from a subform will automatically save the record. You can test this by adding a record, moving from the subform, and then checking the table.

Answer 2

You don't have to do anything at all, as the subform is saved as soon as it loses focus (when the tab control changes).

But as an exercise, I've outlined the code you'd write if you needed to.

You can save any form by setting it's .Dirty property to False. For something like this that's going to run a lot, I think I'd write a sub to walk through the subforms, check if any are dirty, and save the dirty ones. Something like this:

  Public Sub SaveSubFormsOnTab()
    Dim pge As Control
    Dim ctl As Control

    For Each pge In Me!ctlTab.Pages
      Debug.Print pge.Name
      For Each ctl In pge.Controls
        If ctl.ControlType = acSubform Then
           If ctl.Form.Dirty Then
              ctl.Form.Dirty = False
           End If
           Debug.Print ctl.Name
        End If
      Next ctl
    Next pge
    Set ctl = Nothing
    Set pge = Nothing
  End Sub

Now, that's actually quite inefficient in cases where you have lots of controls on your tab control that aren't subforms. If your tab has nothing but subforms, it will be fairly efficient. In either case, it's much more efficient to use a custom collection populated in the form's OnLoad event, and then you'd walk that collection that includes nothing but your tab control's subforms, and save any that are dirty.

Either of these is preferable to using the OnChange event of the tab, because each time you add a tab page with a subform or change the name of a subform control, you'd have to alter the OnChange event.