Implementing a Save, Delete or Cancel MsgBox

[John S. Ford, MD]

I have a subform embedded into a main form. It has some buttons on it that
allow the user to enter a new record and decide whether to save it or cancel
it (cmdNew, cmdSave, cmdCancel buttons). The problem is that the user can
unfortunately do a number of other things that will bypass the save and
cancel buttons:

1) Close the form.
2) Close the database.
3) Move to another record in the subform by the navigation buttons.
4) Probably other ways that I haven't discovered.

Any of these things will cause the new record to be saved probably without
the user realizing it thus allowing bad or incomplete data into the
database.

How can I implement the sort of Save, Delete or Cancel MsgBox seen on
Windows applications where before the application is closed, the MsgBox
arises allowing the user to save or delete prior to closing or actually
cancel the closing? I tried tying some code to the OnUnload event but this
doesn't seem to work (the application closes or the form closes
prematurely).

John

 

[Allen Browne]

The only way to catch all the ways that a record is saved is to use the
BeforeUpdate event of the form.

Form_Unload is too late: the record is already saved by then.

This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox("Save?", vbYesNoCancel)
Case vbYes
'do nothing.
Case vbNo
Cancel = True
Me.Undo
Case vbCancel
Cancel = True
End Select
End Sub

 

[Van T. Dinh]

You need to use the Form_BeforeUpdate Event (of the
Subform) to ask the user whether to save or cancel the
Record currently being edited.

Check Access VB Help on the BeforeUpdate Event of a Form.

When the user moves the Focus from the Subform to the Main
Form, the Record (in the Subform) is also saved
automatically.

HTH
Van T. Dinh
MVP (Access)

 

[John S. Ford, MD]

Thanks! It seems that using the BeforeUpdate event has gotten me closer but
of course it generated a new problem. The cmdNew button ALSO triggered the
BeforeUpdate event thus triggering the MsgBox. How can I tell whether the
BeforeUpdate event was triggered by the cmdNew button or one of the other
ways (ie. closing the form)? I don't want the MsgBox to come up if the
BeforeUpdate event was triggered by the cmdNEW button.

Thanks,
John

 

[test]

Re. your original question, there is *no way* in Access to implement a 100%
reliable "cancel unsaved changes" button.

The reason is, that the focus must leave the current control, before the
button's Click event can fire. But there are various data errors that will
prevent the focus from leaving the current control. So, you can't click the
button (to discard the erroneous data), until you have *fixed* the erroneous
data, or have used some other method (eg. pressing Escape) to discard it.
Catch 22!

If you don't believe me, just make a simple form with a textbox based on a
mandatory (required) table field, and a command button that displays a
message. Create a new record and type some spaces (no other characters) into
the textbox. Now see if you can click the button!

HTH,
TC

 

[Arvin Meyer]

Re. your original question, there is *no way* in Access to implement a 100%
reliable "cancel unsaved changes" button.

I disagree. One can always use unbound forms and write the data to the
table(s) using Insert statements or the Recordset.AddNew method.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

Set a flag, a form-wide variable should do fine, in the OnCurrtent event of
the form. Turn it Off when you dirty the form or click the cmdNew button.
You'll need to experiment with how you want to cancel or not cancel, but
basically the flag is set by diming a variable (air code).

Dim bFlag As Boolean

Sub Form_Current
bFlag = False
End Sub

Sub cmdNEW_Click()
bFlag = True
'... Add New record code
End Sub

Sub Form_BeforeUpdate(Cancel As Integer)
If bFlag = False Then
'... do something
Else
' ... do something else
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Tim]

You could have a hidden check box on the form. When the form Opens,
set the checkbox to False.

When you click cmdNew, set it to True.

In the BeforeUpdate event, have it check the status of the checkbox.
If it's True, skip all the message box stuff and carry on saving and
closing. if it's false, the user must have exited the form by another
means, so you can run the msgbox code.

You could probably do the same with a global variable too, or possibly
one that only exists with the form.

 

[Van T. Dinh]

John

The Form_BeforeUpdate Event fires because the (sub)Form tries to *save* the
Current Record before moving to the New Record that called by your cmdNew
button. If you skip the confirmation, this defeats your requirements as you
detailed in the second last paragraph of your 1st post.

There are ways to stop the MsgBox as pointed out by Arvin and Tina but you
need to make sure in your mind what you want to do first ...

 

[grep]

Um... John, the only time that BeforeUpdate event is going to fire is if
you've actually changed something in the current record. If you've
changed something in the current record, don't you still WANT to make
sure that there's no incomplete or erroneous information, regardless of
whether you're moving to a new record?

grep

 

[TC]

I disagree. One can always use unbound forms and write the data to the
table(s) using Insert statements or the Recordset.AddNew method.

Not if the unbound form has any BeforeUpdate validations! If a BeforeUpdate
validation prevents the focus from leaving that field, it will not be
possible to click the "cancel changes" button to discard that unsaved
change.

TC

 

[Arvin Meyer]

Not if the unbound form has any BeforeUpdate validations! If a BeforeUpdate
validation prevents the focus from leaving that field, it will not be
possible to click the "cancel changes" button to discard that unsaved
change.

It is not necessary to perform all validations in the BeforeUpdate event.
There is a validation property, both in the form and in the table. Code can
also be used to validate in other events, and an event can be Cancelled by
using DoCmd.CancelEvent in code. Since all web apps use unbound forms (even
with Access/JET and ASP), there are plenty of example of using validation,
both locally and on the server.

Additionally, one can use a bound temporary table, or even memory, to hold
the records while doing almost anything to them, then write the resulting
data to the permanent table. There are several ways in Access to 100%
guarantee a reliable cancel button.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[TC]

It is not necessary to perform all validations in the BeforeUpdate event.
There is a validation property, both in the form and in the table. Code can
also be used to validate in other events, and an event can be Cancelled by
using DoCmd.CancelEvent in code. Since all web apps use unbound forms (even
with Access/JET and ASP), there are plenty of example of using validation,
both locally and on the server.

Additionally, one can use a bound temporary table, or even memory, to hold
the records while doing almost anything to them, then write the resulting
data to the permanent table. There are several ways in Access to 100%
guarantee a reliable cancel button.

Sure you can do it by not putting validation in BeforeUpdate events. You can
also do it by ensuring that there are never any records in the table. Or by
preventing the user from opening the application. Or by locking the PC in a
box so the user can not get to it.

My point - surely obvious to most readers - was that all of the usual
attempts to code a Cancel button are doomed to failure, because they do not
consider the focus issue that I described. I do not deny that this can be
solved by ridiculous contortions such as removing all validation code from
the very event that is designed for managing validation. In that regard, I
agree that it is not "100% impossible". It is only 100% impossible for
people who like to do weird things such as using bound forms and/or
BeforeUpdate events for validations.

TC

 

[Arvin Meyer]

Sure you can do it by not putting validation in BeforeUpdate events. You can
also do it by ensuring that there are never any records in the table. Or by
preventing the user from opening the application. Or by locking the PC in a
box so the user can not get to it.

My point - surely obvious to most readers - was that all of the usual
attempts to code a Cancel button are doomed to failure, because they do not
consider the focus issue that I described. I do not deny that this can be
solved by ridiculous contortions such as removing all validation code from
the very event that is designed for managing validation. In that regard, I
agree that it is not "100% impossible". It is only 100% impossible for
people who like to do weird things such as using bound forms and/or
BeforeUpdate events for validations.

I don't want to argue the point, so this will be my last post on the
subject.

Simple Access front-end database applications are usually bound to the data.
Most other applications are unbound. I prefer bound forms because they are
easier to build and use ... except under the condition that the edits are
cancellable. If the edits must be cancellable, there is little choice but to
make unbound forms. Experienced database developers should be comfortable
with both methods, as neither situation is unusual.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[TC]

I don't want to argue the point

Neither do I - which is why I started to get heated.

so this will be my last post on the subject.

No point stopping if there is something worth discussing.

Simple Access front-end database applications are usually bound to the data.
Most other applications are unbound. I prefer bound forms because they are
easier to build and use ...

Agree 100%.

except under the condition that the edits are
cancellable. If the edits must be cancellable, there is little choice but to
make unbound forms.

I don't see that. You simply tell your users to press the Esc key when
they want to cancel pending changes. This gives easy cancellation of
pending changes, while retaining the convenience of using bound forms
& having validation in BeforeUpdate events.

Experienced database developers should be comfortable
with both methods, as neither situation is unusual.

Cheers,
TC

 

[Mamdouh El Nahry]

"Arvin Meyer" <[email protected]> wrote in message

Neither do I - which is why I started to get heated.



No point stopping if there is something worth discussing.



Agree 100%.



I don't see that. You simply tell your users to press the Esc key when
they want to cancel pending changes. This gives easy cancellation of
pending changes, while retaining the convenience of using bound forms
& having validation in BeforeUpdate events.


bye

Cheers,
TC

 

[???]

"Arvin Meyer" <[email protected]> wrote in message

Neither do I - which is why I started to get heated.



No point stopping if there is something worth discussing.



Agree 100%.



I don't see that. You simply tell your users to press the Esc key when
they want to cancel pending changes. This gives easy cancellation of
pending changes, while retaining the convenience of using bound forms
& having validation in BeforeUpdate events.




Cheers,
TC