ms access form closing ask save yesnocancel

Question

I have put the VBA in Unload Form Event in ms access 2010

Private Sub Form_Unload(Cancel As Integer)

Dim strMsg As String
   Dim iResponse As Integer

   ' Specify the message to display.
   strMsg = "Do you wish to save the changes?" & Chr(10)
   strMsg = strMsg & "Click Yes to Save or No to Discard changes."

   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save Record?")

   ' Check the user's response.
   If iResponse = vbYes Then

      ' Undo the change.
      DoCmd.RunCommand acCmdSave
   End If

   If iResponse = vbNo Then


      ' Undo the change.
      DoCmd.RunCommand acCmdUndo
      End If



   If iResponse = vbCancel Then

      ' Undo the change
   Cancel = True


   End If



End Sub

If data is changed then the above code is working fine, then yes to save & close, No to undo & close and cancel to cancel event and remain on the form but when the data is unchanged then Yes button is working fine, but NO button do not close the form

Where I m mistaking ?

If nothing has changed, then there is no reason to prompt the user at all. Simply add before your first statement: If Not Me.Dirty Then Exit Sub End If — AVG

Mark C. Mark C. · Accepted Answer · 2014-06-08T17:17:24

Your question is too vague because you simply state "NO button do not close the form."

You can do:

DoCmd.Close

See here:

Private Sub cmdCloseForm_Click() 
On Error GoTo Err_cmdCloseForm_Click 

 DoCmd.RunCommand acCmdUndo  'OR Me.Undo - test which works best for your situation
 DoCmd.Close 

Exit_cmdCloseForm_Click: 
 Exit Sub 

Err_cmdCloseForm_Click: 
 MsgBox Err.Description 
 Resume Exit_cmdCloseForm_Click 

End Sub

On Me.Undo from Allen Browne:

Me.Undo cancels the edits in a specific form, so that it is no longer dirty. Once the form is no longer dirty, no further undo is possible, because it has reached the desired state. However, Me. represents the form that is in focus, so your form must have focus to perform the Me.Undo command.

ms access form closing ask save yesnocancel

1 Answers