Hi all Experts!
I want your help on cost of sales, the second sub report attached to the invoice is supposed to calculate the cost of goods sold. At the moment staff are keying in the calculated average unit cost of the products manually which is prone to errors, and so to try and solve this problem I have created the code below, the idea here is that, the code will first sum all the total invoices values and then divided by the sum of all units for that product bought, the result is supposed to be an average cost per unit. But it is giving an error, any idea?
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Cost = (Dsum(“TotalCost”,”tblInventory”,”ProductID = “ &Me.ProductID))/ (Dsum(“QuantityBought”,”tblInventory”,”ProductID = “ &Me.ProductID))
Before select the product Id I have also notice an error, it disappears temporary after product ID selection. I understand there is a function in Ms Access called ISERROR(Value)that can suppress this error but I’m not sure how to use it.
Here what I want to archive is that the moment a product ID is selected, the cost field on the form must be field and once the form was finished and saved then the cost should be inserted in the underlying table. Does this sound possible?