simple calculation from form

[PhilipEwen]

I have a table with costprice, maxsellprice and minsellprice fields.

On the form, i want to be able to enter the costprice. The i would like the maxsellprice to be autofilled with 2.5 x cost price and the minsellprice to be filled with 1.5 x costprice.

I would normally just have the latter 2 as unbound boxes, but i want to be able to
1. edit the figures, so on some products i can add max and min by hand
2.enter this data into the table

How is this done?

Amny thanks

 

[D B Lawson]

Hi Philip

You could try creating a query with your three fields. In the costprice criteria reference to the form that you fill the data into. In the field for the maxsellprice do the calculation maxsellprice: [costprice]*2.5 and in the other field minsellprice: [costprice] * 1.5.

On the afterupdate event of the Costprice field of the form put:

Me.maxsellprice = DLookup("maxsellprice", "QueryName")
Me.minsellprice = DLookup ("minsellprice", "QueryName")

When you enter the cost price the code looks to the query and fills in the calculations in the other two fields and saves the results to the base table. If, however, you want to change the max and min prices you can, and it over-writes what was saved to the table.

HTH

D

 

[lwhite]

Considering that the table behind the form has all three fields, you can use the form to update maxsell and minsell with an afterupdate event.
Example:
Private Sub Costprice_AfterUpdate()
[Maxsellprice] = [Costprice] * 2.5
[Minsellprice] = [Costprice] * 1.5
End Sub
This is automatic and yet allows you to update by hand records of your choice.

 

[PhilipEwen]

Thanks you very much for your input.
I went with the latter as it was simpler than queries.

thank you both though.