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.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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