How to make an Inventory Engine in Microsoft Access?

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.

  1. Make a public query
  2. Create a general update inventory code
  3. 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=" &amp; intItemId &amp; " AND warehouseId=" &amp; intWarehouseId &amp; " AND batchNumber='" &amp; strBatchNumber &amp; "'"
If Nz(DCount("itemID", "mstitembegqty", strCriteria), 0) = 0 Then
DoCmd.RunSQL "INSERT INTO mstitembegqty(begQty,inQty,outQty,balQty,itemID,warehouseId,batchNumber,unitCost) " &amp; _
"SELECT 0,0,0,0," &amp; intItemId &amp; "," &amp; intWarehouseId &amp; ",'" &amp; strBatchNumber &amp; "'," &amp; dblUnitCost
End If

'<del>Recount</del>
strCriteria = "id=" &amp; intItemId &amp; " AND warehouseId=" &amp; intWarehouseId &amp; " AND batchNumber='" &amp; strBatchNumber &amp; "'"
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [pubInventoryQ020 (Warehouse Inventory)] WHERE " &amp; strCriteria)
If rs.RecordCount &gt; 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=" &amp; intItemId &amp; " AND warehouseId=" &amp; intWarehouseId &amp; " AND batchNumber='" &amp; strBatchNumber &amp; "'"
DoCmd.RunSQL "UPDATE mstitembegqty " &amp; _
"SET begQty =" &amp; dblBegQuantity &amp; "," &amp; _
"inQty  =" &amp; dblInQuantity &amp; "," &amp; _
"outQty =" &amp; dblOutQuantity &amp; "," &amp; _
"balQty =" &amp; dblBalQuantity &amp; " " &amp; _
"WHERE " &amp; 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=" &amp; lngRRId &amp; " AND itemId IS NOT NULL")

If rs.RecordCount &gt; 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.

Advertisements
About

Software developer living in the Philippines.

Posted in Microsoft Access

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: