vba Ms Access 在子表单中保存记录

Ms Access Save record in subform

提问by Icode4food

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.

我有一个带有包含多个子表单的选项卡控件的主表单。我需要确保在用户切换选项卡时保存子表单中的数据。问题是 DoCmd.RunCommand acCmdSaveRecord 似乎只适用于当前表单，因此它不会将数据保存在子表单中。

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.

我在子表单上尝试了不同的事件，例如停用、OnLostFocus 等，但它们不会触发，直到其他地方的另一个字段获得焦点。

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?

理想的解决方案似乎是在选项卡控件的 OnChange 事件上放置一些东西，以确保保存所有数据。那是我的问题，如何将记录保存在子表单中？

采纳答案by Fionnuala

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.

在 Access 中，默认设置是保存，因此除非您做了一些非常复杂的事情来防止这种情况发生，否则将焦点从子窗体移动将自动保存记录。您可以通过添加记录、从子窗体移出，然后检查表来测试这一点。

回答by David-W-Fenton

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:

您可以通过将其 .Dirty 属性设置为 False 来保存任何表单。对于像这样会运行很多的东西，我想我会写一个子来遍历子表单，检查是否有脏的，并保存脏的。像这样的东西：

  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.

现在，在选项卡控件上有很多不是子窗体的控件的情况下，这实际上非常低效。如果你的选项卡只有子表单，它会相当有效。在任何一种情况下，使用在表单的 OnLoad 事件中填充的自定义集合都更有效率，然后您将遍历该集合，该集合只包含选项卡控件的子表单，并保存任何脏的。

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.

这两种方法都比使用选项卡的 OnChange 事件更可取，因为每次添加带有子窗体的选项卡页或更改子窗体控件的名称时，都必须更改 OnChange 事件。

回答by shlgug

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):

这是一个名为“frmCustomers”的主表单的示例，其中包含一个子表单“sfmTransaction”，而该子表单又包含一个名为“sfmOrderLine”的子表单。表单有一个按钮“cmdSave”，只有当用户单击“cmdEdit”按钮时才可见（其目的是锁定所有控件直到用户单击编辑按钮，然后在用户单击时重新锁定它们节省）：

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

在主窗体('frmCustomer') 上转到子窗体的退出事件，并添加 'me.recalc'两次，如下所示：

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:

在子窗体('sfmTransaction') 中，转到子窗体的子窗体的退出事件，并添加 'me.recalc'两次，如下所示：

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.

希望这可以帮助。

回答by Hedwyg

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.

将dirty 属性设置为false 可能会强制Access 将数据保存到数据库，但它会绕过before_update 事件。这意味着，如果您已将此事件用于验证或其他目的，则您的数据库中现在可能存在错误数据。