In this sales footer I displayed PurPrice just to have the idea while selling item.
I wrote my codes to get the recordset once user enters item code in text box, item name, Stock, Sales Price, PurPrice etc are automatically pulled on footer form and then cursor moves to PurQty text box.
Stock field in the footer form is to display current available stock for the item entered. I was doing it in following way.
It was agreed to place PurQty and PurRetQty in T_PurInvFoot and SalesQty and SalesRetQty fields in T_SalesInvFoot for easy access. So that addition and reduction of the stock qty can be handled in a very easy way.
I found the aggregate Dsum performs slower with a large table. Hence I changed it to below and works fine.
xSalesQty = 0
ySalesRetQty = 0
Currstock = (Currstock - xSalesQty) + ySalesRetQty
Set Rst1 = Nothing
Stock = Currstock
(Once the PurQty in Purchase for OR SalesQty in Sales form is updated, it updates stock in Product_master tbl.in field named STOCK)
Dim db As DAO.Database
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("select * from Product_master where Productcode=" & ProductCode)
If Rst.RecordCount = 1 Then
Rst!Stock = Currstock
Set Rst = Nothing
This is absolutely working as desired.
Now what I have been asked to do is like this.
Once user enters the quantity for the respective item in sales invoice footer, it should deduct display a small form for ref that displays same product list with different prices and diff stock qty and at the same time it should deduct the stock from the lowest purchase price first and then it should deduct from next lower purchase price and so on.
If item A is entered in sales invoice footer (off course item code will enter to call recordset) a small form should display below data of the entered item:
(The above form query could be represented in Ascending order on PurPrice)
So if the SalesQty is entered as 7, the qty will be deducted (looking in T_PurInvFoot tbl) from the lowest price qty which price $ 1.2 Qty is: 5. Here PurQty 5 will be 0 in T_PurInvFoot tbl and the control will search for next lowest price purchased qty again in T_PurInvFoot tbl which is again $ 1.2 Qty is: 10. This 10 will be now replace by 8 bcz the 2 is still remain to deduct (as the user entered sales qty 7)
I believe that I explained the matter in proper way.
My above code is maintaining stock in my way but the new requirement is heavy task to perform. The display of small form can be avoided and execution of code could done behind the screen also.
I am badly looking for the help. Can some one come ahead to help please.
I think instead of making PurQty 0, it could be better if I place another new field in which we could place 0 so that original PurQty figure will remain as is for future record and further calculations.