访问/VBA："Run-time error 2169. You can't save this record at this time"

Question

使用Access 2013与ADO连接到SQL Server后端数据库

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

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

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

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

但有问题，它给出了上述错误，如果取消被点击。还有其他问题，比如，错误发生一次后，然后任何进一步的命令(保存或取消或关闭表单)不起作用-我认为这是因为VBA解释器由于先前的错误而停止。另一个复杂的是出现-我现在需要结束MS-Access进程从Windows任务管理器，这样做，然后重新启动数据库，然后打开这个表单将给出一个错误，窗体将无法加载。然后在设计模式下打开表单时，我可以看到表单的连接字符串保存在表单的Record Source属性中(这种情况有时会发生)，它看起来像这样:

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

下面是我的代码:

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

我被卡住了，想知道其他人是怎么处理这个问题的?基本上，当用户试图使用Cancel按钮或(Windows)关闭按钮关闭表单时，我想为用户提供Save, Ignore, Cancel选项。如果用户选择Cancel，那么它应该返回到表单，而不会更改或撤销对数据的任何更改。解决办法也许很简单，但我却想不起来。

提前感谢!

Answer 1

请尝试以下代码-我针对所有六种情况进行了测试，并采取了适当的操作。

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