Form and Table

[answergirl]

A form was created using a specific table with calcuating fields that should automatically fill the table with data. Unfortunately, the data is remaining in the form only with the calculated amounts. I don't feel like answergirl on this one.

 

[fneily]

Make sure to create a field in a table that will hold the result of the calculated control.

Example: We have two fields, Discount and List Price, and we want to calculate Cost which would be =[List Price] * (1-[Discount]). We want to store Cost in the table.

If either Discount of List Price changes, then Cost changes. So :
1. Click the Discount Control
2. Display its Property Sheet
3. Click the Event Tab
4. Click the After Update box
5. Click the Build Button (…) located to the right of the dropdown list
6. Select Code Builder
7. Type [Cost]=[List Price]*(1-[Discount])

Do the same for the List Price control.

Neil

 

[lespaul]

However, storing calculated fields in most instances breaks 3NF rules. Why do you want to store a calculated field?

Leslie

 

[answergirl]

The property sheet does not give me the option tab "event" or steps noted. Further suggestions are appreciated.

 

[answergirl]

the form is a data entry form that should automatically update the table by users who will be entering employee related data into the form only. When keying in hourly rate it calculates the annual salary then calculates the FICA % and Retirement % automatically within the form itself. I want this information to default into the Table but it won't.

 

[lespaul]

Again, why would you want to store this information rather than calculate it on the fly in a query when you need it.

Leslie

 

[lespaul]

Again, why would you want to store this information rather than calculate it on the fly in a query when you need it.

Leslie

 

[answergirl]

other people will be using this application who are not accustomed to the software and what its true capabilities are and want to see the calculations within the table

 

[fneily]

In design view, bring up the Property sheet. Click on the text box for hourly rate. You should see on the property sheet a tab that says Event. You should have in your table fields to store annual salary, Fica %, and retirement %. You should have on your form three corresponding textboxes. Then on the AfterUpdate event of the hourly rate textbox, you would write the appropriate code.

And it's true you should not keep totals in tables because if someone changes something in the table the totals are not automatically updated. And it violates 3NF. But that's up to you.

Neil

 

[lespaul]

Other people shouldn't be looking at the data in the tables anyway. Only through the user interface. Having an application set up where users have direct access to the tables is just asking for trouble with data integrity.



Leslie