Most business application requires an inventory management. For this type of application the user must be able to determine the remaining inventory of an item in an instant activity like sales. The easiest way to count the inventory is to make a query that crunch through the transactions to sum up the quantities in and out. This is ok if your application has a limited number of items to manage but if you have a thousand items or more plus transaction this wouldn’t be possible. The solution is to create an Inventory Engine.
Steps in making an inventory engine.
- Make a public query
- Create a general update inventory code
- Create a form specific update item inventory
To make a public query, all you need to do is to combine all item in the transaction into one query. Then compute for the balance. Below is an example of an item query in the Receiving Receipt transaction:
Now do this for all transaction then merge all of them into one query to compute for the balance quantity.
The next step is to create a general update inventory code in the item table using the public inventory query. Below is the sample code snippet (updateWarehouseInventory).
Public Sub updateWarehouseInventory(intItemId As Integer, intWarehouseId As Integer, strBatchNumber As String, dblUnitCost As Double) Dim dblBegQuantity As Double Dim dblInQuantity As Double Dim dblOutQuantity As Double Dim dblBalQuantity As Double Dim strCriteria As String Dim db As DAO.Database Dim rs As Recordset DoCmd.SetWarnings False '<del>Prepare the inventory</del> strCriteria = "itemId=" & intItemId & " AND warehouseId=" & intWarehouseId & " AND batchNumber='" & strBatchNumber & "'" If Nz(DCount("itemID", "mstitembegqty", strCriteria), 0) = 0 Then DoCmd.RunSQL "INSERT INTO mstitembegqty(begQty,inQty,outQty,balQty,itemID,warehouseId,batchNumber,unitCost) " & _ "SELECT 0,0,0,0," & intItemId & "," & intWarehouseId & ",'" & strBatchNumber & "'," & dblUnitCost End If '<del>Recount</del> strCriteria = "id=" & intItemId & " AND warehouseId=" & intWarehouseId & " AND batchNumber='" & strBatchNumber & "'" Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM [pubInventoryQ020 (Warehouse Inventory)] WHERE " & strCriteria) If rs.RecordCount > 0 Then rs.MoveFirst dblBegQuantity = Nz(rs!begQuantity, 0) dblInQuantity = Nz(rs!begQuantity, 0) + Nz(rs!rrQuantity, 0) + Nz(rs!transferInQuantity, 0) + Nz(rs!customerReturnQuantity, 0) + Nz(rs!adjustmentQuantity, 0) + Nz(rs!prQuantity, 0) dblOutQuantity = Nz(rs!salesQuantity, 0) + Nz(rs!transferOutQuantity, 0) + Nz(rs!supplierReturnQuantity, 0) + Nz(rs!productionQuantity, 0) dblBalQuantity = Nz(rs!balanceQuantity, 0) 'Update inventory strCriteria = "itemId=" & intItemId & " AND warehouseId=" & intWarehouseId & " AND batchNumber='" & strBatchNumber & "'" DoCmd.RunSQL "UPDATE mstitembegqty " & _ "SET begQty =" & dblBegQuantity & "," & _ "inQty =" & dblInQuantity & "," & _ "outQty =" & dblOutQuantity & "," & _ "balQty =" & dblBalQuantity & " " & _ "WHERE " & strCriteria DoCmd.SetWarnings True updateInventory intItemId End If End Sub
The above code is an UPSERT code, meaning, if the item has no inventory (mstitembegqty) the code will make an INSERT otherwise it will do an UPDATE. The source query for the UPDATE code is the Public Query. By the way, the sample code above is designed for multiple warehouse and batch type inventory for the cost, you can make a simpler code if you want but keep in mind that the objective is to update the item inventory table, in our example its the mstitembegqty, using the values from the public query.
The last step is to create a form specific update item inventory code by looping through the items of the transaction. Below is the sample code snippet the loop through the Receiving Receipt transaction (updateInventoryRR).
Public Sub updateInventoryRR(lngRRId As Long, intWarehouseId As Integer, strBatchNumber As String) Dim db As DAO.Database Dim rs As Recordset Set db = CurrentDb Set rs = db.OpenRecordset("SELECT itemId,BaseUnitCost,quantity FROM trnrritem WHERE rrid=" & lngRRId & " AND itemId IS NOT NULL") If rs.RecordCount > 0 Then rs.MoveFirst lngRecord = 1 Do Until rs.EOF updateWarehouseInventory rs!itemID, intWarehouseId, strBatchNumber, rs!BaseUnitCost rs.MoveNext Loop End If db.Close Set db = Nothing End Sub
Call the above code every time the user press the save button in your transaction form.