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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

compsol1993Author Commented:
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?

compsol1993Author Commented:
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.
<<<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
compsol1993Author Commented:
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...?
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
compsol1993Author Commented:
Thank you puppydogbuddy, that worked perfectly.  Just for my understanding, does setting Me.Dirty=False force a write to a data table?

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.

Glad I could help.  Thanks for the points and grade.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.