Access VBA Code to stop data saving to tables


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
        End If
    End If

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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Use the BeforeSave event of the record.
Set parameter Cancel to True if you don't want the record to be saved.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
He meant BeforeUpdate.

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
        Cancel = True
        Exit Sub
    End If
End If

bAllowSave = False


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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.