Stock Control

[GGrieve]

I am trying to complete a calculation to keep track of stock in a yard, If Stock is purchased it gets added to quantity in stock if ordered it gets removed from quantity in stock
Briefly In After Update of [Purchase_quantity] field I tried the following code. IIf ([Supplier_Name]="Yard" and [Product_Name]=[Stock_Name], [Stock_Quantity] =[Stock_Quantity]+[Purchase_Quantity] Where [Supplier_name] = [Yard_Name] But no suuccess so obviously in wrong place or incorrect code. Can anyone help Please!!!!

 

[Rabbie]

Check the format for IIF in Access help. It is IIF(condition,Value if True,Value if False). It evaluates both the True and False values in every case.

In your case it might be better to put some VBA code in the event procedure for the After_Update event like this

It is untested air code but it should give you the idea.

If ([Supplier_Name]="Yard" and [Product_Name]=[Stock_Name] then
   [Stock_Quantity] =[Stock_Quantity]+[Purchase_Quantity] 
End If

 

[GGrieve]

Thanks a million for both your replies, I have decided to go with Allen Browne scenario regarding DSum() The problem I have is that the Dsum works but does not do an individual count for each product e.g.
Total Quantity: DSum("[On_Hire_Quantity]","tblOnHire","[Supplier_Name]='Dungiven' or 'Dublin'"), this is to work out Invoice. Any Assistance greatly apprecipated