ACC2000: How to Automatically Subtract a Quantity Ordered from Your Inventory

The information in this article applies to:

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

This article discusses two methods that you can use to automatically reduce the available inventory (or units in stock) by the quantity ordered. Method 1 shows you how to use an update query, and is for Jet databases only. Method 2 shows you how to use recordset code, and is for both Jet databases and Access projects).

NOTE: To use Method 1 with Access projects, use the following information to get started:

Q235359 Implementing Query-by-Form in an Access Project

 

MORE INFORMATION

The following two methods show you how you can automatically reduce inventory by the amount ordered by a customer. In the examples, each method adds a product named Chai to the customer's order.

Before you begin, open the Products table in the Northwind sample database. If you have not modified the Northwind sample database, product ID #1 (that is, Chai) has a value of 39 in the UnitsInStock field. After a customer places an order for 9 items of Chai, the UnitsInStock field will automatically be reduced to 30.

The examples affect only new orders, not changes to existing orders. If you change the quantity of an existing order, the UnitsInStock field will not be updated. In many cases, it is bad business practice to change an existing order. Instead, if a customer requires more of the same product, you should enter a new order.

These examples do not consider that customers may return orders. It is better that you design an entirely new form to track and increment the units in stock whenever a customer returns an order.

These examples make use of the existing Orders and Orders Subform form in the Northwind database. The BeforeUpdate property of the Orders Subform form already contains code. Do not modify the existing code in any way. Add the sample code to the BeforeUpdate event of the Orders Subform form, but do not replace the existing code. Inserted the code in the examples after the existing End If statement and before the End Sub statement.

Method 1 - Using an Update Query (Applies to Jet Databases Only):

  1. In the sample Northwind database, click Queries under Objects, and then click New.
  2. Click Design View, and then click OK.
  3. In the Show Table dialog box, click Products, click Add, and then click Close.
  4. Add the ProductID and UnitsInStock fields to the query design grid.
  5. On the Query menu, click Update Query.
  6. In the Criteria cell for the ProductID field, type the following criteria:

[Forms]![Orders]![Orders Subform].[Form]![ProductID]

  1. In the UpdateTo cell for the UnitsInStock field, type the following expression:

[UnitsInStock]-[Forms]![Orders]![Orders Subform].[Form]![Quantity]

  1. Save the query with the name qryUpdateUnitsInStock, and then close the query.
  2. Click Forms under Objects, and then open the Orders Subform form in Design view.
  3. If the property sheet is not already displayed, click Properties on the View menu.
  4. In the property sheet, click the Build (...) button next to the BeforeUpdate property. Note that there is already code for the BeforeUpdate event property.
  5. Insert the following code within the existing code (insert this code after the End If statement and before the End Sub statement):
If Me.NewRecord Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUpdateUnitsInStock", acViewNormal, acEdit
    DoCmd.SetWarnings True
End If
  1. On the Debug menu, click Compile Northwind.
  2. Close the Visual Basic editor, and then save and close the Orders Subform form.
  3. Open the Orders form in Form view, browse to the blank row on the Orders Subform form, and for the Product control, choose Chai.

    NOTE: The Quantity field always defaults to a value of 1 when a new record is added to Orders Subform form.
  4. Increase the Quantity control to a value of 9, and then on the Records menu, click Save Record.
  5. Close the Orders form, and then open the Products table in Datasheet view. Note that the UnitsInStock field for the product Chai now contains a value of 30.

Method 2 - Using Recordset Code (Applies to both Jet databases and Access Projects):

  1. In the Northwind or NorthwindCS sample database, click Forms under Objects, and then open the Orders Subform form in Design view.
  2. If the property sheet is not already displayed, click Properties on the View menu.
  3. In the property sheet, click the Build (...) button next to the BeforeUpdate property. Note that there is already code for the BeforeUpdate event property.
  4. Insert either the following DAO code or ADO code in the existing code.

DAO Code (Microsoft DAO 3.6 Object Library - for Jet databases only):

If Me.NewRecord Then
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
        
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Products", dbOpenTable)
        
    rst.Index = "PrimaryKey"
    rst.Seek "=", Me!ProductID
    rst.Edit
    rst("UnitsInStock") = rst("UnitsInStock") - Me!Quantity
    rst.Update
    rst.Close
        
    Set rst = Nothing
    Set dbs = Nothing
End If

ADO Code (Microsoft ActiveX Data Objects 2.1 Library - for Jet databases and Access Projects):

If Me.NewRecord Then
    Dim con As New ADODB.Connection
    Dim rst As ADODB.Recordset
        
    Set con = CurrentProject.Connection
    Set rst = New ADODB.Recordset
        
    rst.Open "Products", con, adOpenKeyset, adLockOptimistic
        
    rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
    rst("UnitsInStock") = rst("UnitsInStock") - Me!Quantity
    rst.Update
    rst.Close
        
    Set rst = Nothing
    Set con = Nothing
End If
  1. On the Debug menu, click Compile Northwind (or Compile NorthwindCS). If any compile errors occur, click References on the Tools menu. Make sure that you have referenced the appropriate DAO or ADO library (listed in the heading for each code section).
  2. Close the Visual Basic Editor, and then save and close the Orders Subform form.
  3. Open the Orders form in Form view, browse to the blank row on the Orders Subform form, and for the Product control, choose Chai.
  4. Set the Quantity control to a value of 9, and then on the Records menu, click Save Record.
  5. Close the Orders form, and then open the Products table in Datasheet view. Note that the UnitsInStock field for the product Chai contains a value of 30.