Updating Access 2003 Table With Form Instantly... Is It Possible?

Posted on 2007-03-19
Last Modified: 2013-11-28
Hello, I am using Microsoft Access 2003.  

I have setup a continuous form, with multiple records displayed in a single line list format.  At the bottom of the list is a sum of one of the columns.  Currently, I have that sum generated directly from the underlying data tables.  This is fine, but due to this approach, that sum only updates when the user moves on to the next record.  I would really like to trigger the data record update after that field is updated, even if the user does not move outside of that record.

So, to summarize, I would like to update the underlying table of an access form without moving to a new record.  I am familiar with VBA, would some function call there be the best approach?

I am open to suggestions,
Thanks for the help.
Question by:compsol1993
  • 6
  • 4
LVL 38

Expert Comment

ID: 18748783
You should not store calculations in a table.  You can display the updated total in your form control as follows:
Private Sub YourColumnName_AfterUpdate()
End Sub
LVL 38

Expert Comment

ID: 18749319
Forgot to mention that, notwithstanding my comments above, if a form control is bound to a field in the underlying table, the table will be updated when the form control is updated.

Expert Comment

ID: 18749598
The comments placed by puppydogbuddy is heading in the right direction but the AfterUpdate event for a text box is slightly tricky. I would recommend that you put the code in the OnChange event of the text box. This will trigger any time someone types something into the text box.

The problem with the AfterUpdate event is that it only triggers when the user tabs out of the control.

Author Comment

ID: 18750947
Thanks for the help.  I think I see where you are going.  I am actually not storing calculations in the table.  Take for example the following set of data:

Val  Field1  Field2  Field3  Field4
1      xxx     xxx       xxx    xxx
2      xxx     xxx       xxx    xxx
3      xxx     xxx       xxx    xxx
4      xxx     xxx       xxx    xxx

SumOfVal:  10

What I am saying is that the SumOfVal is automatically summing the values in the table.  What I would like to do is to be able to change Val from 4 to 5, hit the tab key and see SumOfVal update.  I am familar with the events, what I am actually asking is if I can force that 5 to be written to the table on Tab to Field1 (as an example event).  Is there some code to put in the event handler that will force update without passing onto the next row?


Author Comment

ID: 18750960
Just to clarify on the last comment, when I say force update to Table I mean the underlying database table.  The table that I drew above is actually a form.  Hope that helps.
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

LVL 38

Expert Comment

ID: 18751468
<<<Is there some code to put in the event handler that will force update without passing onto the next row?>>>

1. Sounds like you are referring to row totals, as shown below.  Please clarify.
Val  Field1  Field2  Field3  Field4    RowTotal
1      xxx     xxx       xxx    xxx           XXX
2      xxx     xxx       xxx    xxx           XXX
3      xxx     xxx       xxx    xxx           XXX
4      xxx     xxx       xxx    xxx           XXX

2. How do the field columns relate to the Val column? I assume the  amount in the Val column influences  the  amounts shown in the field columns in some way.
3. what formula do you have for SumOfVal

Author Comment

ID: 18756279
Ok, first let me provide a clearer example, I see how that was confusing.

Project    Cost      Date
  A0         $10     3/7/07
  A1         $12     3/9/07
  A6         $24     3/12/07
  B0         $44     3/19/07

Total Cost:  $90

Think of the above example of a form, not a table.  The underlying table contains data for the four project.  What I am saying is, if I move to the Cost field for Row A0 and change it to $12, I want to hit tab and see the Total Cost field at the bottom instantly update.  Currently total cost is just showing a query result for a SELECT Sum(Cost) .... statement based on the underlying table. It does not update until I move to the next row.   What I want to do is for that $10 to $12 change to immediately impact the underlying database table without me advancing to the next line on the form.  I would like to setup an Event Procedure OnChange for example that forces the database table to update, without relying on Access to do it for me.

Does that help at all...?
LVL 38

Accepted Solution

puppydogbuddy earned 315 total points
ID: 18756533
Yes, try using this code.  Adapt the names of the controls used to the actual names you used.  if it does not work, tell me what happens.

Private Sub Cost_Change()
Me.Dirty = False
End Sub

Author Comment

ID: 18763213
Thank you puppydogbuddy, that worked perfectly.  Just for my understanding, does setting Me.Dirty=False force a write to a data table?

LVL 38

Expert Comment

ID: 18763262
yes, it commits any unsaved changes in bound controls  on the form to the underlying table.  The form is said to be dirty if there are any changes pending a save.

LVL 38

Expert Comment

ID: 18764370
Glad I could help.  Thanks for the points and grade.

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now