Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: A bit complex - help needed badly

    Hi,

    It is a bit lengthy and hard (for me) but hopefully forum members could help me in this regard. The story is like this:

    I have a below purchase form with header and footer form in it with one-to-many relationship. Let us assume their source is query or table. It works smoothly.

    F_PurInvHead (InvNum, InvDate, SuppCode, SuppName etc.)
    F_PurInvFoot (InvNum, ItemCode, ItemName, Stock, PurQty & PurPrice)

    In F_PurInvFoot table there are lots of entries of material that we purchase with different price. Item in footer table are repeated than once with different more or less price.

    Example. Item A is purchased more than once in diff dates as follows.

    @ $1.2 / pc in InvNum 1245 PurQty : 5
    @ $1.6 / pc in InvNum 1490 PurQty : 6
    @ $1.3 / pc in InvNum 1941 PurQty : 3
    @ $1.2 / pc in InvNum 2056 PurQty : 10

    Now we will take a look at Sales Invoice

    This form also created same as purchase form with one-to-many relationship.

    F_SalesInvHead (InvNum, InvDate, CustCode, CustName etc.)
    F_SalesInvFoot (InvNum, ItemCode, Item Name, Stock, SalesQty, SalesPrice & PurPrice)

    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.

    Dim Currstock As Long

    Currstock = Nz(DSum("Openingbal", "Product_master", "ProductCode = " & ProductCode), 0)
    Currstock = Currstock + Nz(DSum("PurQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)
    Currstock = Currstock + Nz(DSum("SalesRetQty", "T_SalesInvFoot", "Productcode = " & ProductCode), 0)
    Currstock = Currstock - Nz(DSum("SalesQty", "T_SalesInvFoot", "Productcode = " & ProductCode), 0)
    Currstock = Currstock - Nz(DSum("PurRetQty", "T_PurInvFoot", "Productcode = " & ProductCode), 0)
    Currstock = Currstock - Nz(DSum("Qty", "T_MInv_Foot", "Productcode = " & ProductCode), 0)

    Note:
    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.

    Currstock = Nz(DSum("Openingbal", "Product_master", "ProductCode = " & ProductCode), 0)

    I used here DSum because Product_master table holds only one record for each item as opening balance so this performs fast to get this single data. But for other large table I wrote below code

    Dim db1 As DAO.Database
    Dim Rst1 As DAO.Recordset
    Dim X As Integer
    Dim xPurQty, yPurRetQty, xSalesQty, ySalesRetQty As Integer

    Set Rst1 = CurrentDb.OpenRecordset("select * from T_PurInvFoot where PurQty>0 And Productcode=" & ProductCode)

    If Rst1.RecordCount >= 1 Then

    For X = 1 To Rst1.RecordCount
    xPurQty = xPurQty + Rst1!PurQty
    yPurRetQty = yPurRetQty + Rst1!PurRetQty
    Rst1.MoveNext
    Next

    Currstock = ((Currstock + xPurQty) - yPurRetQty)

    Else
    xPurQty = 0
    yPurRetQty = 0
    Currstock = ((Currstock + xPurQty) - yPurRetQty)
    End If

    Rst1.Close
    Set Rst1 = Nothing

    Set Rst1 = CurrentDb.OpenRecordset("select * from T_SalesInvFoot where SalesQty>0 And Productcode=" & ProductCode)

    If Rst1.RecordCount >= 1 Then

    For X = 1 To Rst1.RecordCount
    xSalesQty = xSalesQty + Rst1!SalesQty
    ySalesRetQty = ySalesRetQty + Rst1!SalesRetQty
    Rst1.MoveNext
    Next

    Currstock = (Currstock - xSalesQty) + ySalesRetQty

    Else
    xSalesQty = 0
    ySalesRetQty = 0
    Currstock = (Currstock - xSalesQty) + ySalesRetQty
    End If

    Rst1.Close
    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.Edit

    Rst!Stock = Currstock

    Rst.Update
    End If
    Rst.Close
    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.

    Example.

    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:

    ItemCode ItemName PurPrice Stock InvNum
    21---------A-------- $ 1.2----5-----1245
    21---------A-------- $ 1.6----6-----1490
    21---------A-------- $ 1.3----3-----1941
    21---------A-------- $ 1.2----10----2056

    (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.
    Last edited by Ashfaque; 02-11-08 at 03:32.
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    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.
    With kind regards,
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •