473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 developers and data experts.

How to Stop Bound Forms from Updating Automatically

NeoPa
32,556 Expert Mod 16PB
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.) :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private blnGood As Boolean
  5.  
  6. Private Sub cmdSave_Click()
  7.     blnGood = True
  8.     Call DoCmd.RunCommand(acCmdSaveRecord)
  9.     blnGood = False
  10. End Sub
  11.  
  12. Private Sub Form_BeforeUpdate(Cancel As Integer)
  13.     Dim strMsg As String
  14.  
  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.


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.
Jul 9 '10 #1
10 46242
Jerry Maiapu
259 100+
This is very useful thanks Neo
Aug 30 '10 #2
munkee
374 256MB
Just posting so I can come back to this
Sep 1 '10 #3
TheSmileyCoder
2,322 Expert Mod 2GB
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
259 100+
Wow, our fault. Thanks for that TheSmileyOne.

We are regrettably sorry.
On behave of munkee & Myself
Sep 6 '10 #5
Lysander
344 Expert 100+
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
NeoPa
32,556 Expert Mod 16PB
That would have been tricky Lysander. I only posted it in July :-D

Glad it helped though.
Nov 14 '10 #7
mbndvm
1
Such a simple solution, but it has saved me so many problems. Thank you for sharing!
Jan 14 '15 #8
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
zmbd
5,501 Expert Mod 4TB
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.
Aug 10 '16 #10
NeoPa
32,556 Expert Mod 16PB
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

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Paul Moore | last post by:
Is there a convenient way to ignore the Insert, Delete, and keyboard navigation keys when using bound forms?
5
by: Steve Strik | last post by:
My Problem: I have created a database here at work that is exhibiting some very strange behaviour. Essentially the database is structured in a manner where one table is a master record table...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
0
by: AnAnimal | last post by:
Hi. In the ComboBox drop down list we have: AB - Alberta BC - British Columbia .. .. ..
0
by: Rob | last post by:
Using VB.net and SQL server... In general, is it a good practice to use Bound forms ?
6
by: Kevin | last post by:
I've got an mdiParent form. I open an instance of a child form like this: Dim frmChild as New frmCustomers frmChild.Show() I've got a few of these open at a time. On each frmChild I open...
3
by: Kathryn Gill | last post by:
I;m having probs with this script. when i click update form the values from the text boxs that javascript updates won't be saved in the data base. Can any1 help? Thanks Kat <script...
0
by: sammartin | last post by:
Hi, I've got a question which I'm hoping has a simple answer. I have a series of control bound to an underlying DataTable. The databinding are setup to update the data source on validation....
0
by: aRuaL | last post by:
Hi all, i have a problem with updating the junction table. I am doing this access project after learning for 2 weeks so pardon me if what the question im asking is really simple. Firstly i have 3...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.