Access VBA - prevent closing form

Question

I have a code in Before_Update event, for saving changes. For this I use Msgbox with YesNoCancel prompt, but I cannot prevent closing form when I hit Cancel button. Here is a short sample of my code:

Option Compare Database
Public SomeVariable As Integer

    Private Sub Form_BeforeUpdate(Cancel As Integer)

             If MsgBox("There has been done some changes. You wish to save these changes ?", vbQuestion + vbYesNoCancel, "Save changes") = vbYes Then

              'do nothing and Access saves automatically

             ElseIf vbNo Then
              DoCmd.RunCommand acCmdUndo

             ElseIf vbCancel Then
              SomeVariable = 1
             End If

     End Sub

Private Sub Form_Unload(Cancel As Integer)

If SomeVariable = 1 Then
 SomeVariable=0
 Cancel = True
End If

End Sub

Any way to fix this ?

Paul Ogilvie · Accepted Answer · 2016-04-24 12:11:23Z

2

You must save the return value from MsgBox, then proceeed accordingly:

        Dim vbAnswer
        vbAnswer= MsgBox("There has been done some changes. You wish to save these changes ?", vbQuestion + vbYesNoCancel, "Save changes")

        if vbAnswer = vbYes Then

          'do nothing and Access saves automatically

         ElseIf vbAnswer= vbNo Then
               ....
         ElseIf vbAnswer= vbCancel Then

answered Apr 24, 2016 at 12:11

Paul Ogilvie

25.4k4 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access VBA - prevent closing form

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related