Using IF

[scottappleford]

Hi

If you could help that would be great.


I have a main form which has two sub forms:

Main form has:

Units in stock=[Products Subform].[Form]![UnitsOnHand]

Units on order=[Products Subform].[Form]![UnitsOnOrder]



1st Subform - Products Subform has the following hidden controls.

Units on order=Sum(nz([UnitsOrdered])-nz([UnitsReceived]))

Units on hand=Sum(nz([UnitsReceived])-nz([UnitsShrinkage]))


2nd Subform - Stock control subform is the one i am having trouble with:

I have a trasaction type and a quantity control .

transaction type has an 'in' and 'out' from a lookup. Now i would like to have my 'Units in stock' on the main form calculate the correct stock when items go out and items come back.

Have i set it up correctly?

If so should i have a hidden control on the 2nd subform like the 1st to get the calculation and then have the main form control collect and calculate the figure? e.g use an IF to work out if it is an in or out, add up the 'out' and then add the the 'ins' and then minus 'in' from the 'outs' to give a net figure.

The units in stock would then be:

Units in stock=[Products Subform].[Form]![UnitsOnHand]-[stockcontrolsubform].[form]![netstock]

thanks

 

[Oldsoftboss]

Have i set it up correctly?

Does it work ?

 

[scottappleford]

Hi

What i meant was - am i going about it the right way before i spend time working out the IF calcualtion?

The IF calc is what i need help with?

Sorry for the confusion.

Many Thanks