Cancel button on bound form

[Sreedhar]

Using bound forms in Access 2003.

If I want to provide a cancel button on a bound form that is
-- disabled when the form opens (for new or existing records),
and
-- enabled -if data is entered for new record
-if data is modified for existing record,
and, if clicked,
--will not allow the new record to be added or
--modifications to the existing record are undone,

what code should I write and where should I place it ? Please elaborate with
sample code.

Thanks.

 

[Tom van Stiphout]

On Sat, 18 Jul 2009 08:16:01 -0700, Sreedhar

You're trying to change how Access works out of the box, rarely a
productive or simple way to go. It would be MUCH better to work within
what Access has to offer, which includes:
If you hit Esc, the edit in the current control is undone.
If you hit Esc again, the edit in the current record is undone.
If you have already saved the record explicitly, or if Access saved it
for you implicitly (e.g. step into a subform control), you can Delete
the record by selecting the Record Selector and clicking Delete.

Try working with that. It will be much easier, and much more
consistent with how other Access applications work.

-Tom.
Microsoft Access MVP

 

[Belinda]

Tom
Can this double "ESC" be coded to a button on a form? If so, what would be
the code to tie the button to causing the ESC button to being activated/hit
twice?
Thank you!

 

[Linq Adams via AccessMonster.com]

The standard way of doing this, rather than having a "Cancel" button, is to
always check with the user before saving the new record/edited record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion +
vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo +
vbDefaultButton1, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub

 

[Bob Larson]

I believe you would need to add the Cancel = True part as well.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?",
vbQuestion +
vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Cancel = True
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo +
vbDefaultButton1, "Save This Record ???") = vbNo Then
Cancel = True
Me.Undo
End If
End If
End Sub