By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

MS Access 2003 - Refresh a textbox's calculation

P: 21
Hi there,

I have a job details form - on that form I have fields to store the job rate, expenses and number of days this rate/expenses applies e.g. rate 150, expenses 24 for 3 days.

I have 2 unbound textfields:
TotalRate (rate * no of days)
TotalExpenses (expenses * no of days)

This then calculates the invoicetotal:
Expand|Select|Wrap|Line Numbers
  1. Me!InvoiceTotal.Value = [TotalRate] + [TotalExpenses]
There's a possibility that the rate/number of days worked/expenses may change after a project has been commissioned, is there anyway to refresh that invoice total text box automatically without having to write that code on the afterupdate event for every textbox that may effect the total?
Jan 10 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,607
Hi there,

I have a job details form - on that form I have fields to store the job rate, expenses and number of days this rate/expenses applies e.g. rate 150, expenses 24 for 3 days.

I have 2 unbound textfields:
TotalRate (rate * no of days)
TotalExpenses (expenses * no of days)

This then calculates the invoicetotal:
Expand|Select|Wrap|Line Numbers
  1. Me!InvoiceTotal.Value = [TotalRate] + [TotalExpenses]
There's a possibility that the rate/number of days worked/expenses may change after a project has been commissioned, is there anyway to refresh that invoice total text box automatically without having to write that code on the afterupdate event for every textbox that may effect the total?
You did not specify whether or not the [InvoiceTotal] Field is Bound/Unbound. If it is Unbound, you can place this code in the Control Source property of the Text Box:
Expand|Select|Wrap|Line Numbers
  1. =([Rate]*[no of days])+([Expenses]*[no of days])
Now, any time you enter a Value in either the [Rate], [no of days], or [Expenses] Field, and move Focus from either Field, [InvoiceTotal] will dynamically be updated and the only line of code will reside in the ControlSource property of this Field. Data is literally being "Pulled" from these Fields. Remember, this can occur only if the [InvoiceTotal] Field is Unbound. Hope this helps.
Jan 10 '07 #2

NeoPa
Expert Mod 15k+
P: 31,263
There's a possibility that the rate/number of days worked/expenses may change after a project has been commissioned, is there anyway to refresh that invoice total text box automatically without having to write that code on the afterupdate event for every textbox that may effect the total?
Not if I understand your explanation correctly.
If any of the values (which affect the total result) changes on the form then the total needs to be recalculated.
However, the code doesn't need to be duplicated.
You can simply have a private procedure which does the work and the AfterUpdate events of the various controls simply call this.
This is not like Excel where you can have event procedures for a workbook which capture the same events as those for an individual worksheet.
Jan 10 '07 #3

P: 21
Sorry, I should have said the InvoiceTotal textbox is bound.

So what you're saying is that I should create a prcoedure (maybe a runCode macro?) to perform the calculation and for each textbox (rate/expenses/no of days), I should call the afterUpdate(calculate total or whatever) procedure?

Well that was what I was hoping to avoid, but looks like I do have to call something to perform that calculation each time. Fair enough.

Thanks for that.

On the same subject - today I updated the expenses and it displayed on my invoice subform perfectly - but when I selected a command button to go the print preview for the sales invoice - it was still showing the old expenses total. I had to go to Records ---> Refresh from the menu to refresh before displaying the invoice again - is there a way to write some sort of 'pls refresh all data on this form before displaying the report' command?

I have tried inserting:

Expand|Select|Wrap|Line Numbers
  1. Me!InvoiceTotal.Value = [TotalRate] + [TotalExpenses]
with the OnClick event of the button that will display the report, but it still shows the total BEFORE I updated it.
Jan 10 '07 #4

NeoPa
Expert Mod 15k+
P: 31,263
This is because you are storing a calculated field.
The problem you're getting is exactly the reason that you shouldn't do that in an RDBMS like Access. If you use the calculation everywhere you won't have the problem.
If you insist on doing it this way (saving the calculated result) then you will have to take on updating the field every time one of the component fields changes.
Jan 10 '07 #5

P: 21
The reason I had to store the invoice total as a calculated field was because I had a query, which identifies the income for any given month. I can't total that income if the invoicetotal is unbound. It can display the calculation on the form, but it wont show that value in datasheet view.

So the only way this will work I guess is if I create a report? Or a form? But even so, how can I add up the total for all jobs in a month if totalrate, totalexpenses and invoicetotal are all unbound?
Jan 13 '07 #6

NeoPa
Expert Mod 15k+
P: 31,263
I don't follow you.
Why can't datasheet view show this calculated value in your query?
Jan 13 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.