By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

Cancelling a Record Update with BeforeUpdate Event

P: 8
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
May 15 '07 #1
Share this Question
Share on Google+
8 Replies


JConsulting
Expert 100+
P: 603
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
Expand|Select|Wrap|Line Numbers
  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
  6.  
May 16 '07 #2

P: 8
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.
May 16 '07 #3

Denburt
Expert 100+
P: 1,356
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.  
May 16 '07 #4

JConsulting
Expert 100+
P: 603
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.
May 16 '07 #5

P: 8
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 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
May 17 '07 #6

Denburt
Expert 100+
P: 1,356
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
May 17 '07 #7

Denburt
Expert 100+
P: 1,356
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.
May 17 '07 #8

P: 8
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private saveBtnClicked As Boolean
  4. Private cancelBtnClicked As Boolean
  5. Private formCancel As Boolean
  6. Private editSaveData As New Collection 'a collection of parameters or values, all strings
  7. Const SAVEFORM As Byte = 1
  8. Const FILLFORM As Byte = 2
  9.  
  10. Private Sub Form_BeforeUpdate(Cancel As Integer)
  11.  
  12. Dim Response As VbMsgBoxResult
  13. Dim Msg, Title As String
  14. Dim Style As VbMsgBoxStyle
  15.  
  16. On Error GoTo Err_Form_BeforeUpdate
  17.  
  18. '       User clicked the 'X' control on the window border
  19. If (Not saveBtnClicked) And (Not cancelBtnClicked) Then
  20.     Msg = "Are you sure you want to undo your changes?" ' Define message.
  21.                 ' Define buttons.
  22.     Style = vbYesNo + vbApplicationModal + vbDefaultButton2 + vbQuestion
  23.     Title = "Cancel Edit Ticket"    ' Define title.
  24.         ' Display message.
  25.     Response = msgBox(Msg, Style, Title)
  26.     Cancel = True
  27.  
  28.     If Response = vbNo Then     ' User does not want to loose changes
  29.         formCancel = False      ' Cancels Unload, otherwise Unload is allowed
  30.         procFormData SAVEFORM   ' Save the form data to be restored in Unload
  31.     Else
  32.         formCancel = True       ' Allows Unload, otherwise Unload is canceled
  33.     End If
  34. End If
  35.  
  36. Exit_Form_BeforeUpdate:
  37.     Exit Sub
  38.  
  39. Err_Form_BeforeUpdate:
  40.     msgBox "FrmTT_Edit_One:Form_BeforeUpdate Error- " & Err.Description, _
  41.                 vbExclamation, "Error No: " & Err.Number
  42.     Resume Exit_Form_BeforeUpdate
  43.  
  44. End Sub
  45.  
  46. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  47.  
  48. Select Case DataErr
  49.     Case 2169
  50.         Response = acDataErrContinue
  51.     Case Else
  52.         Response = acDataErrDisplay
  53.     End Select
  54. End Sub
  55.  
  56. Private Sub Form_Open(Cancel As Integer)
  57.     saveBtnClicked = False
  58.     cancelBtnClicked = False
  59.     formCancel = True
  60. End Sub
  61.  
  62. Private Sub Form_Unload(Cancel As Integer)
  63.  
  64. If (Not saveBtnClicked) And (Not cancelBtnClicked) And (Not formCancel) Then
  65.     Cancel = True
  66.     procFormData FILLFORM   ' Restore form data
  67. End If
  68.  
  69. End Sub
  70.  
  71. Private Sub frmSaveData(ByVal strParmName As String, ByVal varValue As Variant)
  72.  
  73. On Error GoTo Err_frmSaveData
  74.  
  75. editSaveData.Remove strParmName   ' if doesn't exist, no error
  76. editSaveData.Add Item:=varValue, Key:=strParmName   '   replaces prior value
  77.  
  78. Exit_frmSaveData:
  79.     Exit Sub
  80.  
  81. Err_frmSaveData:
  82.     Select Case Err.Number
  83.         Case 5
  84.              Resume Next ' no value was already set for the parm
  85.         Case Else
  86.             msgBox "FrmTT_Edit_One:frmSaveData Error- " & Err.Description, _
  87.                 vbExclamation, "Error No: " & Err.Number
  88.     End Select
  89.     Resume Exit_frmSaveData
  90.  
  91. End Sub
  92.  
  93. Public Function frmGetData(ByVal strParmName As String) As Variant
  94.  
  95. On Error GoTo Err_frmGetData
  96.  
  97. frmGetData = editSaveData.Item(strParmName)
  98.  
  99. Exit_frmGetData:
  100.     Exit Function
  101.  
  102. Err_frmGetData:
  103.     Select Case Err.Number
  104.         Case 5 ' null
  105.             frmGetData = Null
  106.             Resume Exit_frmGetData
  107.         Case Else
  108.             msgBox "FrmTT_Edit_One:frmGetData Error- " & Err.Description, _
  109.                 vbExclamation, "Error No: " & Err.Number
  110.     End Select
  111.     Resume Exit_frmGetData
  112.  
  113. End Function
  114.  
  115. Private Sub procFormData(action As Byte)
  116.  
  117. Dim ctl As Access.Control
  118. Dim savName As String
  119.  
  120. For Each ctl In Me.Controls
  121.     If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) Then
  122.         savName = "SAV" & ctl.Name
  123.         Select Case action
  124.             Case SAVEFORM
  125.                 frmSaveData savName, ctl.Value
  126.             Case FILLFORM
  127.                 ctl.Value = frmGetData(savName)
  128.         End Select
  129.     End If
  130. Next ctl
  131.  
  132. End Sub
  133.  
This question is closed. The thread may be closed.
Thanks again!

Ed
May 18 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.