Need help to Simplify a Complex Calculation on a Form

[juggernauts]

Hi,



As a newbie on Access 2007, I would like to get your expert advice on simplifying calculation on form. I have a form to do search on my item [ItemCode] and display result with its sales records and stock quantity. For my stock calculation, I used following formula:
Stock quantity at Store = Item Purchase to Store + Item transfer to Store (from Warehouse) – Item Sold from Store – Item transfer from Store (to Warehouse)


The code I use on my form is
=Nz(DSum("[Qty]","tblPurchaseOrderDetails","[SentTo] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
+Nz(DSum("[Qty]","tblStockTransaction","[InvAction] = 'Xfer2Store' AND [Location] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
-Nz(DSum("[Qty]","tblStockTransaction","[InvAction] = 'Xfer2Warehouse' AND [Location] = 'Warehouse' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
-Nz(DSum("[Qty]","tblOrderDetails","[SentFrom] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)


Where:
[frmStockSearch] : the form where I did my search.
tblPurchaseOrderDetails : table for Purchase records which has [SentTo] option for sending item to Store or Warehouse.
tblStockTransaction : table for item movement form / to Store / Warehouse which has [InvAction] as inventory action (transfer to Store or transfer to Warehouse) and [Location] as Destination of Item (Store or Warehouse).
tblOrderDetails : table for sales records which has [SentFrom] option for sending item out from Store or Warehouse.
[ItemCode] : Item Code where I enter on my search form.
[ID] : the ID of the item.


This calculation works fine, however you can see the repeating of “ & [Forms]![frmStockSearch]![ID]),0 ” on every parameter. If I have extra warehouse, then this code will be come even longer. Is there any way to simplify this calculation?

Thank you for your help. I greatly appreciate that.

Also posted at http://www.pcreview.co.uk/forums/nee...-t4063582.html

[John_G]

Hi -

If this code is in the module for the form [frmStockSearch] itself, then you can replace [Forms]![frmStockSearch]![ID] with Me![ID]. Beyond that however, I dont see anything else you can do. Sometimes complex code is just something we just have to live with.

HTH

John