My client need average price in AvgPurPrice column which should update automatically in Product_Master table whenever there are transactions of new purchasing or sales returns. I tried with different ways but wont work. Here is the detail what we need.
Let us assume that ProductCode 1 is available at present in Product_Master table along with its other detail as below.
All values in Product_Master table will be steady for a product (item) except AvgPurPrice because AvgPurPrice price will be changed at every transaction. But at the beginning (at first entry in product master) AvgPurPrice value will be same as purchase value. (I mean 20). I found variance in Average price at the 5th transaction which is in fact 2nd entry of the purchasing. When I purchased 11 items at the rate of 33
The average price should be calculate on EXISTING STOCK QTY that shows at the time of transactions.
(Please refer to my Excel table I attached herewith)
New purchasing price could be different from the previous one for the same item hence the average purchase price should update at every transaction. This will help to calculate the value of the current stock.
Please note that I Sales retrun qty and its amount field I keep in in the same table called T_SalesInvFoot to avoid dealing with multi tables. The same way, Purhase return qty and its amount filed is placed in same table called T_PurInvFoot.
There will be no return qty in maintenance table called T_MInv_Foot.
To do this all what I put the VBA code on after update event of PurQty field in subform (based on T_PurInvFoot) is as follows.
All Blue color code lines are fine. Just Red ones are not properly giving desired result.
Dim OBPurTrans As Long
Dim OBPrice As Double
Dim CurrStock As Long
Dim OBValue As Double
Dim PurQtyValue As Double
Dim SalesRetValue As Double
Dim SalesValue As Double
Dim PurRetValue As Double
Dim MaintQtyValue As Double
Dim TotValue As Double
Dim AvgPrice As Double
'Count Total Pur transactions till date + 1 transaction of Opening balance
Set rst = CurrentDb.OpenRecordset("select * from Product_master where Productcode=1")
If rst.RecordCount = 1 Then
rst!AvgPurPrice = AvgPrice
MsgBox "New Avg. Pur. Price will be " & AvgPrice & "", vbInformation, "AlRasasia Inf"
Set rst = Nothing
There is no error in the code. I receiving the results. But once the Sales retrun transaction appears, the average price appears either more or less ( with big variance). Hopefully I explained clearly from my site.
I am fedup of this calculation. I would appreciate it very much if someone could correct my code logically.