Possible to move values (1 Viewer)

[mreference]

I have a main form (PRODUCTS) with subforms, on 1 subform (PRICES) I have a continuous form that displays prices of products from different suppliers. On another subform (PROFIT) I have a calculator that works out the profit based on the lowest price from a supplier.

This works well, however, I was hoping to put a command button on the PRICES subform next to each suppliers price so that if clicked it would override the minimum price on the PROFIT subform and recalculate the profit, then once the form is closed it would reset to the lowest price when it opens next time.

This is handy for us as a supplier may have ran out of stock and we have to goto the next lowest priced supplier.

The current minimum price is calculated from a totals query using Min.

Is this possible?

 

[JHB]

Something like below, (remember to change the fieldname/queryname).

SELECT Min([YourFieldName]) AS [MinYourFieldName]
FROM YourQuery
WHERE ([YourFieldName]>YourCurrentMinValue);