OnUpdate calculation in continuous form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have three numberical fields in a continous form and whenever one is
updated, the other two must be re-calculated to reflect the change.
The three fields are txtCostPrice, txtSalePrice and txtMargin.
So far I've got:
OnUpdate event for txtSalePrice
Me!txtMargin = Round((1 - (Me!CostPrice / Me!SalePrice)), 4)
to update the margin figure if the sale price changes.

However, this changes the margin value for all the records in the form, not
just the current one. How do I change the focus to just the updated record?

Thanks.
 
Freeflyer said:
Hi,

I have three numberical fields in a continous form and whenever one is
updated, the other two must be re-calculated to reflect the change.
The three fields are txtCostPrice, txtSalePrice and txtMargin.
So far I've got:
OnUpdate event for txtSalePrice
Me!txtMargin = Round((1 - (Me!CostPrice / Me!SalePrice)), 4)
to update the margin figure if the sale price changes.

However, this changes the margin value for all the records in the
form, not just the current one. How do I change the focus to just the
updated record?

Thanks.
Click to expand...

Just enter...

= Round((1 - (Me!CostPrice / Me!SalePrice)), 4)

....as the ControlSource of the TextBox. No recalculation should then be
necessary. Of course this will not save the value to your table but you should
not be doing that anyway.
 
Hi Rick,

Thanks, but that's not quite what I'm looking for.
I also need to be able to enter a value into the txtMargin field and create
an OnUpdate event that will alter the txtSalePrice field. If I give the field
a calculation as a control source, I will no longer be able to enter a value
too.
 
Freeflyer said:
Hi Rick,

Thanks, but that's not quite what I'm looking for.
I also need to be able to enter a value into the txtMargin field and
create an OnUpdate event that will alter the txtSalePrice field. If I
give the field a calculation as a control source, I will no longer be
able to enter a value too.
Click to expand...

If what you were doing before was changing the value in every record then the
TextBox was not bound to a field in your table. It needs to be for what you
want.
 
Thanks Rick, that's solved it.
It does mean that I now have a field in my table that is essentially
calculated data which I thought I should avoid. Is this normal?
 
Freeflyer said:
Thanks Rick, that's solved it.
It does mean that I now have a field in my table that is essentially
calculated data which I thought I should avoid. Is this normal?
Click to expand...

What you wanted to do and what you should do were contradictory. You are
correct that you should not store the result in your table.
 
Hi Rick,

If you don't mind indulging me, for the sake of good practice, would you be
able to suggest how this should be done then?
Our sales team require three text boxes per record, on a continuous form,
that show them the cost price, sale price and margin for that item. The cost
and sale prices are populated from the item record and stored in the quote
table that the form uses as a record source. The margin is calculated as
Margin = 1-(Cost/Sale).
The sales team then need to be able to alter any one of the three values (as
they may be unique for that particular occurance) and have the other fields
update to reflect the change.
I've currently implemented this by adding the margin as a field on the
underlying table and defining OnUpdate events for each of the textboxes to
change the others.

As you've said, the margin value really should not be stored on the table,
but the effect I require cannot be achieved without doing so - unless I have
missed something.

So, do these requirements force me into the 'bad practice' I have
implemented or is there a better way?

Regards,

Michael
 
Back
Top