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

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 True
    End 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 = 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
   						

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.