Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2007-03-19
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.


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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

688 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