calculating the difference between 2 boxes

[DowningDevelopments]

I have a form where im trying to calculate the difference between the moneies
collected and those owed.
The collected amount is worked out by summing up a subform which holds all
the payments made by a person. There is a SUM field in that subform that is
referenced by the mainfrom to display this data to the user.
I Have a requery button which is pressed to update the subform and give the
new total. This is the sub for the update button.

<!---

Forms!Receipts![PaymentListSubform].Requery

If Me![TotalCollected] < Me![TotalCharges] Then
'difference = charges - collect
Me![Difference] = Me![TotalCharges] - Me![TotalCollected]
Else
'difference = collect - charges
Me![Difference] = Me![TotalCollected] - Me![TotalCharges]
End If

--!>

So why is it that im getting the value of Total charges in the difference
box? ive used a watch and its telling me that its not giving any value for
the TotalCollected. I suspect that this has to do with the requery, which
does update this total, but ive tried to put this extract of code into other
subs and im finding it hard to figure out where to put it so that it updates
to show the changing total.

With Much thanks

Amit

 

[DowningDevelopments]

Let me just add that when i step through the above code and have a watch on
all the variables then it behaves as i wanted it to and it gives the correct
output, however when i recreate this without any breaks or checks then it
just gives the output as given above. ???

with thanks for any help or explaination

Amit

 

[Marshall Barton]

Let me just add that when i step through the above code and have a watch on
all the variables then it behaves as i wanted it to and it gives the correct
output, however when i recreate this without any breaks or checks then it
just gives the output as given above. ???

The problem you're having is because the calculation of
control expressions is performed as an asynchronous (and
lower priority) task to your VBA code execution.

To get consistent results, you need to either do it all in
VBA code or all in control expressions.

If possible, it might be better to precalculate the core
values in the form's record source query.

 

[DowningDevelopments]

Thanks Marshall,

I had a feeling it was something to do with the succession of events but
didnt realise how it worked exactly. I also didnt understand what you mean by:

'If possible, it might be better to precalculate the core values in the
form's record source query'

However by following your suggestion and using the expression builder i was
able to get the effect i wanted. But im still curious about precalulating the
values on the forms records, as the value was a sum how would i do this in a
query without producing an additional field for each row and how would i be
able to pick out the exact field that i wanted from this, or am i getting
hold of this from completely the wrong end?

Anyway thank you again,

Amit

 

[Marshall Barton]

I had a feeling it was something to do with the succession of events but
didnt realise how it worked exactly. I also didnt understand what you mean by:

'If possible, it might be better to precalculate the core values in the
form's record source query'

However by following your suggestion and using the expression builder i was
able to get the effect i wanted. But im still curious about precalulating the
values on the forms records, as the value was a sum how would i do this in a
query without producing an additional field for each row and how would i be
able to pick out the exact field that i wanted from this, or am i getting
hold of this from completely the wrong end?

You can do an awful lot of stuff in queries, including
joining a totals query to your form's current table/query.
Sometimes, this can be used to simplify complex calculations
in your form.

In this case, it doesn't sound like it would provide much
benefit, but, not knowing everything about your form, I
can't be sure. Just keep it in mind as another tool that's
available if you run into a tricky situation.