Prompt before closing a Dirty Form

[djossh]

Hi. I have a form with subforms to enter various data.. Whenever I closed my form using the "x" on the upper right side or the form.. Whatever data/information that are left in the form will be saved to my table. (this happens when a user enter some data then suddenly realized not to continue entering data and closes the form). I want to undo this but i cant find a way to work.. here are some code that I found but doesn't work in my forms.. any help is appreciated... thanks..

here are similar code that I found but this is done in BeforeUpdate event

If Me.Dirty Then
If MsgBox("Do you want to save the changes?", vbYesNo) = vbNo Then
'Undo all the changes
Me.Undo
End If
End If
End Sub

 

[KenHigg]

I'm thinking just remove or disable the close button - ?

 

[VilaRestal]

That is a bug in Access. I think, you can get the behaviour you want with:

Option Compare Database
Option Explicit

Private UndoOnClose As Boolean

Private Sub Form_Current()
    UndoOnClose = False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty Then
        If MsgBox("Do you want to save the changes?", vbYesNo) = vbNo Then
            Me.Undo
            UndoOnClose = True
        End If
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If UndoOnClose Then Me.Undo
End Sub

 

[djossh]

That is a bug in Access. I think, you can get the behaviour you want with:

Option Compare Database
Option Explicit
 
Private UndoOnClose As Boolean
 
Private Sub Form_Current()
    UndoOnClose = False
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty Then
        If MsgBox("Do you want to save the changes?", vbYesNo) = vbNo Then
            Me.Undo
            UndoOnClose = True
        End If
    End If
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    If UndoOnClose Then Me.Undo
End Sub

Thanks for the Reply.. I tried the code but Im afraid this is not what I'm looking for.. My form have a subform, and my Tab Orders are to enter data in the first 3 Fields in my form then it will go to subform to enter another data.... what's happening is everytime I enter data on last fields in my form (3rd Field) before the subform.. MsgBox will pop up if I want to save or not....

That code was totally fine if the user realized to close the form just before the 3rd Field in my form, because the Msgbox will not pop up since it will fire only after the 3rd Field and before Focus is set to subform.. I Know I can easily remove the "X" button in my form and create my own button to close my form and it will be easy to add Undo/clear all the data before closing..

But for the sake of learning.. this will be good inputs once I got it works... Thank you and I really appreciate the help...

 

[VilaRestal]

Yeah, sorry I was getting it mixed up with Cancelling Before Update when the form's closing. Undoing changes isn't a bug or a problem.


If it's an issue with subforms then you're probably best off doing what Ken says - remove the close button and put your own on there.

Shifting the focus to the subform will require the main form's changes to be saved. Each current event in the subform will save changes to the previous record there.

To get round that (to allow users to undo all changes) will involve some sophisticated stuff (probably involving temporary tables)

 

[djossh]

Yeah, sorry I was getting it mixed up with Cancelling Before Update when the form's closing. Undoing changes isn't a bug or a problem.


If it's an issue with subforms then you're probably best off doing what Ken says - remove the close button and put your own on there.

Shifting the focus to the subform will require the main form's changes to be saved. Each current event in the subform will save changes to the previous record there.

To get round that (to allow users to undo all changes) will involve some sophisticated stuff (probably involving temporary tables)

Yes I agree... I read some blogs and most of the suggestions is to have a DELETE QUERY... anyhow.... Thank you so much.... I really appreciate it..

 

[gemma-the-husky]

why does the first code you posted not work?

the form closing tries to fire the beforeupdate which you reject - then the form closes. it should work, i think.


one way of maybe sorting this is to have a mandatory field (such as originator) which you actually set in the forms before update event

if this never gets set, then the record is rejected - but on closing the form you then get the rather ugly "access cannot save the record at this time".

you could possibly intercept THAT error n the forms error event, and disregard it there.

but you are looking at the accesserror object, not the err object

 

[VilaRestal]

I think it's the saving of changes in the subforms the OP wants to undo Gemma.

 

[djossh]

why does the first code you posted not work?

the form closing tries to fire the beforeupdate which you reject - then the form closes. it should work, i think.


one way of maybe sorting this is to have a mandatory field (such as originator) which you actually set in the forms before update event

if this never gets set, then the record is rejected - but on closing the form you then get the rather ugly "access cannot save the record at this time".

you could possibly intercept THAT error n the forms error event, and disregard it there.

but you are looking at the accesserror object, not the err object

Thank you for the reply... Yes the first code will work if my focus is still on the mainform before the user close the form... but in the instance when the focus is on my subform.. the data in the mainform are automatically save and vice versa...