How to cancel a form from closing when it's dirty

[Schof]

Currently I have code in the form's BeforeUpdate event to notify the user if the record is dirty, telling them that they must save the record before proceeding. The event is then cancelled and everything works fine.

The problem I need help with is that when the user tries to close a dirty form using the close button (X) in the top right hand corner of the form, after my notification message, MS-Access displays the message:

You can't save this record at this time.
Ms-Access may have necountered 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?

I have tried putting code in the the form's UnLoad event (as I have seen suggested in a million posts) but the message comes before the event occurs. I have too many forms to remove the close button (X) and manually add my own button control. Any thoughts on how I can handle this would be appreciated.

 

[Rich]

I use a function to do this try,
Public Function Conf()
Dim Msg, Style, Title, Response, MyString, frm As Form

Beep
Msg = "You have altered existing data are you sure you wish to do this?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm record change"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Screen.ActiveForm.Undo
Exit Function
DoCmd.RunCommand acCmdSaveRecord

End If
End Function


On the before update event of the forms you wish to validate changes add
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord And Me.Dirty Then
Call Conf
End If
End Sub

 

[Schof]

That is pretty much what I have (I don't have it in a function) but I think you will run into the same problem I am. When you are editing a record, click on the close (X) button. When you get your prompt say No. Don't you get the prompt (from original thread) I am trying to avoid?

 

[Pat Hartman]

I think the problem is being caused because you are attempting to run the BeforeUpdate event recursively.

1. The ONLY time the BeforeUpdate event is executed is if the form is dirty so there is NO point in testing the dirty flag. This event is the LAST event executed prior to saving a new or changed record. It is ALWAYS executed when the form is dirty no matter how the user trys to exit from the current record (short of a power off or reboot). So whether the user uses the navigation buttons to scroll to the next record or the x to close the form or some close button that you have added. Access makes sure that data is not lost.

2. When you put code in the close event to try to prevent a user from exiting without saving, you are actually closing the barn door after the horse is out The record has already been saved automatically by Access prior to the execution of the close event.

A simple way to see if your code is really working the way you think is to put message boxes in all the relevant events so you can see for yourself the order in which the events are executed.

The sequence is BeforeUpdate, Unload, Close.

Both the BeforeUpdate and Unload events can be Cancelled but the Close cannot. So do your editing in the BeforeUpdate event and cancel the event with (Cancel = True) if you don't want the record to be saved. If you also want to prevent the form from being closed if the user was trying to save bad data, you'll need a global variable that you can check from the Unload event to use so you can set the Cancel parameter if necessary.

 

[Rich]

Schof, sorry I didn't see your further question, but the code will undo the changes if the user selects No and access will not therefore display the record cannot be saved message. Did you try the function?

 

[Pat Hartman]

Rich, I hope you don't mind if I make a few comments.
1. If you are in the form's BeforeUpdate event, there is no point in testing the dirty flag since you only get to the BeforeUpdate event if the form is dirty so it will always be true.
2. In the Conf() function, the DoCmd.Runcommand is never being executed. It immediately follows an Exit Function. This doesn't really matter since the record will be saved automatically anyway.
3. It is a good thing that the doCmd is not being executed because if you were to move it to after the End If, which is where I think you intended for it to be, it would cause run time error 2046 - the command or action save record is not available now ..... The reason for this is because if Access allowed you to execute the save command from within an event triggered by a record save, it would be put into an endless loop. You are in a function that was called from the form's BeforeUpdate event. Executing a save record at this point would cause Access to re-enter the BeforeUpdate event without first finishing its current path through the code.

 

[Rich]

Thanks Pat, I never mind you making comments they're as always, an invaluable source. Happy New Year

 

[Schof]

Pat, do you know if it is possible to determine whether the close button (X) has been pressed? I could then display the appropriate message from the Form_BeforeUpdate procedure telling the user that they must either save the record before proceeding (if X not pressed) or that their changes are being undone (if X is pressed). At least that's the logic I will implement if I can determine why the procedure is being executed...