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?

 

[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?

 

[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...