Access/VBA：“运行时错误 2169。此时您无法保存此记录”

Access/VBA: "Run-time error 2169. You can't save this record at this time"

提问by Steve F

Using: Access 2013 with ADO connection to SQL Server back-end database

使用：通过 ADO 连接到 SQL Server 后端数据库的 Access 2013

A form in my Access database is dynamically bound at runtime to the results of a SELECT stored-procedure from SQL Server, and allows the user to make changes to the record.

我的 Access 数据库中的表单在运行时动态绑定到来自 SQL Server 的 SELECT 存储过程的结果，并允许用户对记录进行更改。

It has 2 buttons: Save and Cancel.

它有两个按钮：保存和取消。

It is shown as a pop-up, modal, dialog form, and it has a (Windows) Close button at the top right corner.

它显示为一个弹出式、模态、对话框形式，并在右上角有一个 (Windows) 关闭按钮。

I've put VBA code to ask the user whether he wants to Save, Ignore or Cancel the close action.

我已经放置了 VBA 代码来询问用户是否要保存、忽略或取消关闭操作。

But there are problems and it gives the aforementioned error if Cancel is clicked. There are also other problems, like, after the error occurs once, then any further commands (Save or Cancel or closing the form) don't work - I think this is because the VBA interpreter has halted due to the earlier error. Another complication is that arises - I now need to end the MS-Access process from Windows Task Manager, doing this and then restarting the database and then opening this form will give an error and the form won't load. When the form is then opened in Design mode, I can see the connection string for the form is saved in the Form's Record Source property (this happens only sometimes), and which looks something like this:

但是有问题，如果单击取消，则会出现上述错误。还有其他问题，例如，在错误发生一次之后，任何进一步的命令（保存或取消或关闭表单）都不起作用 - 我认为这是因为 VBA 解释器由于较早的错误而停止。出现了另一个复杂情况 - 我现在需要从 Windows 任务管理器结束 MS-Access 进程，执行此操作，然后重新启动数据库，然后打开此表单将出现错误并且该表单将无法加载。当表单在设计模式下打开时，我可以看到表单的连接字符串保存在表单的记录源属性中（这只是有时发生），它看起来像这样：

{ ? = call dbo.tbBeneficiary_S(?) }.

{ ? = 调用 dbo.tbBeneficiary_S(?) }。

Here is my code:

这是我的代码：

Dim CancelCloseFlag As Boolean
Dim SavePrompt As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim a As Integer

    If SavePrompt Then
        a = MsgBox("Do you want to save changes?", vbQuestion + vbYesNoCancel, "Changes made")

        Select Case a
        Case vbNo:
            Me.Undo
            CancelCloseFlag = False
        Case vbYes:
            'do nothing; it will save the changes
            CancelCloseFlag = False
        Case vbCancel:
            Cancel = True
            CancelCloseFlag = True
        End Select
    End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
    SavePrompt = True
End Sub

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

Private Sub Form_Load()
    LoadBeneficiaryDetails
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If CancelCloseFlag Then
        Cancel = True
    End If
End Sub

Private Sub btCancel_Click()
    If Me.Dirty Then
        SavePrompt = True
    End If

    DoCmd.Close
End Sub

Private Sub btSave_Click()
    SavePrompt = False

    DoCmd.Close
End Sub

I'm stuck and would like to know how others go about this issue? Basically I want to offer the user the choice Save, Ignore, Cancel when the user attempts to close the form with either Cancel button or the (Windows) close button. If the user chooses Cancel, then it should just return to the form without changing or undoing any changes to the data. The solution may be simple but it escapes my overworked mind.

我被卡住了，想知道其他人如何解决这个问题？基本上，当用户尝试使用“取消”按钮或 (Windows) 关闭按钮关闭表单时，我想为用户提供“保存”、“忽略”、“取消”选项。如果用户选择取消，那么它应该只返回表单而不更改或撤消对数据的任何更改。解决方案可能很简单，但它摆脱了我过度劳累的头脑。

Thanks in advance!

提前致谢！

回答by Wayne G. Dunn

Please try the following code - I tested against all six scenarios and the proper action is taken.

请尝试以下代码 - 我针对所有六个场景进行了测试，并采取了适当的措施。

Option Compare Database
Option Explicit

Dim blnAction               As Integer
Dim blnBeenThereDoneThat    As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If blnBeenThereDoneThat = True Then Exit Sub
    blnBeenThereDoneThat = True
    blnAction = MsgBox("Do you want to save changes?", vbQuestion + vbYesNoCancel, "Changes made")

    Select Case blnAction
    Case vbNo:
        Me.Undo
    Case vbYes:
        'do nothing; it will save the changes
    Case vbCancel:
        Cancel = True
    End Select
End Sub

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

Private Sub Form_Load()
    LoadBeneficiaryDetails
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If blnAction = vbCancel Then
        blnBeenThereDoneThat = False
        Cancel = True
    End If
End Sub

Private Sub btCancel_Click()
    If Me.Dirty Then
        Form_BeforeUpdate (0)
    End If
    If blnAction = vbCancel Then
        blnBeenThereDoneThat = False
        Exit Sub
    ElseIf blnAction = vbYes Then
        DoCmd.Close
    Else
        DoCmd.Close
    End If

End Sub

Private Sub btSave_Click()
    If Me.Dirty Then
        Form_BeforeUpdate (0)
    End If
    If blnAction = vbCancel Then
        Exit Sub
    Else
        DoCmd.Close
    End If
End Sub