Microsoft Access: Applications and Utilities

Provided by Allen Browne. Updated April 2010.

Inventory Control: Quantity on Hand

"How do I update the Quantity On Hand each time an item is sold/used?" The simple answer is: You don't! You calculate the value when you need it.

The calculation is very simple: the total number acquired, less the number disposed of. Just use DSum() on the table of acquisitions to get the number acquired for any product. Another DSum() expression on the table of uses/invoices gets the number disposed of. For the simplest databases, that's all you need.

For heavily used databases, there are two practical problems:

1. Over the years, recalculating all values from the beginning becomes slow.
2. Outside the realm of pure maths, items go missing.

To solve the second problem, most companies do periodic stocktakes. The stocktake gives you a known starting point, so it solves the first problem too: you can limit your calculation to transactions since then. Storing the stocktake quantities is not denormalizing: any difference between the stored stocktake value and a pure calculated value is not a lack of data integrity. It's valuable information that may be worth reporting on.

The quantity on hand is therefore:

• quantity as of the last stocktake,
• plus acquisitions since then,
• less disposals since then.

Even if you don't actually count stock, you can still take this approach to provide known values at a certain date, avoiding the need to recalculate everything from the beginning.

Sample Function

A typical database has an Invoice table and an Invoice Detail table, so one invoice can have many lines. (If this is unfamiliar, see the Order and Order Detail tables in the Northwind sample database.) In the same way, a single incoming delivery may include many products, so you need Acquisition and Acquisition Detail tables. With the Product table and Stocktake table, you have a structure like this:

This function returns the quantity on hand for any product, optionally at any date.

Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
    'Purpose:   Return the quantity-on-hand for a product.
    'Arguments: vProductID = the product to report on.
    '           vAsOfDate  = the date at which quantity is to be calculated.
    '                           If missing, all transactions are included.
    'Return:    Quantity on hand. Zero on error.
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim lngProduct As Long          'vProductID as a long.
    Dim strAsOf As String           'vAsOfDate as a string.
    Dim strSTDateLast As String     'Last Stock Take Date as a string.
    Dim strDateClause As String     'Date clause to use in SQL statement.
    Dim strSQL As String            'SQL statement.
    Dim lngQtyLast As Long          'Quantity at last stocktake.
    Dim lngQtyAcq As Long           'Quantity acquired since stocktake.
    Dim lngQtyUsed As Long          'Quantity used since stocktake.

    If Not IsNull(vProductID) Then
        'Initialize: Validate and convert parameters.
        Set db = CurrentDb()
        lngProduct = vProductID
        If IsDate(vAsOfDate) Then
            strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
        End If

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
        End If
        strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake " & _
            "WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY StockTakeDate DESC;"

        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
                lngQtyLast = Nz(!Quantity, 0)
            End If
        End With
        rs.Close

        'Build the Date clause
        If Len(strSTDateLast) > 0 Then
            If Len(strAsOf) > 0 Then
                strDateClause = " Between " & strSTDateLast & " And " & strAsOf
            Else
                strDateClause = " >= " & strSTDateLast
            End If
        Else
            If Len(strAsOf) > 0 Then
                strDateClause = " <= " & strAsOf
            Else
                strDateClause = vbNullString
            End If
        End If

        'Get the quantity acquired since then.
        strSQL = "SELECT Sum(tblAcqDetail.Quantity) AS QuantityAcq " & _
            "FROM tblAcq INNER JOIN tblAcqDetail ON tblAcq.AcqID = tblAcqDetail.AcqID " & _
            "WHERE ((tblAcqDetail.ProductID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause & "));"
        End If

        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyAcq = Nz(rs!QuantityAcq, 0)
        End If
        rs.Close

        'Get the quantity used since then.
        strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " & _
            "FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
            "tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
            "WHERE ((tblInvoiceDetail.ProductID = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (tblInvoice.InvoiceDate " & strDateClause & "));"
        End If

        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyUsed = Nz(rs!QuantityUsed, 0)
        End If
        rs.Close

        'Assign the return value
        OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
    End If

    Set rs = Nothing
    Set db = Nothing
    Exit Function
End Function

Storing the Quantity On Hand instead

One of the basic rules of data normalisation is not to store dependent values. Break the rule at your own peril: it takes more work than doing the job properly; it diminishes the queryability of your data; it leaves you without any certainty that your answer is correct.

If you still want to store a QuantityOnHand value, you must update it on every insert, edit, and delete, without exception. If you want to go that route, here's an overview of what's involved.

1. Handling Inserts

   In the AfterInsert event of your form, create a dynamic update query statement to subtract the Quantity used/sold on this row from the QuantityOnHand in the Product table.

2. Handling Edits

   If the user changed either the quantity or the product, you have work to do. In the form's BeforeUpdate event, examine the OldValue of these controls. If either changed, set a module-level flag and capture the old values into a pair of module level variables. Then in the form's AfterUpdate event, check the flag and execute an UPDATE query to increment/decrement the quantity by the difference between the Value and the OldValue. Reset the flag.

3. Handling Deletes

   In the continuous subform, multiple records can be selected for deletion. The Delete event fires for each record, but you do not know whether the deletion will be cancelled. The BeforeDelConfirm and AfterDelConfirm events fire once for the batch, but the record(s) being deleted are not available at this stage. You need both events and a temporary table.

   In the Delete event of the form, append a record to a temporary table to store the product and quantity being deleted. In the AfterDelConfirm event, if the Status is acDeleteOk, execute an update query that joins the temporary table to the Product table and increases the QuantityOnHand by the Quantity being deleted. Then delete all records from the temporary table, whether the delete proceeded or not.

4. Additional Problems

   For this to work, you must ensure that the user does not turn off the confirmation options (Tools | Options | Edit/Find | Confirm in Access 95 - 2003; Microsoft Office button | Access Options | Advanced | Confirm in Access 2007; File | Access Options | Advanced | Confirm in Access 2010.) To complicate matters further, there is a bug in Access 2, 95, and 97 (all patches) such that it may point to the wrong record in the AfterInsert and AfterUpdate events, so you will have to code around that bug as well.