Form/Subform Calculation Query

[Graham T]

I have 2 tables Product & Stock Amount and Sales. These are joined by a 1-M relationship by ProductID.

I have created a Main form (Product&StockAmount) which contains a subform (Sales Subform).

On the main form I have a field [ProductStockNumber] containing Stock Amount.
The subform contains SalesID, ProductID (foreign key from Products table), SalesAmount and Sale Date.

I have created a macro and attached this to the On_Change event of the SalesAmount control in the subform. On entering a SalesAmount value, I would like the macro to run and set the value of the ProductStockNumber on the main form. The Set value expression in the macro is as follows:

Item: [Forms]![Product&StockAmount]![ProductStockNumber]

Expression:[Forms]![Product&StockAmount]![ProductStockNumber]=[Forms]![Product&StockAmount]![ProductStockNumber]-[Forms]![Product&StockAmount]![Sales Subform]![SaleAmount]

This macro then contains a GoToControl of: Sales Date.

If for example I have a value of 10 in the ProductStockNumber, and in the subform I enter a SaleAmount of 1, it moves the focus to SaleDate but alters the value in ProductStockNumber to -1 (where I would expect the value to alter to 9!). No matter what value I enter it alters the previous value to -1.

Any ideas would be appreiciated. Should I be performing a calculation like this on form/subform?

Graham

 

[Dave Sherer]

If you just move that expression you have written to the After Update Event Of the field that holds the SaleAmount that should work just fine.

 

[Graham T]

Thanks for the reply.

Unfortunately, moving the macro to the different event does not solve the problem.

The only difference that this makes is the value of ProductStockNumber now no longer changes to -1. Instead it updates to 0 !!

I feel the problem must be down to the calculation and not to do with which event this is attached to.