Hope this code will help someone. It looks up the cost price of stock items that have been purchased over several orders and calculates the aggregate cost so that when posted to the accounts table the COGS is correct, even if the goods were bought at different cost prices.
To summarise the code, after entering a product stock id (txtStoGID) it goes through the purchase order details table joined with the shipment table in reverse date recvd order, remembering the cost price paid, until it reaches the total number of items being sold (SoldQty), adds the avg prices for each batch and then finally divides them by the original sold qty (OrigQty). Notice that If you don't have enough stock it uses the last price paid for the stock, and your accounts dept would have to correct this info later if different.
To summarise the code, after entering a product stock id (txtStoGID) it goes through the purchase order details table joined with the shipment table in reverse date recvd order, remembering the cost price paid, until it reaches the total number of items being sold (SoldQty), adds the avg prices for each batch and then finally divides them by the original sold qty (OrigQty). Notice that If you don't have enough stock it uses the last price paid for the stock, and your accounts dept would have to correct this info later if different.
Code:
Private Sub txtStoGID_AfterUpdate()
'get price
Me.curPrice = DLookup("curRRP", "tblStock", "txtgid='" & Me.txtStoGID & "'")
'get cost
SoldQty = Nz(DSum("intQtyReqd", "tblSOD", "txtStoGID='" & Me.txtStoGID & "' AND intUID <> " & Me.intUID & ""), 0) + Nz(Me.intQtyReqd, 0)
OrigQty = SoldQty
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM qryJoinPODShipm WHERE txtStoGID='" & Me.txtStoGID & "' ORDER BY datDateRecvd ASC")
rs1.MoveFirst
Do While Not rs1.EOF
PurcQty = PurcQty + rs1!intQtyReqd
If OrigQty <= PurcQty Then
avgPrice = avgPrice + (SoldQty * rs1!curPrice)
SoldQty = SoldQty - rs1!intQtyReqd
LastPrice = rs1!curPrice
Exit Do
Else
avgPrice = avgPrice + rs1!intQtyReqd * rs1!curPrice
SoldQty = SoldQty - rs1!intQtyReqd
LastPrice = rs1!curPrice
End If
rs1.MoveNext
Loop
If SoldQty > 0 Then avgPrice = avgPrice + (SoldQty * LastPrice)
Me.curCost = avgPrice / OrigQty
End Sub
Comment