By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,337 Members | 2,234 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to Stop Bound Forms from Updating Automatically

Expert Mod 15k+
P: 31,494

A very common question we have here on Bytes relates to how bound forms update the record data automatically when the focus is in any way moved away from the current record onto another, or new, record. In many instances this is required behaviour. It can make a form work similarly to reviewing data in a table grid. Sometimes however, the designer feels that the users may make changes to the data stick (save them away) without being properly conscious of what they are doing, so requires a way of channeling such decisions through an explicit Save button. Below we will discuss the approach that can provide this facility.

Form Design.

Before we go much further let's go through the type of form we're dealing with and describe how it may be set up.

First start with a bound form. For illustrative purposes we can create this from a table using the Create Form wizard. Simply select the table you're interested in then click on the New Object button. If the Form option isn't currently selected then use the drop-down facility to select it.

From the wizard, add all the fields into the box on the right, then select the defaults until prompted with the name of the form on the last page. Don't use the default name, as it will be the same as the table, which is very poor policy. When a name has been entered, select to modify design on completion. You will notice (after Finishing) that the Record Source property is already set to the name of the table. This makes it a Bound form. This is a basic form for modifying the table.

Design Changes to Force Use of Save Button.

The first change to make is to add the Save button itself. Easily done using the Toolbox (Available from Form Design toolbar). I suggest adding the button into the form's footer section. From the Command Button wizard select Record Operations and Save Record. Use defaults or change as you will. I never advise leaving the default names, so I've called mine cmdSave. Never mind about the code, as we'll be changing that later anyway.

The code to support this change is (Be sure to copy and paste this over whatever is there currently, allowing for the name you use for the Save Command Button.) :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private blnGood As Boolean
  6. Private Sub cmdSave_Click()
  7.     blnGood = True
  8.     Call DoCmd.RunCommand(acCmdSaveRecord)
  9.     blnGood = False
  10. End Sub
  12. Private Sub Form_BeforeUpdate(Cancel As Integer)
  13.     Dim strMsg As String
  15.     If Not blnGood Then
  16.         Cancel = True
  17.         strMsg = "Please use the Save button to save your changes," & _
  18.                  vbNewLine & "or Escape to reset them."
  19.         Call MsgBox(Prompt:=strMsg, Title:="Before Update")
  20.     End If
  21. End Sub
The first two (Option) lines are (should be) in all modules.

Line #4 sets up a persistent but private (to the form module) boolean variable to indicate whether the save is due to a call from cmdSave or not.

cmdSave_Click ensures blnGood is set True before attempting the save, then reset to False afterwards to ensure no other saves are flagged as good in error.

Form_BeforeUpdate checks blnGood before proceeding. If True, then the save goes ahead normally. Otherwise Cancel is set to True (to stop the save) and a polite and informative message is sent to the operator to explain why, and how to continue.


Although this is a common problem, particularly for newbies, this is very easy to get around. This should never be cause for anyone to try to provide similar functionailty via unbound forms, which is much more difficult and results in an unnecessarily complex project. Unbound forms have their uses, but this is not one of them.
Jul 9 '10 #1
Share this Article
Share on Google+

Jerry Maiapu
P: 259
This is very useful thanks Neo
Aug 30 '10 #2

P: 374
Just posting so I can come back to this
Sep 1 '10 #3

Expert Mod 100+
P: 2,321
At the bottom of a thread there is a "subscribe to this thread" button, which you can use to subscribe without "spamming" the thread.
Sep 6 '10 #4

Jerry Maiapu
P: 259
Wow, our fault. Thanks for that TheSmileyOne.

We are regrettably sorry.
On behave of munkee & Myself
Sep 6 '10 #5

Expert 100+
P: 344
I just composed a long reply about all sorts of problems we had with trying to stop the records being updated automatically when I re-read the article and realised I had missed a cruical point. Thanks Neo, wish I had read this two years ago, it would have saved me and my NGO a lot of agony.
Nov 13 '10 #6

Expert Mod 15k+
P: 31,494
That would have been tricky Lysander. I only posted it in July :-D

Glad it helped though.
Nov 14 '10 #7

P: 1
Such a simple solution, but it has saved me so many problems. Thank you for sharing!
Jan 14 '15 #8

P: 1
I am not clear about where the suggested code is supposed to be saved. Is all this code associated with the new Save button? I am confused because I don't understand how Access would know to invoke the code unless the button is pressed?
Aug 10 '16 #9

Expert Mod 5K+
P: 5,397

Lines 1, 2, and 4 are part of the form's declaration section
(top of the form's class module)

Lines 6 thru 10 would be placed within the command button's on_click event, of course, altered for the control's name.

Lines 12 thru 21 would be placed within the form's Before_Update event.

When the user enters the data in to a record (or alters a current record) the before_update event is, or should be, triggered when the user moves to a new record or the user shifts the focus away from the new/edited record - the code in lines 12 thru 21 would then be executed;


the only option the user has to actually save the new information is to click on the save button; thus triggering the save button's code, which executes before the code in the before_update event of the form, or to use the [Esc] to cancel the record edit.
Aug 10 '16 #10

Expert Mod 15k+
P: 31,494
For any interested parties a recent hijack question has been split off into its own question thread at Stay on Current Record Without Saving.
Aug 11 '17 #11