How to Stop Bound Forms from Updating Automatically

[NeoPa]

Introduction.

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.) :

Option Compare Database
Option Explicit
 
Private blnGood As Boolean
 
Private Sub cmdSave_Click()
    blnGood = True
    Call DoCmd.RunCommand(acCmdSaveRecord)
    blnGood = False
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
 
    If Not blnGood Then
        Cancel = True
        strMsg = "Please use the Save button to save your changes," & _
                 vbNewLine & "or Escape to reset them."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
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.


Conclusion.

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.

[Jerry Maiapu]

This is very useful thanks Neo

[munkee]

Just posting so I can come back to this

[TheSmileyCoder]

At the bottom of a thread there is a "subscribe to this thread" button, which you can use to subscribe without "spamming" the thread.

[Jerry Maiapu]

Wow, our fault. Thanks for that TheSmileyOne.

We are regrettably sorry.
On behave of munkee & Myself

[Lysander]

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.

[NeoPa]

That would have been tricky Lysander. I only posted it in July :-D

Glad it helped though.

[mbndvm]

Such a simple solution, but it has saved me so many problems. Thank you for sharing!

[mdwester]

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?

[zmbd]

mdwester

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;

thus,

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.

[NeoPa]

For any interested parties a recent hijack question has been split off into its own question thread at Stay on Current Record Without Saving.