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

Question

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

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.

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.

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

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:

{ ? = call 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.

Thanks in advance!

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2014-03-12T21:36:38

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

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

1 Answers