Preventing close buttons from saving records in MS Access

Question

In a Microsoft Access form, whenever the current record changes, any changes in bound controls are silently saved to the database tables. This is fine, but I don't want it to happen when a user closes a form, because it is the direct opposite of what many people would expect.

The best example is when you try to close an excel file with unsaved changes, it asks whether the changes should be discarded. This is exactly what I'm trying to achieve in Access, but can't find any way to trap the close button's event in VBA.

The form's Unload event is the first event that is triggered when someone clicks the close button, but by then the changes are already written to the database.

Is this at all possible, or do I have to create my own close buttons? I'm comfortable with writing large amounts of code for trivial things like this but I hate having to clutter the GUI.

Musketeer

This may not strictly help, but this is why unbound form's are great to work with. I look at it as this: with bound forms you have to prevent any changes you don't want, and with unbound you only commit the changes you do want, depends which side you like to attack the problem from. However bound form's can be useful, but I really only go for them when I need continuous forms. – Smoky

In a way that's exactly what I'm looking for, but I don't want to lose all of the code plumbing that is done for me when I do bind my controls. – Musketeer

There sadly is where the problem of unbound forms comes in, there's a lot more code work during setup, but is ultimately an easier product to control. – Smoky

Answer 1

You have to work with Form_BeforeUpdate event. Below is an example; however it does create a typical warning message: "You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. ..." - depending on your database settings. You can use simple workaround below to avoid displaying of that message.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Cancel = True
   'Or even better you can check certain fields here (If Then...)

End Sub


Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2169 Then 
        Response = True
    End If
End Sub

Richart

I forgot about the BeforeUpdate event, but wouldn't it trigger before any of the form's close events? – Musketeer

To clarify, I only want cancel = true in the event that the user clicked the form close button. – Musketeer

You can easily disable close (X) button on the form (form properties). – Richart

I still want to allow the user to close the form but they somehow need to be informed that their changes will be saved. Even better would be if they could undo the changes before closing the form. It's just so annoying that there is no BeforeUnload event. – Musketeer

Answer 2

Sean gave an almost correct answer but it leaves gaps.

In general, the FORM's BeforeUpdate is the most important form event. It is your LAST line of defense and ALWAYS runs prior to a record being saved regardless of what prompted the save (form close, new record, your own save button, clicking into a subform, etc.) Although I occasionally use the control's BeforeUpdate event just so the user gets the error message sooner, the bulk of the validation code I write runs in the Form_BeforeUpdate event. This is the event you MUST use if you want to ensure that certain controls are not empty. No control level event will do this reliably for all situations. Primarily because if the control never gets focus, no control level event ever fires. Form_BeforeUpdate is also the event you would use if your validation involves multiple fields. If you are using any other control or event level event, you are wasting your time. There is always away around your "trap" and your table almost certainly contains invalid data.

Regarding the OP's question. If you want to force people to use your own save button and prompt them if they don't then you need a form level variable as Sean's suggestion implied. The only difference, is that you need to set it to False, in the form's Current event NOT the Open event. You want the flag to be reset for EVERY new record, not just when the form opens. Then you set it to True in your save button click event, just before you force the record to save with DoCmd.RunCommand acCmdSaveRecord.

Then finally, in the Form_BeforeUpdate event, you check the value of the variable.

If bClose = False Then
   If MsgBox("Do you want to save the changes?", vbYesNo) = vbNo Then
       Cancel = True
       If MsgBox("Do you want to discard the Changes?", vbYesNo) = vbYes Then           
            Me.Undo
       End If
       Exit Sub
   End If
End If

Suffix

Formatting you code better would improve your answer. – Suppositive

That's actually really smart. Too bad this didn't get to me 3 years ago. – Musketeer

Answer 3

this is code I have that checks to see if the form is being closed or saved.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not UsingSaveButton Then
    If MsgBox("Abandon Data?", vbInformation + vbYesNo) = vbNo Then
        Cancel = True
    Else
        DoCmd.RunCommand acCmdUndo
    End If
End If
End Sub

I have a Boolean Flag that is set to False on loading, and then when my Save button is used, I set it to true to allow the update to run through.
If the flag is not set, then they are leaving the record (either through going to a different record, or closing the form) so I ask if they actually want to save the changes.
The Cancel = True aborts the exit of the form or the move to a different record if any changes have been made.
The DoCmd.RunCommand acCmdUndo undoes any changes so they are not saved.

Coccid

This sounds really good. I'll assume that UsingSaveButton = False goes into Form_AfterUpdate? – Musketeer

I use it in the save eoutine. put UsingSaveButton=True at the beginning, and set it to false at the end (making sure that any error handling also sets the value to false. Set the value to false on form load, or on current event – Coccid

Answer 4

In the Form in the 'On Unload' event add the below code.

DoCmd.RunCommand acCmdUndo
DoCmd.Quit

Records no longer being saved when users close a form in any way.

Pennsylvanian

Answer 5

Actually, you cannot trap this, Access is working directly upon the table, every change is already being saved when the field looses the focus by moving to another field, record or a button.

Actually imho this is a great advantage compared to Excel

If you really want a behaviour similar to Excel you'd need to work on a copy of the table and some code for updating.

Balkanize

I was always under the impression that bound countrols are actually linked to fields of the form's RecordSet property. Is this a wrong assumption? – Musketeer

@StevenDotNet, a record is not saved until a save is executed, or a move to a different (or new) record is executed, or the form is closed. – Coccid

Which is similar to how you must call RecordSet.Update() to apply changes. Except that the form updates the recordset even when I don't want it to. – Musketeer

Answer 6

** you can use exit button with this code**

Private Sub Button_Click()
If Me.Dirty = True Then
   If MsgBox(" Save Change ", vbYesNo) = vbYes Then
      Me.Dirty = False
   Else
      Me.Undo
   End If
End If
DoCmd.Close acForm, "FormName"
End Sub

Showdown

I didn't downvote this answer, but I guess it was downvoted because it's just code without a description. – Musketeer