Subform Calculation

[JaredNJames]

hi, i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.

[instock] = [boughtin] - [sold]

because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.

basically what i need is this:
if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown.
if only [boughtit] is present then the stock value simply shows the [boughtin] value.

[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).

i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.

a simple idea of what i want is:
If [Sold] has no value Then
[InStock] = [BoughtIn]
Else
[InStock]=[BoughtIn]-[Sold]
End If

Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.

Thanks
Jared James

 

[jzwp22]

First, you should not be storing the instock value since it is a calculated value. Calculated values are generally not stored, just calculated when you need them.

Not knowing your table structure makes it a little tough to help, but I'll give it a shot.

You'll need a query to get the total of each item in your boughtin table

query name: qrySumOfBoughtIn

SELECT tblBoughtIn.fkItemID, Sum(tblBoughtIn.BoughtInQTY) AS SumOfBoughtInQTY
FROM tblBoughtIn
GROUP BY tblBoughtIn.fkItemID;

You'll also need the sum of all items sold from you invoice detail table

query name: qrySumOfSold

SELECT tblInvoiceDetails.fkItemID, Sum(tblInvoiceDetails.SoldQty) AS SumOfSoldQty
FROM tblInvoiceDetails
GROUP BY tblInvoiceDetails.fkItemID;

Now nest those queries into this one to get your InStock value using the IIF() and IsNull() functions

SELECT tblItems.pkItemID, tblItems.txtItemDesc, qrySumOfBoughtIn.SumOfBoughtInQTY, qrySumOfSold.SumOfSoldQty, IIf(IsNull([SumOFSoldQty]),[SumOfBoughtInQTY],[SumOfBoughtInQTY]-[SumOfSoldQty]) AS InStock
FROM (tblItems LEFT JOIN qrySumOfBoughtIn ON tblItems.pkItemID = qrySumOfBoughtIn.fkItemID) LEFT JOIN qrySumOfSold ON tblItems.pkItemID = qrySumOfSold.fkItemID;


You can use this query as the record source for a subform if you want to display the boughtin, sold and instock values for the various items.

Hopefully this answers your question and if not at least points you towards a solution.