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

Article translations Article translations
Article ID: 252813 - View products that this article applies to.
This article was previously published under Q252813
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

Expand all | Collapse all

On This Page

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 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.

Properties

Article ID: 252813 - Last Review: June 29, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto KB252813

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com