Access VBA Code to stop data saving to tables

Hi

I have an Access form that was generated using a wizard.
I want to put an "OK" and "Cancel" button on the form so that data
is not commit to the underlying table unless the OK button is used.

So far I have the following code that runs on the click of the "Cancel" button:
  If Me.NewRecord Then
        If Me.Dirty Then
            Me.Undo
        End If
    End If
    DoCmd.Close

Open in new window

Where else should I put this or similar code to stop data being saved unless the user clicks the "OK" button?
One major concern is that the user clicks an arrow button to move to the next record. How do I stop that?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
PatHartmanCommented:
Controlling the save by using a button imposes your own will upon Access.  This is not the way Access works.  Access ALWAYS wants to save your data for you.  I understand the desire to control the save but it will take code in several places in order for you to succeed.

Define a form level variable in the header of the form's class module. Put it immediately after Option Explicit and before any procedure header.   This is what you are going to use to control the save.
Option Compare Database
Option Explicit

Dim bAllowSave As Boolean

Open in new window

In the form's Current event, set the value to False

bAllowSave = False

Open in new window


In the click event of the button, set the value to True

bAllowSave = True

Open in new window


In the BeforeUpdate event of the FORM, check the value of the variable.

If bAllowSave = False Then
    If msgbox("You did not press the save button.  Press Yes to Save your changes.  Press No to discard them",,vbYesNo) = vbNo Then
        Me.Undo
        Cancel = True
        Exit Sub
    End If
End If

bAllowSave = False

...  ALL YOUR OTHER VALIDATION CODE GOES HERE

Open in new window


Make sure you put your validation code in the form's BeforeUpdate event.  It does NOT belong in the Save button.

The code for the Cancel button would be:
bAllowSave = False
Me.Undo

Open in new window

Although it would be nice to give the user an option to not loose his data.  Rather than simply blowing it away, I would ask if he wants to continue.
0
 
Gustav BrockCIOCommented:
Use the BeforeSave event of the record.
Set parameter Cancel to True if you don't want the record to be saved.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
He meant BeforeUpdate.

Jim.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, if you use Subforms note that Access will always save the Parent data when you move into that subform. If you introduce the suggestions above, the user would be asked to save the data anytime they move into the subform (assuming they've "dirtied" the parent data, of course), so you could run into a situation where you must Delete the parent data if they decide to NOT save the Subform data. AFAIK, there's no way to avoid that other than using temporary tables to base your forms on.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
 
PatHartmanCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.