VB Code for Access DB for Inventory Tracking with FIFO

[rao.gnaneswara]

Helo every body,



I just joined this forum and I see this is very useful forum for extending the access usage in organisations.

I have recently come across some problem with vb script for FIFO calculation looping on our Inventory Tracking DB. Can any body help us in making this script for FIFO loop?

The Details are as follows.

we have a table in the database called 'TblSparesAdjusted' with following columns

1) AdjustmentID
2) SpareID
3) SpareName
4) UnitPrice
5) QtyAdjusted
6) AdjustType(NewReceipt/Consumption)
7) OldQtyOnhand
8) UpdatedQtyOnhand
9) DateOfAdjust
10) ConsumedQtyfromReceipts


This table gets updated whenever there is new receipt Or Consumption with respect to particular Spare ID(is unique for each spare)

Now we would like to have a module (VB code) to calculate the values for column 'ConsumedQtyfromReceipts' for each receipt column of the specific Spare ID where consumption happend. Values for this column will be from AdjusteQty as Consumption(Type of Adjust-Column6) should be distributed across all relevant receipt rows on FIFO basis. Please look at the table, 1-9 columns are user filled, and column 10 to be calcluated through code by making some looping. Code should omit those rows of receipts where already filled with cosumedqtyofreceipts in full receieved qty.

AdjID	SpareId	SpareName	UnitPrice	QtyAdjusted	AdjType	OldQtyinHand	UpdatedQtyinHand	DateofAdjust	ConsumedQtyfromReceipts
1	111	Item1	$10	2	Receipt	0	2	1 jan 2011	2
2	111	Item1	$12	3	Receipt	2	5	3 Mar 2011	4
3	222	Item2	$5	10	Receipt	0	10	3 Mar 2011	10
4	111	Item1	$15	5	Receipt	5	10	5 May 2011
5	111	Item1		4	Consumption	10	6	7 July 2011
6	111	Item1		2	Consumption	6	2	10 Aug 2011
7	222	Item2	$8	5	Receipt	10	15	10 Aug 2011	2
8	222	Item2		12	Consumption	15	3	2 Sep 2011

Can somebody helpme out for this logic?

[Rod]

Yes, but before I do may I reiterate your requirement to show I have understood correctly.

You have a table that for each spare contains records of receipts and consumption. When a consumption occurs, the system should allocate the quantity consumed across the receipts for the spare, starting with the oldest not-fully-consumed receipt and moving forward in time as necessary.

If that is correct, publish your full table design. I need to know the data type of each attribute.

[recyan]

Am not sure, but the table structure creates some doubt in my mind ?
Off the cuff, would have expected something like below :
1) AdjustmentID
2) SpareID
3) UnitPrice
4) QtyAdjusted
5) AdjustType(NewReceipt/Consumption)
6) DateOfAdjust

Thanks

[Rod]

Hi recyan,

Agreed it's a horrible unnormalised design - probably a transcript from a spreadsheet. Quantities on hand are indeed derived values and should not really be in the table. The concept of ConsumedQuantityFromReceipts is necessary for the allocation (the allocation is doubtless due to the accounting and stock valuation methods) but should not be an attribute in this table but an attribute in a joining/cross reference table between receipts and consumption.

However it's the design the poster has and unless we hear differently it's the design we work with.

PS This type of stock valuation/accounting that involves allocation of consumption should have a mechanism of check points for recovery and auditing purposes.

[rao.gnaneswara]

Hi Rod,

thanks for the reply. Indeed the table shown in post was just an extract of a query from original table(to which I need the FIFO logic to apply). It seems like unnormalised may be because the columns Spare ID and Spare Name also shown in table(apologies for showing that as table) and records repeated. Indeed spares list is a separate table with which is in relation(One to many) with current table 'SparesAdjusted' which allows each spare item will have multiple records/transactions in 'SpareAdjusted' table. But now, how to establish relation among records based on columnsis what in question. (as I am not too good in Access).

Actually for the original table in database is similar to what recyan said. Below shows data types which you indicated for.
1) AdjustmentID (Autonumber/PrimaryKey)
2) SpareID(ForeignKey)
3) New Unit Price(Double)
4) QtyAdjusted(Double)
5) AdjustType(NewReceipt/Consumption)(String/Text))
6) OldQtyOnhand(Double)
7) DateOfAdjust(Date&Time)
8) ConsumedQtyfromReceipts(Double)

[Rod]

I understand now that some demormalisation has taken place because this is the result set of a query. Although we are critical, there is no suggestion that you should change your design. One further observation however: your numeric attributes are Double; this can lead to slight inaccuarcies in arithmetic, not so important except when doing a comparison; a comparison for equality will fail even if the 32nd decimal place differs! I recommend that in future you use Integer and Long when there are no decimal places and Currency when you need decimal places. Currency is a data type that guarantees arithmetic consistency and accuracy.

OK, I'll write a sample procedure for you. It's getting late here now and I'm busy tomorrow morning so look out for my response after say 24 hours.

[Rod]

The following procedure compiles but because I have no data I am unable to test it. Some comments are included in line and a brief commentary follows.

Public Sub AllocateConsumption(rlngAdjustmentID As Long)
    Dim lngSpareID As Long
    Dim lngQtyToAllocate As Long
    Dim lngQtyAvailable As Long
    Dim strSQL As String
    Dim rstSparesAdjusted As DAO.Recordset
    
    On Error GoTo AllocateConsumption_Error
    
    'Validate the argument by retrieving the consumption record.
    
    strSQL = "SELECT * FROM tblSparesAdjusted " & _
             "WHERE AdjustmentID = " & rlngAdjustmentID
    
    Set rstSparesAdjusted = CurrentDb.OpenRecordset(strSQL)
    If rstSparesAdjusted.RecordCount = 0 Then
        MsgBox "Record for AdjustmentID = " & rlngAdjustmentID & " not found.", vbCritical
        GoTo Exit_Procedure
        Set rstSparesAdjusted = Nothing
    ElseIf rstSparesAdjusted!AdjustmentType <> "Consumption" Then
        MsgBox "Record for AdjustmentID = " & rlngAdjustmentID & " is not a consumption.", vbCritical
        GoTo Exit_Procedure
        Set rstSparesAdjusted = Nothing
    End If
    
    'There should be some way of checking this consumption has not already been allocated!
    
    'Capture required values.
    
    lngSpareID = rstSparesAdjusted!SparesID
    lngQtyToAllocate = CLng(rstSparesAdjusted!QtyAdjusted)
    Set rstSparesAdjusted = Nothing
    
    'Retrieve candidate receipts
    
    strSQL = "SELECT * FROM tblSparesAdjusted " & _
             "WHERE SparesID = " & lngSpareID & " AND (QtyAdjusted - ConsumedQTYfromReceipts) > 0 " & _
             "AND AdjustType = 'Receipt' " & _
             "ORDER BY DateOfAdjust"
    Set rstSparesAdjusted = CurrentDb.OpenRecordset(strSQL)
    
    'Check for empty recordset.
    
    If rstSparesAdjusted.BOF Then
        MsgBox "There are no candidate receipts for SpareID = " & lngSpareID, vbCritical
        GoTo Exit_Procedure
        Set rstSparesAdjusted = Nothing
    End If
    'Check for sufficient unallocated receipts.
    lngQtyAvailable = 0
    With rstSparesAdjusted
        Do Until .EOF
            lngQtyAvailable = lngQtyAvailable + !QtyAdjusted - !ConsumedQtyfromReceipts
            .MoveNext
        Loop
    End With
    
    If lngQtyAvailable < lngQtyToAllocate Then
        MsgBox "There are insufficient open receipts for SpareID = " & lngSpareID & vbCrLf & _
               "Needed = " & lngQtyToAllocate & "; Available = " & lngQtyAvailable, vbCritical
        GoTo Exit_Procedure
        Set rstSparesAdjusted = Nothing
    End If
    
    'Perform allocation.
    
    DBEngine.Workspaces(0).BeginTrans
    On Error GoTo AllocateConsumption_Error_Rollback
    With rstSparesAdjusted
        .MoveFirst
        Do Until lngQtyToAllocate <= 0 
            .Edit
                lngQtyAvailable = !QtyAdjust - !ConsumedQtyfromReceipts
                Select Case lngQtyAvailable
                    Case Is >= lngQtyToAllocate
                        !ConsumedQtyfromReceipts = !ConsumedQtyfromReceipts + lngQtyToAllocate
                    Case Else
                         !ConsumedQtyfromReceipts = !ConsumedQtyfromReceipts + lngQtyAvailable
                End Select
                lngQtyToAllocate = lngQtyToAllocate - lngQtyAvailable
            .Update
            .MoveNext
        Loop
    End With
    DBEngine.Workspaces(0).CommitTrans
    Set rstSparesAdjusted = Nothing
    
Exit_Procedure:
    On Error GoTo 0
    Exit Sub
AllocateConsumption_Error_Rollback:
    DBEngine.Workspaces(0).Rollback
    
AllocateConsumption_Error:
    'Put your eror handler here
    Resume Exit_Procedure
    Resume
    
End Sub

The [CODE] tags on this site seem to ignore blank lines so I have highlighted the comments in blue in an attempt to split up the code.

• The procedure assumes that the consumption record has been added to the table and its ID is known. This ID needs to be passed to the procedure as an argument.
• The argument is validated by checking: that it exists; that it is a consumption. There should be some way of determining if this consumption has already been allocated.
• If everything is OK, a recordset is retrieved of those receipts matching the spare ID that have an unallocated quantity. The recordset is retrieved in date order, oldest first.
• More checks are performed to ensure that there are candidate receipts and that the sum of unallocated receipts is equal or greater than the quantity to allocate.
• The allocation itself is encased in a Begin-Commit Trans pair so the update to the table is 'all or nothing.'
• I'm unsure what happens if you try to rollback a transaction that does not exist which is why I have separated it out from the remainder of the error code.
• You need to use an error handler to report run time errors.
• Use your own names if different.

OK, this may prompt as many questions as it answers.

[orange]

There are some posts related to FIFO Inventory at
https://www.accessforums.net/access/...ory-25249.html

It isn't related to your specific requirement, but it may be of some use for concepts or code.

If you download and open the database, you must hold down the shift key to get access to the Database window.

[rao.gnaneswara]

Hi Rod, thanks for the code. I will test test it today will get back to you.

[rao.gnaneswara]

Hi Rod,

I could not succeed with the code. It is not updating column 'ConsumedQtyfromReceipts' in the table. May be you can have a look at the sample DB attached here new.zip where I tried to run this code.

[rao.gnaneswara]

Hi Orange

I am looking at these posts advised by you. Thanks for the links.

[Rod]

May be you can have a look at the sample DB attached here new.zip where I tried to run this code.

Will do - bit busy right now - expect response within 24 hours.

[Rod]

OK, there were a few things wrong.

You forgot to include the procedure’s argument, rlngAdjustmentID, both in the procedure’s declaration and in the call to the procedure.

The procedure declaration should be:

Public Sub AllocateConsumption(rlngAdjustmentID As Long)

And the call to the procedure:

Mod_AllocateConsumption.AllocateConsumption Me.AdjustmentID

There are some spelling mistakes – all mine I think, sorry! – but it was difficult without having the actual table definition.

My procedure tested something called AdjustmentType which does not exist; the correct name is AdjustType.

ElseIf rstSparesAdjusted!AdjustType <> "Consumption" Then

Similarly SparesID should be SpareID

lngSpareID = rstSparesAdjusted!SpareID

"WHERE SpareID = " & lngSpareID & " AND (QtyAdjusted - Nz(ConsumedQTYfromReceipts,0)) > 0 " & _

Again QtyAdjust should be QtyAdjusted

lngQtyAvailable = !QtyAdjusted - Nz(!ConsumedQtyfromReceipts, 0)

The definition of ConsumedQtyfromReceipts has no default value of zero so nulls are possible. There are 5 lines to be changed to include a Nz(,0)

"WHERE SpareID = " & lngSpareID & " AND (QtyAdjusted - Nz(ConsumedQTYfromReceipts,0)) > 0 " & _

lngQtyAvailable = lngQtyAvailable + !QtyAdjusted - Nz(!ConsumedQtyfromReceipts, 0)

lngQtyAvailable = !QtyAdjust - Nz(!ConsumedQtyfromReceipts, 0)

!ConsumedQtyfromReceipts = Nz(!ConsumedQtyfromReceipts, 0) + lngQtyToAllocate

!ConsumedQtyfromReceipts = Nz(!ConsumedQtyfromReceipts, 0) + lngQtyAvailable

I have made these changes and return the database.

new.zip

Two points:

1. I recommend you always include Option Explicit at the start of every module.
2. There is no check to determine whether a receipt has already been allocated.

[rao.gnaneswara]

There are some posts related to FIFO Inventory at
https://www.accessforums.net/access/...ory-25249.html

It isn't related to your specific requirement, but it may be of some use for concepts or code.

If you download and open the database, you must hold down the shift key to get access to the Database window.

Hi Orange,

The posts specified by you were really helpful on FIFOcalculation. Though, I could see lot difference in datamodel of our DB when compared to what shown in Posts, but I could get some idea about FIFO calculation. I will try to map this idea to my case if it works. Thanks for the posts. I will get back to you whether it worked for me.

[orange]

Thanks for the update. Good luck with your project.