vba 防止关闭按钮在 MS Access 中保存记录

Preventing close buttons from saving records in MS Access

提问by Steven Liekens

In a Microsoft Access form, whenever the current record changes, any changes in bound controls are silently saved to the database tables. This is fine, but I don't want it to happen when a user closes a form, because it is the direct opposite of what many people would expect.

在 Microsoft Access 窗体中，每当当前记录更改时，绑定控件中的任何更改都会以静默方式保存到数据库表中。这很好，但我不希望在用户关闭表单时发生这种情况，因为这与许多人所期望的正好相反。

The best example is when you try to close an excel file with unsaved changes, it asks whether the changes should be discarded. This is exactly what I'm trying to achieve in Access, but can't find any way to trap the close button's event in VBA.

最好的例子是，当您尝试关闭包含未保存更改的 Excel 文件时，它会询问是否应放弃更改。这正是我试图在 Access 中实现的目标，但找不到任何方法在 VBA 中捕获关闭按钮的事件。

The form's Unload event is the first event that is triggered when someone clicks the close button, but by then the changes are already written to the database.

表单的 Unload 事件是当有人单击关闭按钮时触发的第一个事件，但此时更改已经写入数据库。

Is this at all possible, or do I have to create my own close buttons? I'm comfortable with writing large amounts of code for trivial things like this but I hate having to clutter the GUI.

这是可能的，还是我必须创建自己的关闭按钮？我很乐意为这样的琐碎事情编写大量代码，但我讨厌把 GUI 弄得一团糟。

回答by salih0vicX

You have to work with Form_BeforeUpdateevent. Below is an example; however it does create a typical warning message: "You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. ..." - depending on your database settings. You can use simple workaround below to avoid displaying of that message.

你必须处理Form_BeforeUpdate事件。下面是一个例子；但是它确实会创建一条典型的警告消息：“此时您无法保存此记录。Microsoft Access 可能在尝试保存记录时遇到错误。...” - 取决于您的数据库设置。您可以使用下面的简单解决方法来避免显示该消息。

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Cancel = True
   'Or even better you can check certain fields here (If Then...)

End Sub


Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2169 Then 
        Response = True
    End If
End Sub

回答by PatHartman

Sean gave an almost correct answer but it leaves gaps.

肖恩给出了一个几乎正确的答案，但它留下了空白。

In general, the FORM's BeforeUpdateis the most important form event. It is your LAST line of defense and ALWAYS runs prior to a record being saved regardless of what prompted the save (form close, new record, your own save button, clicking into a subform, etc.) Although I occasionally use the control's BeforeUpdateevent just so the user gets the error message sooner, the bulk of the validation code I write runs in the Form_BeforeUpdateevent. This is the event you MUST use if you want to ensure that certain controls are not empty. No control level event will do this reliably for all situations. Primarily because if the control never gets focus, no control level event ever fires. Form_BeforeUpdateis also the event you would use if your validation involves multiple fields. If you are using any other control or event level event, you are wasting your time. There is always away around your "trap" and your table almost certainly contains invalid data.

一般来说，FORMBeforeUpdate是最重要的表单事件。这是您的最后一道防线，并且始终在保存记录之前运行，无论提示保存的是什么（表单关闭，新记录，您自己的保存按钮，单击子表单等）虽然我偶尔会使用控件的BeforeUpdate事件所以用户会更快地收到错误消息，我编写的大部分验证代码都会在Form_BeforeUpdate事件中运行。如果要确保某些控件不为空，则必须使用此事件。没有任何控制级事件可以在所有情况下可靠地做到这一点。主要是因为如果控件从未获得焦点，则不会触发控件级别事件。 Form_BeforeUpdate如果您的验证涉及多个字段，这也是您将使用的事件。如果您正在使用任何其他控件或事件级事件，那么您就是在浪费时间。您的“陷阱”总是存在，并且您的表格几乎肯定包含无效数据。

Regarding the OP's question. If you want to force people to use your own save button and prompt them if they don't then you need a form level variable as Sean's suggestion implied. The only difference, is that you need to set it to False, in the form's Current event NOT the Open event. You want the flag to be reset for EVERY new record, not just when the form opens. Then you set it to True in your save button click event, just before you force the record to save with DoCmd.RunCommand acCmdSaveRecord.

关于OP的问题。如果你想强迫人们使用你自己的保存按钮并提示他们如果他们不这样做，那么你需要一个表单级别的变量，正如肖恩的建议所暗示的那样。唯一的区别是您需要将其设置为 False，在表单的 Current 事件中而不是 Open 事件中。您希望为每条新记录重置标志，而不仅仅是在表单打开时。然后在您的保存按钮单击事件中将其设置为 True，就在您强制使用DoCmd.RunCommand acCmdSaveRecord.

Then finally, in the Form_BeforeUpdateevent, you check the value of the variable.

最后，在Form_BeforeUpdate事件中，您检查变量的值。

If bClose = False Then
   If MsgBox("Do you want to save the changes?", vbYesNo) = vbNo Then
       Cancel = True
       If MsgBox("Do you want to discard the Changes?", vbYesNo) = vbYes Then           
            Me.Undo
       End If
       Exit Sub
   End If
End If

回答by SeanC

this is code I have that checks to see if the form is being closed or saved.

这是我用来检查表单是否正在关闭或保存的代码。

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not UsingSaveButton Then
    If MsgBox("Abandon Data?", vbInformation + vbYesNo) = vbNo Then
        Cancel = True
    Else
        DoCmd.RunCommand acCmdUndo
    End If
End If
End Sub

I have a Boolean Flag that is set to False on loading, and then when my Save button is used, I set it to true to allow the update to run through.
If the flag is not set, then they are leaving the record (either through going to a different record, or closing the form) so I ask if they actually want to save the changes.
The Cancel = Trueaborts the exit of the form or the move to a different record if any changes have been made.
The DoCmd.RunCommand acCmdUndoundoes any changes so they are not saved.

我有一个在加载时设置为 False 的布尔标志，然后当使用我的保存按钮时，我将它设置为 true 以允许更新运行。
如果未设置标志，那么他们将离开记录（通过转到不同的记录，或关闭表单），所以我问他们是否真的想要保存更改。
该Cancel = True中止形式的出口或转移到是否已作出任何改变不同的记录。
该DoCmd.RunCommand acCmdUndo所以他们不会保存撤销任何更改。

回答by Georg Lackner

Actually, you cannot trap this, Access is working directly upon the table, every change is already being saved when the field looses the focus by moving to another field, record or a button.

实际上，您无法捕捉到这一点，Access 直接在表上工作，当该字段通过移动到另一个字段、记录或按钮而失去焦点时，每个更改都已经被保存。

Actually imho this is a great advantage compared to Excel

实际上恕我直言，与 Excel 相比，这是一个很大的优势

If you really want a behaviour similar to Excel you'd need to work on a copy of the table and some code for updating.

如果你真的想要一个类似于 Excel 的行为，你需要处理表格的副本和一些用于更新的代码。

回答by spotnag

In the Form in the 'On Unload' event add the below code.

在“卸载时”事件的表单中添加以下代码。

DoCmd.RunCommand acCmdUndo
DoCmd.Quit

Records no longer being saved when users close a form in any way.

当用户以任何方式关闭表单时不再保存记录。

回答by user3003395

** you can use exit button with this code**

** 您可以使用带有此代码的退出按钮**

Private Sub Button_Click()
If Me.Dirty = True Then
   If MsgBox(" Save Change ", vbYesNo) = vbYes Then
      Me.Dirty = False
   Else
      Me.Undo
   End If
End If
DoCmd.Close acForm, "FormName"
End Sub