By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

expression in form will not update table field

P: 5
Hi, I have a form which updates my table just fine except for some fields in the form are expressions, and the results of the expressions do not populate the table, but the form always remembers the results of the expression. Also if I open the "datasheet" view it looks like the table but with the results of the expressions present in the appropriate fields. I am very new to access so it might be something simple.

Thanks!
Mar 20 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 374
The simple answer to that is because it is calculated. You NEVER want to store calculated values. Because if for any reason that the values change, then you have to update many fields and rows of data in order to keep the information accurate.

Best practices is that you NEVER will store calculated values or Expressions as you're putting it.

Hope that helps,

If you have more questions, please feel free to post again.

Joe P.
Mar 21 '08 #2

P: 5
The simple answer to that is because it is calculated. You NEVER want to store calculated values. Because if for any reason that the values change, then you have to update many fields and rows of data in order to keep the information accurate.

Best practices is that you NEVER will store calculated values or Expressions as you're putting it.

Hope that helps,

If you have more questions, please feel free to post again.

Joe P.
Yeah, that's what I keep reading all over when I research this problem, but if I want to store calculated values in a table, that's really my call to make, not Microsofts. Is there a way to do this? What I am trying to enter is just the price paid for a group of items, then divided by the number of the items, so the calculated value is the price per item. After it's calculated those figures will never change. I see alot of examples of Access being used for inventory, payroll, or billing. If I use a form to generate a billing reciept, which, for example, calculates the cost by comparing hours worked against charge per hour, wouldn't it make sense to permently store this reciept record into a table? Is there a different way perhaps that this is typically accomplished? Again I am very new to access and I appreciate the help.
Mar 21 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
Never is something that probably should seldom be said when referring to database design. There are cases when storing calculated values is acceptable, even cases where it's mandatory!

Many of the acceptable cases involve the very things you've cited. Invoices and such, where the price of items change on a regular, as well as sales tax and shipping costs. The alternative to storing this kind of thing is to have a separate price table, where price and effective dates for the price is stored. Then each time you accress a report or history form, for each and every item, Access has to run a search and check the invoice date against the price at the time the invoice originated. An awful lot of processing to do in order to avoid storing a calculated value, especially considering the cheap memory that's available today.

You haven't said how you're doing these calculations, but I'm guessing that you're using them in the Properties sheet as the Control Source for the textboxes, which is why they're not being stored. If this is the case, you need to bind the textboxes to the appropriate fields in the underlying table and move your calcualtions into VBA code, in the AfterUpdate events, for example, of the textboxes where the supporting data for the calculations resides.

Let us know if you need help with this.

Welcome to TheScripts!

Linq ;0)>
Mar 21 '08 #4

P: 5
Never is something that probably should

You haven't said how you're doing these calculations, but I'm guessing that you're using them in the Properties sheet as the Control Source for the textboxes, which is why they're not being stored. If this is the case, you need to bind the textboxes to the appropriate fields in the underlying table and move your calcualtions into VBA code, in the AfterUpdate events, for example, of the textboxes where the supporting data for the calculations resides.

Let us know if you need help with this.

Welcome to TheScripts!

Linq ;0)>
Hi, Thanks, that is exactly what I was doing, putting the formula into the control source. For a temporary workaround I just added another text box which is not calculated, which does update the table I just need to manually enter the same data is as the field just before that in the form. I won't be doing these entries very often, so I might leave it this way. But I am going to try to do it the way you suggested though and see if I get anywhere.
Mar 25 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.