Microsoft KB Archive/252813

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

Article ID: 252813

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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):
 In the sample Northwind database, click Queries under Objects, and then click New. Click Design View, and then click OK. In the Show Table dialog box, click Products, click Add, and then click Close. Add the ProductID and UnitsInStock fields to the query design grid. On the Query menu, click Update Query. In the Criteria cell for the ProductID field, type the following criteria:

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

</li> In the UpdateTo cell for the UnitsInStock field, type the following expression:

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

</li> Save the query with the name qryUpdateUnitsInStock, and then close the query.</li> Click Forms under Objects, and then open the Orders Subform form in Design view.</li> If the property sheet is not already displayed, click Properties on the View menu.</li> In the property sheet, click the Build (...) button next to the BeforeUpdate property. Note that there is already code for the BeforeUpdate event property.</li>  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                   </li> On the Debug menu, click Compile Northwind.</li> Close the Visual Basic editor, and then save and close the Orders Subform form.</li> 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.</li> Increase the Quantity control to a value of 9, and then on the Records menu, click Save Record.</li> 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.</li></ol>

Method 2 - Using Recordset Code (Applies to both Jet databases and Access Projects):
<ol> In the Northwind or NorthwindCS sample database, click Forms under Objects, and then open the Orders Subform form in Design view.</li> If the property sheet is not already displayed, click Properties on the View menu.</li> In the property sheet, click the Build (...) button next to the BeforeUpdate property. Note that there is already code for the BeforeUpdate event property.</li> 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

</li> 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).</li> <li>Close the Visual Basic Editor, and then save and close the Orders Subform form.</li> <li>Open the Orders form in Form view, browse to the blank row on the Orders Subform form, and for the Product control, choose Chai.</li> <li>Set the Quantity control to a value of 9, and then on the Records menu, click Save Record.</li> <li>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.</li></ol>

Keywords: kbhowto KB252813

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.