Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Before Update Routine Not working as expected MS-Access 2003

Routine to ask user if they want to save amendments.

Yes = Save
No= Don't Save and Close Form
Cancel=Don't save at all and leave form open with amendments intact

But it does not work as it should. I get Error 2101 but this should be handled by error routine.

Problem is the cmdClose has to be clicked twice when No is selected
Selecting Yes or Cancel works as expected!

Any ideas?

Regards

Peter


Option Compare Database
Option Explicit
____________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler
Select Case MsgBox("Save changes?", vbYesNoCancel)
      Case vbNo 'Undo update and close the form.
            Cancel = True
            Me.Undo
      Case vbCancel ‘ Ignore update keeping edits, but don’t close form
            Cancel = True
End Select
Exit_Proc_Form_BeforeUpdate:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " / " & Err.Description
Resume Exit_Proc
End Sub
____________________________________________-
Private Sub cmdClose_Click()
On Error GoTo Err_Handler
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, "frmSMSMessagesPredifined"
Exit_Proc:
Exit Sub
Err_Handler:
Select Case Err
Case 3314, 2101, 2115 ‘Ignore as they are all can’t save errors.
Case Else
MsgBox "Error " & Err.Number & " / " & Err.Description
End Select
End Sub
______________________________________________________-
I can make it work by using the following:

Option Compare Database
Option Explicit
Dim fOKToClose As Boolean
__________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler
Select Case MsgBox("Save changes?", vbYesNoCancel)
      Case vbNo 'Undo and close.
            Cancel = True
            Me.Undo
            fOKToClose = True
      Case vbCancel 'Don't close, and don't save.
            Cancel = True
            fOKToClose = False
End Select
Exit_Proc_Form_BeforeUpdate:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " / " & Err.Description
Resume Exit_Proc
End Sub
_______________________________________________
Private Sub cmdClose_Click()
On Error GoTo Err_Handler
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, "frmSMSMessagesPredifined"
Exit_Proc:
Exit Sub
Err_Handler:
Select Case Err
Case 3314, 2101, 2115 ‘Ignore as they are all can’t save errors.
If fOKToClose Then Resume Next Else Resume Exit_Proc
Case Else
MsgBox "Error " & Err.Number & " / " & Err.Description
End Select
End Sub
0
PeterRollinson
Asked:
PeterRollinson
  • 3
1 Solution
 
rockiroadsCommented:
when u do

Cancel = True

it forces u to stay on the form, that could be the reason why

thats what the use of Cancel is for, it doesnt do the update and leaves focus back on the form

0
 
rockiroadsCommented:
here is the logic in your first code

on cmdClose, u reset the Dirty flag, which triggers the call to BeforeUpdate
then u attempt to close the form

On BeforeUpdate, u set Cancel = True, which means we dont do the form operation
yet you set cancel = true, which forces focus on the form

when u click no, it undo's your changes
then set cancel = true so that focus remains on form
but when u exit out from BeforeUpdate, its causing an error.


If u uncomment out your Cancel = True on the No select (well u said u wanted to exit out of the form)
it does not trigger an error and next command - which is closed gets called

e.g.

    Select Case MsgBox("Save changes?", vbYesNoCancel)
         Case vbNo 'Undo update and close the form.
'THIS LINE NOT NEEDED AS U WANT TO GET OUT OF THE FORM
            'Cancel = True
              Me.Undo
         Case vbCancel 'Ignore update keeping edits, but don’t close form
              Cancel = True
    End Select



0
 
PeterRollinsonAuthor Commented:
You are right of course :)

Thanks for the quick response.

Peter
0
 
rockiroadsCommented:
No worries, glad to have helped
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now