To Save or not to Save, that is the question.

I have a form, bound to a table. On closing the form, I would like the user to have the option of saving any data he has entered or not. The choice would be made via a command button which would invoke a standard message box offering the usual Yes, No or Cancel options.

I believed, at first, that I could achieve this by specifying the acSaveYes or acSaveNo arguments associated with the Close method. Much to my consternation, however, I discovered that these parameters appear to do absolutely nothing. All entries or amendments on the form are saved to the underlying table irrespective of which Save argument is chosen.

The only viable alternative, at the moment, seems to be to bind the form to a facsimile buffer table and then use the Yes/No option to determine whether or not the buffer is appended to the main table. I have had a look at Rollback but discounted it as being far too arcane. Have I missed something obvious?

Peter

Depending on how you've modified your form, there could be multiple ways to
close it. When a form bound to a table closes, Access saves the record
bound to the form.

If you want the option of not saving a record, you'll need to intercept the
form before it has closed. One approach would be to embed code in the
BeforeUpdate event of the form that tests for whether the form is "dirty"
(i.e., changes made). If dirty, you're "Do you wish to save this?" message
could be popped-up and the choice responded to, either undoing changes or
saving them.

--
Good luck

Jeff Boyce
Access MVP

Have you tried an "Undo" button. Look at the Command
Buttons its under "Record Operations".

One note if the No changes are made and you select the
Undo Button you get an error. To stop that from happening
add the following code:

If Me.Dirty then
The Undo Instructions
end if

A form is "Dirty" when there have been changes!


-----Original Message-----
I have a form, bound to a table. On closing the form, I
would like the user to have the option of saving any data
he has entered or not. The choice would be made via a
command button which would invoke a standard message box
offering the usual Yes, No or Cancel options.

I believed, at first, that I could achieve this by
specifying the acSaveYes or acSaveNo arguments associated
with the Close method. Much to my consternation,
however, I discovered that these parameters appear to do
absolutely nothing. All entries or amendments on the
form are saved to the underlying table irrespective of
which Save argument is chosen.

The only viable alternative, at the moment, seems to be
to bind the form to a facsimile buffer table and then use
the Yes/No option to determine whether or not the buffer
is appended to the main table. I have had a look at
Rollback but discounted it as being far too arcane. Have
I missed something obvious?

.

On Mon, 31 May 2004 08:01:04 -0700, "Peter Hallett"
wrote:

I have a form, bound to a table. On closing the form, I would like the user to have the option of saving any data he has entered or not. The choice would be made via a command button which would invoke a standard message box offering the usual Yes, No or Cancel options.

I believed, at first, that I could achieve this by specifying the acSaveYes or acSaveNo arguments associated with the Close method. Much to my consternation, however, I discovered that these parameters appear to do absolutely nothing. All entries or amendments on the form are saved to the underlying table irrespective of which Save argument is chosen.

It's a common source of confusion! The acSave options refer to saving
(or not saving) *design changes to the structure of the form*, not to
saving the data.

The only viable alternative, at the moment, seems to be to bind the form to a facsimile buffer table and then use the Yes/No option to determine whether or not the buffer is appended to the main table. I have had a look at Rollback but discounted it as being far too arcane. Have I missed something obvious?

Perhaps. You can put VBA code in the Form's BeforeUpdate event, which
will fire whenever and however the user attempts to save a record.
Here's some sample code:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
iAns = MsgBox("OK to save this record?", vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing, Access will save the record
Case vbNo
' Erase everything on the form and cancel the update
Cancel = True
Me.Undo
Case vbCancel
' let the user keep working with this data but cancel update
Cancel = True
End Select
End Sub

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thank you, John, Turner & Jeff. That was a perfect solution and even easier to implement than your examples might have suggested. Most of the required software was already in place. All it needed was a 'Me.Undo' dropped into the code at the appropriate point - a real 'one-liner'. I have tested the result and it works even better than I had hoped. Now I can get on. In the mean time my additional thanks to John for clearing up the acSaveYes and acSaveNo puzzle.