Programmatically Deduct Item Before Expiration Date

[Moore71]

Hi,
I have a table that keep date of expired items and quantity.
My question is how to be deduct from each item programmatically, such that if 10 items are in 3 batches, how do I deduct from batch 1 and if batch 1 has exhausted, then deduct from batch 2 and so on.
Here's my demo:

Ball Pen (batch1) =10pcs [expiring 12/10/15]
Ball Pen (batch2) =7pcs [expiring 01/5/16]
Ball Pen (batch3) =8pcs [expiring 02/1/16]

Now If I am selling 15 items to a client, how does it programmatically deduct the first 10pcs and 5pcs from the second batch and so forth...

I hope this is clear enough to receive answer or reply.

 

[Minty]

There are a number of ways - Check that the initial amount is no bigger than your first Batch, if it is subtract that amount from the sales order qty to give you whats left.
Reduce Batch one Stock
Rinse and repeat...

 

[Ranman256]

use a form that has the txtSold, and a field to hold remaining qty, txtQty

Sub DeductInventory()
Dim iSold As Integer, iCount As Integer, iQty As Integer

DoCmd.SetWarnings False
iSold = txtSold   '15
txtQty = iSold

iCount = DCount("*", "batch1")   'only has 5
If iCount < iSold Then
   iQty = iSold - iCount      'iQty = 15-5= 10
End If
txtQty = iCount
DoCmd.OpenQuery "quSubtractFromBatch1"  'query to subtract from table using the text box txtQry on form
txtQty = iQty   'now 10

If iQty > 0 Then
  iCount = DCount("*", "batch2")  'only has 5
  If iCount < iQty Then
     iQty = iQty - iCount
  End If
  txtQty = iCount         'put 5 in the box to be subracted by the query
  DoCmd.OpenQuery "quSubtractFromBatch2"  'batch 2  deducts using txtQty

    If iQty > 0 Then
      iCount = DCount("*", "batch3")
      If iCount < iQty Then
         iQty = iQty - iCount
      End If
      txtQty = iQty
      DoCmd.OpenQuery "quSubtractFromBatch3"  'batch 3
      txtQty = iQty
      
    If txtQty > 0 Then
        MsgBox txtQty & " remain unfilled", , "backorder"
    End If
End If

DoCmd.SetWarnings True
End Sub

 

[Moore71]

Although I have not really test it out, but from the look of things, it will work. I never thought I will receive a sound reply as this.

Thank you for the reply

 

[arnelgp]

supposed there is a form and a textbox control for qty sold to customer, let us name it sold_qty. then on the after update event of your form:

public sub after_update()
dim db as dao.database
dim rs as dao.recordset
dim sold_out as double
dim onHand as double
sold_out = nz(me.sold_qty, 0)
set db = currentdb
set rs=db.openrecordset("select onhandQty, ExpiryDate From table1 Where ProdCode = '" & me.txtProdCode & ' Order By ExpiryDate Asc")
with rs
if not (.bof and .eof) then .movefirst
while sold_out > 0
onHand = !onHandQty
if onHand >= sold_out then
.edit
!onHandQty = onHand - sold_out
.update
sold_out = 0
elseif onHand > 0 then
.edit
!OnhandQty= 0
.update
sold_out = sold_out - onHand
end if
.movenext
wend
end with
rs.close
set rs=nothing
end sub