MS Access 2003 - Refresh a textbox's calculation

[antheana]

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:

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?

[ADezii]

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:

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:

=([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.

[NeoPa]

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.

[antheana]

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:

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.

[NeoPa]

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.

[antheana]

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?

[NeoPa]

I don't follow you.
Why can't datasheet view show this calculated value in your query?