Save record in subform

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 acCmdSaveRecor开发者_开发技巧d 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?

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.

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.

I was having a similar issue where I needed various code to run in the subform and values in the main form - that were based on values in the subform - to be recalculated. Here is what finally worked:

This is an example with a main form named 'frmCustomers' containing a subform 'sfmTransaction', which in turn has a subform called 'sfmOrderLine'. The forms have a button 'cmdSave' that is only visible when the user clicks the 'cmdEdit' button (the purpose of which is to lock all the controls until the user clicks the edit button, and then to re-lock them when he clicks save):

On the main form ('frmCustomer') go the subform's exit event, and add 'me.recalc' twice, like this:

Private Sub sfmTransaction_Exit(Cancel As Integer)
    If (Not Form_sfmTransaction.NewRecord And Form_sfmTransaction.cmdSave.Visible) Or (Not Form_sfmOrderLine.NewRecord And Form_sfmOrderLine.cmdSave.Visible) Then
        'To save subforms
        Me.Recalc
        Me.Recalc
    End If
End Sub

In the subform ('sfmTransaction') go the subform's subform's exit event, and add 'me.recalc' twice, like this:

Private Sub sfmOrderLine_Exit(Cancel As Integer)
    If Not Form_sfmOrderLine.NewRecord And Form_sfmOrderLine.cmdSave.Visible Then
        'To save subform
        Me.Recalc
        Me.Recalc
    End If
End Sub

Hope this helps.

Setting the dirty property to false may force Access to save the data to the database, but it bypasses the before_update event. This means that if you've used this event for validation or other purposes, you can now have bad data in your database.