Query doesn't recalculate

[CW]

I have several controls on a form where our charges are calculated for
various parts of an order( quantity x unit price, basically). Following
widespread advice not to store the calculated amounts in a table, I have used
a query to collect the results of the various calculations.
I have a Grand Total control which is bound to that query to display the
final total amount.
Problem: if we alter any of the quantities in the calculations, the total
figure (derived from the query) is not refreshed. We have to quit that record
and come back in again, in order to see the amended total. This is really
confusing and has led to several pricing errors so it is vital that I find a
way around this, so that the Grand Total control is automatically
recalculated as soon as any alterations are made.
Many thanks
CW

 

[Jeff Boyce]

One approach to solving this would be to add into every control whose value
contributes to that Grand Total a procedure (or a call to a procedure) that
recalculates the Grand Total.

If I recall correctly, a much simpler approach, since the Grand Total is a
calculated value, would be to call a 'refresh' (Me.Refresh) from any/every
control that contributes ... (test this, though, as my memory isn't what it
once was...<g>)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Paul Shapiro]

You didn't specify, but I'm guessing you have a main form for the Order and
a subform for the OrderItems? If so, instead of including the order total in
the main form query, let the forms calculate the total. The main form query
can't know that you updated the subform. The basic procedure is to put an
unbound textbox in the subform's footer. Set it's control source to
=Sum([quantity] * [unitPrice]). Put an unbound textbox wherever you want it
on the main form and set it's control source to
=subformControlName.Form.totalTextBoxName. Now the subform can automatically
recognize when it needs to recompute the total, and the main form will
recognize the total has changed. I'm not looking at my code for this kind of
thing at the moment, but I believe it all updates automatically.

You can find more details and examples either in Access help or searching
the web.