Need help on how to pass calculated field from a subform to another subform

[juggernauts]

Hi,



I need your expert advice on how to get calculated result on a subform of Form A to subform of Form B

For example, below is my calculated field in Form [frmStockSearch]
=Nz(DSum("[Qty]","tblPurchaseOrderDetails","[SentTo] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
-Nz(DSum("[Qty]","tblOrderDetails","[SentFrom] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)

I want to use the same calculation result in Form [frmStockSearchClient]. I know I can copy the same code by changing [frmStockSearch] to [frmStockSearchClient], but it is tedious as I have many calculated fields and many forms.

Thank you for your help. I greatly appreciate that.

Juggie

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

[fredz]

I have used a couple different methods.
1. Dim a public variable in a module. Then you can use that value most anywhere.

2a. Create a textbox on the parent form.
2b. It can be hidden if you don't want the users to see it. Otherwise, it should be locked so users don't try to change it.
2c. Whenever the value gets calculated in the subform (on current event, after update event, etc) change the textbox value (ie: Me.Parent.txtboxname = calculatedvalue).
2d. Use the textbox value in the 2nd subform.

Hope that helps,
Fred

[juggernauts]

Hi Fred,

Thank you for your advice. I have managed to get to calculated value from a subform of form A to another subform on form B.
However, the problem is I have to open Form A in order to see the calculated value in subform of Form B. Is there solution?

Moreover, how to put this calculated value into a query. So far I have put the following expression into field :
Nz(DSum("Qty","tblPurchaseOrderDetails","[SentTo] = 'Store' AND [ItemCode] =" & [tblItem].[ID]),0
-Nz(DSum("Qty","tblOrderDetails","[SentFrom] = 'Store' AND [ItemCode] =" & [tblItem].[ID]),0)

This expression works, but it takes extremely long time to finish the query.

Thank you for your great help.

Juggie

[fredz]

It appears you're attempting to get the net value of purchases by a store (SentTo - SentFrom) for an item.
So, possibly create multiple queries...

Query #1 - A total by store & item of items being Sent
Query #2 - A total by store & item of items being Received
Query #3 - A join of these two queries