Cancelling a Record Update with BeforeUpdate Event

[evn678]

Hello all,

I am trying to handle a scenario where a user clicks the 'X' close control on a form window border. I want to force the user to use the form's 'save' button to write the updated record. (If the form is not dirty, the 'X' control will just close it silently.)

In the BeforeUpdate event I ask the user if they want to discard their changes (this code is skipped if the user *actually* pressed the save button). If the user answers 'No', I want the form to remain open for editing and not actually close.

When I set Cancel = True in the BeforeUpdate sub and exit, Access pops up a yes/no dialog window stating: "You can't save this record at this time. The application may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"

In this case (cancel = true) I don't want the object (form) closed. Is there a way to keep the form open as the default behavior AND prevent Access from popping up the nuisance question window?

Using: Intel P4 / Win 2K / Access 2003 / VBA

Thank you!
Ed

[JConsulting]

Hello all,

I am trying to handle a scenario where a user clicks the 'X' close control on a form window border. I want to force the user to use the form's 'save' button to write the updated record. (If the form is not dirty, the 'X' control will just close it silently.)

In the BeforeUpdate event I ask the user if they want to discard their changes (this code is skipped if the user *actually* pressed the save button). If the user answers 'No', I want the form to remain open for editing and not actually close.

When I set Cancel = True in the BeforeUpdate sub and exit, Access pops up a yes/no dialog window stating: "You can't save this record at this time. The application may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"

In this case (cancel = true) I don't want the object (form) closed. Is there a way to keep the form open as the default behavior AND prevent Access from popping up the nuisance question window?

Using: Intel P4 / Win 2K / Access 2003 / VBA

Thank you!
Ed

Try this

Private Sub Form_Unload(Cancel As Integer)
If MsgBox("Would you like to discard changes?", vbYesNo) = vbNo Then
Cancel = True
End If
End Sub
 

[evn678]

Originally Posted by evn678
Hello all,

I am trying to handle a scenario where a user clicks the 'X' close control on a form window border. I want to force the user to use the form's 'save' button to write the updated record. (If the form is not dirty, the 'X' control will just close it silently.)

In the BeforeUpdate event I ask the user if they want to discard their changes (this code is skipped if the user *actually* pressed the save button). If the user answers 'No', I want the form to remain open for editing and not actually close.

When I set Cancel = True in the BeforeUpdate sub and exit, Access pops up a yes/no dialog window stating: "You can't save this record at this time. The application may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"

In this case (cancel = true) I don't want the object (form) closed. Is there a way to keep the form open as the default behavior AND prevent Access from popping up the nuisance question window?

Using: Intel P4 / Win 2K / Access 2003 / VBA

Thank you!
Ed



Try this
Code: ( text )

1. Private Sub Form_Unload(Cancel As Integer)
2. If MsgBox("Would you like to discard changes?", vbYesNo) = vbNo Then
3. Cancel = True
4. End If
5. End Sub

Thanks, but the unload does not occur when the update is canceled. Access still pops up the nuisance message.

[Denburt]

I think something along these lines will accomplish what you are seeking.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Save_Response = MsgBox("You Are About To Save This Record.  Do You Want To Continue?", vbQuestion + vbApplicationModal + vbYesNo, "Save Record?")
If Save_Response <> vbYes Then
Cancel = True
Me.Undo
End If
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2169
    Response = acDataErrContinue
Case Else
    Response = acDataErrDisplay
End Select
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = True
End Sub
 

[JConsulting]

Thanks, but the unload does not occur when the update is canceled. Access still pops up the nuisance message.

Don't use the before update event. Just use the unload event.

[evn678]

I think something along these lines will accomplish what you are seeking.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Save_Response = MsgBox("You Are About To Save This Record.  Do You Want To Continue?", vbQuestion + vbApplicationModal + vbYesNo, "Save Record?")
If Save_Response <> vbYes Then
Cancel = True
Me.Undo
End If
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2169
    Response = acDataErrContinue
Case Else
    Response = acDataErrDisplay
End Select
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = True
End Sub
 

Thank you !! This is the closest thing to answering my question "Is there a way to prevent Access from popping up the nuisance question window?"

I had not considered using the form error event to trap and suppress the error. I already had all the necessary logic in beforeupdate and unload. The acDataErrContinue prevents the Access pop up.

But there is one small problem - after the acDataErrContinue, the form loses all data updates as if a Me.Undo was executed. I suspect that using the acDataErrContinue is equivalent to answering 'Yes' to the suppressed dialog, which results in the form being reset to its original data. [Without the form error handler] After canceling the update, Access pops the question: "You can't save this record at this time. The application may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"

My original goal in all this was to preserve those changes so the user would press the 'Save' button and not the frame control box 'X'. Without the form error trap, I was just selecting 'No' on the access dialog, but this was redundant because my VB code in beforeupdate already asked the user this question and set cancel = true based on the user's 'No' answer. The unload code also correctly traps for this condition and executes a cancel.

The acDataErrContinue suppresses the dialog but doesn't preserve the form data. Any idea on how I can accomplish that?

Thanks very much for the help! You have made my app much better!
Ed

[Denburt]

Tried and tested copy/paste solution:

Option Compare Database
Option Explicit
Dim BoolDirty As Boolean
 
Private Sub Command0_Click()
On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
If BoolDirty = True Then
    DoCmd.Close acForm, Me.Name
End If
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Save_Response As Integer
Save_Response = MsgBox("You Are About To Save This Record.  Do You Want To Continue?", vbQuestion + vbApplicationModal + vbYesNo, "Save Record?")
If Save_Response <> vbYes Then
Cancel = True
Me.Undo
Else
    If Not Me.Dirty Then
        DoCmd.Close acForm, Me.Name
    Else
        BoolDirty = True
    End If
End If
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Call myBad
Select Case DataErr
Case 3314
    DoCmd.Beep
    MsgBox "A required field is blank.", vbCritical
    Response = acDataErrContinue
Case 2113
    DoCmd.Beep
    MsgBox "Wrong Data Type. " & vbCr & _
    "Example: You may have entered text in a date field.", vbCritical
    Response = acDataErrContinue
Case 2169
    If MsgBox("Record is incomplete. Your changes will not be saved." & vbCr & _
    "Do you want to review your changes?", vbYesNo, "Record Error") = vbYes Then
    Response = acDataErrContinue
    Else
    Response = acDataErrContinue
    End If
Case Else
    Response = acDataErrDisplay
End Select
Debug.Print Err.Number & "   " & Err.Description
End Sub

[Denburt]

Tried and tested copy/paste solution:

Option Compare Database
Option Explicit
Dim BoolDirty As Boolean
 
Private Sub Command0_Click()
On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
If BoolDirty = True Then
    DoCmd.Close acForm, Me.Name
End If
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Save_Response As Integer
Save_Response = MsgBox("You Are About To Save This Record.  Do You Want To Continue?", vbQuestion + vbApplicationModal + vbYesNo, "Save Record?")
If Save_Response <> vbYes Then
Cancel = True
Me.Undo
Else
    If Not Me.Dirty Then
        DoCmd.Close acForm, Me.Name
    Else
        BoolDirty = True
    End If
End If
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Call myBad
Select Case DataErr
Case 3314
    DoCmd.Beep
    MsgBox "A required field is blank.", vbCritical
    Response = acDataErrContinue
Case 2113
    DoCmd.Beep
    MsgBox "Wrong Data Type. " & vbCr & _
    "Example: You may have entered text in a date field.", vbCritical
    Response = acDataErrContinue
Case 2169
    If MsgBox("Record is incomplete. Your changes will not be saved." & vbCr & _
    "Do you want to review your changes?", vbYesNo, "Record Error") = vbYes Then
    Response = acDataErrContinue
    Else
    Response = acDataErrContinue
    End If
Case Else
    Response = acDataErrDisplay
End Select
Debug.Print Err.Number & "   " & Err.Description
End Sub

Good luck let us know how it goes.

[evn678]

Thanks for the help!

My final solution was to save the values of all the controls before exiting the beforeupdate sub and then restore all the values before exiting the unload sub. It may not be pretty, but it works. Here is the relevant code (there is a lot of other code in the form that I have omitted).

Ed

Option Compare Database
Option Explicit
Private saveBtnClicked As Boolean
Private cancelBtnClicked As Boolean
Private formCancel As Boolean
Private editSaveData As New Collection 'a collection of parameters or values, all strings
Const SAVEFORM As Byte = 1
Const FILLFORM As Byte = 2
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim Response As VbMsgBoxResult
Dim Msg, Title As String
Dim Style As VbMsgBoxStyle
 
On Error GoTo Err_Form_BeforeUpdate
 
'       User clicked the 'X' control on the window border
If (Not saveBtnClicked) And (Not cancelBtnClicked) Then
    Msg = "Are you sure you want to undo your changes?" ' Define message.
                ' Define buttons.
    Style = vbYesNo + vbApplicationModal + vbDefaultButton2 + vbQuestion
    Title = "Cancel Edit Ticket"    ' Define title.
        ' Display message.
    Response = msgBox(Msg, Style, Title)
    Cancel = True
 
    If Response = vbNo Then     ' User does not want to loose changes
        formCancel = False      ' Cancels Unload, otherwise Unload is allowed
        procFormData SAVEFORM   ' Save the form data to be restored in Unload
    Else
        formCancel = True       ' Allows Unload, otherwise Unload is canceled
    End If
End If
 
Exit_Form_BeforeUpdate:
    Exit Sub
 
Err_Form_BeforeUpdate:
    msgBox "FrmTT_Edit_One:Form_BeforeUpdate Error- " & Err.Description, _
                vbExclamation, "Error No: " & Err.Number
    Resume Exit_Form_BeforeUpdate
 
End Sub
 
Private Sub Form_Error(DataErr As Integer, Response As Integer)
 
Select Case DataErr
    Case 2169
        Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
    End Select
End Sub
 
Private Sub Form_Open(Cancel As Integer)
    saveBtnClicked = False
    cancelBtnClicked = False
    formCancel = True
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
If (Not saveBtnClicked) And (Not cancelBtnClicked) And (Not formCancel) Then
    Cancel = True
    procFormData FILLFORM   ' Restore form data
End If
 
End Sub
 
Private Sub frmSaveData(ByVal strParmName As String, ByVal varValue As Variant)
 
On Error GoTo Err_frmSaveData
 
editSaveData.Remove strParmName   ' if doesn't exist, no error
editSaveData.Add Item:=varValue, Key:=strParmName   '   replaces prior value
 
Exit_frmSaveData:
    Exit Sub
 
Err_frmSaveData:
    Select Case Err.Number
        Case 5
             Resume Next ' no value was already set for the parm
        Case Else
            msgBox "FrmTT_Edit_One:frmSaveData Error- " & Err.Description, _
                vbExclamation, "Error No: " & Err.Number
    End Select
    Resume Exit_frmSaveData
 
End Sub
 
Public Function frmGetData(ByVal strParmName As String) As Variant
 
On Error GoTo Err_frmGetData
 
frmGetData = editSaveData.Item(strParmName)
 
Exit_frmGetData:
    Exit Function
 
Err_frmGetData:
    Select Case Err.Number
        Case 5 ' null
            frmGetData = Null
            Resume Exit_frmGetData
        Case Else
            msgBox "FrmTT_Edit_One:frmGetData Error- " & Err.Description, _
                vbExclamation, "Error No: " & Err.Number
    End Select
    Resume Exit_frmGetData
 
End Function
 
Private Sub procFormData(action As Byte)
 
Dim ctl As Access.Control
Dim savName As String
 
For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) Then
        savName = "SAV" & ctl.Name
        Select Case action
            Case SAVEFORM
                frmSaveData savName, ctl.Value
            Case FILLFORM
                ctl.Value = frmGetData(savName)
        End Select
    End If
Next ctl
 
End Sub
 

This question is closed. The thread may be closed.
Thanks again!

Ed