OnChange event

G

Guest

I would like to have a totals field value change when any of its supporting
fields change.

Eg: I want to add up Field1, Field2, Field3, Field4 and Field5 and would
like it to change on the run. When any of the values of the fields change it
should change the Totals value.

How does that happen?
 
A

Allen Browne

Hi Carl.

What you describe sounds like a spreadsheet, where there are lots of
columns, and a total. That's not how you build a database.

Can I encourage you to build a relational structure instead. In a relational
database:
a) You don't store the total: you calculate it when needed (so it's never
wrong.)

b) You use a related table with many records, instead of many fields
repeating across your table.

Since we don't know what these columns are, let's imagine they are items in
an order, so you need to add up the prices for Product 1, Product 2, Product
3 etc. You would build a relational structure consisting of these tables:
- Product (one record for each product)
- Order (the header record: order number, date, customer, etc)
- Order Details (the line items, consisting of product, quantity, unit
price)
For each order, you would then sum the rows from the OrderDetails that are
part of that order.

You can see an example of how to build such a database by opening the
Northwind sample database that installs with Access. Choose Relationships on
the Tools menu for the picture of how the tables fit together. Then open the
Orders form in design view, and see how the subform products are added up
and the total displayed.

For more info on summing fields, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

To study more about how to build relational structures in general, search on
"normalization." Here's a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
and here's a bunch of specific examples:
http://www.databaseanswers.com/data_models/index.htm
and here's one more concrete explanation:
http://allenbrowne.com/casu-06.html
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top