473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

expression in form will not update table field

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
4 8963
PianoMan64
374 Expert 256MB
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
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
3,532 Expert 2GB
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
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

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

Similar topics

7
by: Jack | last post by:
Hi, I have posted this problem before. Apprently, the suggestion took care of the problem. However, still I am getting the above error message. I am using a session variable to transfer a value...
18
by: Steve | last post by:
Hi I have a really weird problem and any assistance would be welcome. I have developed an app in Access 2002. The app runs perfectly on the development machine. I have packaged the app using...
2
by: Lyn | last post by:
Hi, I am working on a genealogy project in which I have two tables: Person -- one record for each person in the family. Each record has a unique Autonum field (IDPerson). Partnerships -- one...
14
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought...
1
by: meganrobertson22 | last post by:
hi everybody- what is the best way to add data from one form to another? i have 2 tables: person and contract. here are some of the fields. table: person personid (autonumber and primary...
2
by: Mike Turco | last post by:
I like using the expression builder for a lot of different things but it isn't always available when I want to use it, for example in the code window, or in all of the control properties. I am...
6
by: Aaron Smith | last post by:
Ok. I have a dataset that has multiple tables in it. In one of the child tables, I have a column that I added to the DataSet (Not in the DataSource). This column does not need to be stored in the...
10
by: sandraz444 | last post by:
I have an expression in the query under my form to autofill the date under a certain condition but it wont write to the underlying table?? The date shows in the form but not the table. Does anyone...
1
by: koehlerc14 | last post by:
So here is the deal, I am attempting to make what is the most complex form i have made yet. It really is not much, but as an amatuer it is a little overwhelming. Here's a few critical background...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.