Solved

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

Posted on 2007-03-19
11
260 Views
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.
0
Comment
Question by:compsol1993
[X]
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
11 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
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()
txtTotal.Recalc
End Sub
0
 
LVL 38

Expert Comment

by:puppydogbuddy
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.
0
 
LVL 6

Expert Comment

by:twintai
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:compsol1993
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?

Thanks
 
0
 

Author Comment

by:compsol1993
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
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
0
 

Author Comment

by:compsol1993
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...?
0
 
LVL 38

Accepted Solution

by:
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
Me!txtTotalCost.Recalc
End Sub
0
 

Author Comment

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

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18763262
compsol1993:
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.

PDB
0
 
LVL 38

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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