Provided by Allen Browne. Updated April 2010.
"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:
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:
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.
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
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.
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.
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.
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.
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.
Home | Index of tips | Top |