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

This article was previously published under Q252813
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to 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:
235359 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]
  7. In the UpdateTo cell for the UnitsInStock field, type the following expression:
    [UnitsInStock]-[Forms]![Orders]![Orders Subform].[Form]![Quantity]
  8. Save the query with the name qryUpdateUnitsInStock, and then close the query.
  9. Click Forms under Objects, and then open the Orders Subform form in Design view.
  10. If the property sheet is not already displayed, click Properties on the View menu.
  11. In the property sheet, click the Build (...) button next to the BeforeUpdate property. Note that there is already code for the BeforeUpdate event property.
  12. 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 TrueEnd If					
  13. On the Debug menu, click Compile Northwind.
  14. Close the Visual Basic editor, and then save and close the Orders Subform form.
  15. 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.
  16. Increase the Quantity control to a value of 9, and then on the Records menu, click Save Record.
  17. 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 = NothingEnd 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 = NothingEnd If						
  5. 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).
  6. Close the Visual Basic Editor, and then save and close the Orders Subform form.
  7. Open the Orders form in Form view, browse to the blank row on the Orders Subform form, and for the Product control, choose Chai.
  8. Set the Quantity control to a value of 9, and then on the Records menu, click Save Record.
  9. 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.
Properties

Article ID: 252813 - Last Review: 12/05/2015 18:26:58 - Revision: 2.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto KB252813
Feedback