APPLIES TO: ACCESS 2007, 2010 (UNTESTED IN EARLIER VERSIONS)
This tutorial will show you how to easily look up the total value of a customer order from an orders table and display it in a text box on a form.
The tutorial assumes that your order table is called “tblOrders” and has at least the following two fields:
orderID
itemCost
In reality of course you will have other fields in your table and you can call the fields and table whatever you like.
Again for the purpose of this tutorial your form should be called “frmViewOrder” and have a text box called “orderID” that holds the value of the order you want to look up.
To display the total value in a text box on your form for any rows in the order table with matching orderIDs do the following:
1. Select the text box you want to show the value you in
2. In the tools section of the “Design” tab click on the item called “Property Sheet”
3. Select the “Data Tab”
4. In the “Control Source” property field enter the following:
=DSum(“itemCost”,”tblOrders”,”orderID= forms![frmViewOrder]![orderID] “)
5. Save your changes and test your form / report
If you wanted to display the total value for all the orders in “tblOrders” you would use:
=DSum(“itemCost”,”tblOrders”)
FURTHER INFORMATION