DSUM Problem

[scottappleford]

Hi

If you could help that would be great.


I have a main form which has two sub forms:

Main form has the following calculated fields:

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

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


1st Subform - Products Subform has the following hidden calculated fields.

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

Units In stock=Sum(nz([UnitsReceived])-nz([UnitsShrinkage]))


2nd Subform - Stock control subform has the following hidden calculated fields:

net stock=nz(DSum("QuauntityOut","IntStockControl","Transtype='issued'"))-(nz(DSum("QuauntityOut","IntStockControl","Transtype='returned'")))

The units in stock on the main form is coming up with a wild answer - maybe i am not undertsanding the Dsum correctly.

Hopefully someone can help.

thanks

 

[scottappleford]

I replaced the DSUM with the following in the 2nd stock control sub form

net stock=Sum(IIf([transtype]="issued",[QuauntityOut],0))-(Sum(IIf([transtype]="returned",[QuauntityOut],0)))

which does work?? how would i sync the DSUM with the record?

All getting a bit advanced now. Although i have ordered a VBA beginners guide and have my Access bible.

thanks for your help.