Cancelling a Record Update with BeforeUpdate Event

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

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(Can cel 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.

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

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.

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

Expand|Select|Wrap|Line Numbers

1. Private Sub Form_BeforeUpdate(Cancel As Integer)
2. Save_Response = MsgBox("You Are About To Save This Record.  Do You Want To Continue?", vbQuestion + vbApplicationModal + vbYesNo, "Save Record?")
3. If Save_Response <> vbYes Then
4. Cancel = True
5. Me.Undo
6. End If
7. End Sub
8. Private Sub Form_Error(DataErr As Integer, Response As Integer)
9. Select Case DataErr
10. Case 2169
11.     Response = acDataErrContinue
12. Case Else
13.     Response = acDataErrDisplay
14. End Select
15. End Sub
16. Private Sub Form_Unload(Cancel As Integer)
17. Cancel = True
18. End Sub
19.  

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 acDataErrContin ue prevents the Access pop up.

But there is one small problem - after the acDataErrContin ue, the form loses all data updates as if a Me.Undo was executed. I suspect that using the acDataErrContin ue 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 acDataErrContin ue 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

Tried and tested copy/paste solution:

Tried and tested copy/paste solution:

Expand|Select|Wrap|Line Numbers

1. Option Compare Database
2. Option Explicit
3. Dim BoolDirty As Boolean
4.  
5. Private Sub Command0_Click()
6. On Error Resume Next
7. DoCmd.RunCommand acCmdSelectRecord
8. DoCmd.RunCommand acCmdSaveRecord
9. If BoolDirty = True Then
10.     DoCmd.Close acForm, Me.Name
11. End If
12. End Sub
13.  
14. Private Sub Form_BeforeUpdate(Cancel As Integer)
15. Dim Save_Response As Integer
16. Save_Response = MsgBox("You Are About To Save This Record.  Do You Want To Continue?", vbQuestion + vbApplicationModal + vbYesNo, "Save Record?")
17. If Save_Response <> vbYes Then
18. Cancel = True
19. Me.Undo
20. Else
21.     If Not Me.Dirty Then
22.         DoCmd.Close acForm, Me.Name
23.     Else
24.         BoolDirty = True
25.     End If
26. End If
27. End Sub
28. Private Sub Form_Error(DataErr As Integer, Response As Integer)
29. 'Call myBad
30. Select Case DataErr
31. Case 3314
32.     DoCmd.Beep
33.     MsgBox "A required field is blank.", vbCritical
34.     Response = acDataErrContinue
35. Case 2113
36.     DoCmd.Beep
37.     MsgBox "Wrong Data Type. " & vbCr & _
38.     "Example: You may have entered text in a date field.", vbCritical
39.     Response = acDataErrContinue
40. Case 2169
41.     If MsgBox("Record is incomplete. Your changes will not be saved." & vbCr & _
42.     "Do you want to review your changes?", vbYesNo, "Record Error") = vbYes Then
43.     Response = acDataErrContinue
44.     Else
45.     Response = acDataErrContinue
46.     End If
47. Case Else
48.     Response = acDataErrDisplay
49. End Select
50. Debug.Print Err.Number & "   " & Err.Description
51. End Sub

Good luck let us know how it goes.

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

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

Ed