Can a calculated field be saved permanently

[jerrywheels]

Can the value of a calculated field, not defined in a table, be saved permanently each time the field changes value, or at some other point defined by a criteria?
thanks

 

[rainman89]

printing it out is probably as permanent as that gets! why save calculated data when u can just retrieve it again with a query?

 

[boblarson]

Unless you have an overriding reason to store calculated data (like I do with a sale and have to store the products with the prices at the time of sale) then calculate it on the fly with a query. And if you say because the users are accessing the data in the tables directly, then they shouldn't be doing that and all interaction should be with forms.

 

[jerrywheels]

thanks for the help on the saving calculated data

I need to know on a daily basis the total on hand of a product which is updated randomly and have view screens and reports printed reflecting that data. But you are right, i can have it calculated each time. thanks so much for the help; I WILL be asking for more and will apprecite it again.
Jerry bennett
Northampton, MA

 

[missinglinq]

The total amount of a product on hand is probably one of those things that you do want to store rather than recalculate each time. Sure, you could calculate it each time, but do you really want to?

Say you have 100 Widgets in stock. Each time one is sold you subtract 1 from the inventory number. Each time you receive a new order of Widgets from the manufacturer, you add that number to the amount on hand. And so on...but how many years do you want to do this? In the year 2017, do you still want to be calculating the number of Widgets currently on hand by starting out with the number on hand in 2007 and adding and subtracting the hundreds of transactions in the past ten years? Doesn't seem reasonable to me!

I just realized, no one really answered your intitial question. You would have to have a
field in your table to hold the amount on hand. Even if you want to calculate the amount on hand over and over again, the amount you start out with on hand has to be stored in a table in order to available as the basis for your calculation.

 

[boblarson]

And that is an overriding reason

 

[missinglinq]

Zackly, Bob! I have to tell you, it's good to see someone actually admit to storing calculated values! So many people are stiff necked about it, automatically railing against it without stopping to think about what the poster is actually trying to accomplish!. You'll see people who develop apps that Rube Goldberg make proud, in order to keep from storing a calculated results!

 

[Fifty2One]

on the text box that you have your calculated value, use the After Update event to save the content to your table... i just did this for truck bills with three boxes - one for width, one for length, one for height - the result is saved in PALLET_DIMENTIONS on my truckbill table when the height is entered...

Private Sub Text20_AfterUpdate()
Me.PALLET_DIMENTIONS = Me.Text16 & " x " & Me.Text18 & " x " & Me.Text20
End Sub

 

[Rich]

Actually the stock on hand should only be stored as a result of periodic stock takes from where the balance should be calculated

 

[robina]

I am trying to do the same thing. On a form I have 3 controls. They are all bound to the same table and all in text boxes. I have the field named [txtLton] calculating as one of the other fields divided by the other. It calculates beautifly and stores in the table but the problem is I don't want it to save to the table until I click my save command button. Any suggestions?

thank you

 

[deputy herb]

I am trying to do the same thing. On a form I have 3 controls. They are all bound to the same table and all in text boxes. I have the field named [txtLton] calculating as one of the other fields divided by the other. It calculates beautifly and stores in the table but the problem is I don't want it to save to the table until I click my save command button. Any suggestions?

You would need to make txtLton an unbound control and then with the click event on the save button do a manual INSERT or UPDATE sql

 

[missinglinq]

Three questions:

1. Why do you want to Save/Store a Calculated Value, having read the advice above that says this is usually a bad idea?
2. Why are you trying to go against the way Access is designed to work, by using a 'save' button?
3. If you insist on saving the Calculated Value, why do you not want to save it until the user clicks a Command Button?

Linq ;0)>

 

[Pat Hartman]

You would need to make txtLton an unbound control and then with the click event on the save button do a manual INSERT or UPDATE sql

No! While saving calculated values is almost always wrong, resorting to unbound controls or forms is not necessary. All you have to do is to understand and learn how to use the Form's BeforeUpdate event. That gives you COMPLETE control over when and if changes get saved. If you try to trap all the places that Access helps you by saving data, you'll drive yourself nuts and probably fail anyway. Just learn the RIGHT way to control saving and life will be good

 

[deputy herb]

No! While saving calculated values is almost always wrong, resorting to unbound controls or forms is not necessary. All you have to do is to understand and learn how to use the Form's BeforeUpdate event. That gives you COMPLETE control over when and if changes get saved. If you try to trap all the places that Access helps you by saving data, you'll drive yourself nuts and probably fail anyway. Just learn the RIGHT way to control saving and life will be good

I disagree. If you were building a database program in almost any other language like any of the .NET languages or any web language there are no bound controls. You post everything manually and it seems to work fine, although it is quite a bit more coding. And you can't do transactions in access with bound controls.

I don't know what the circumstance are that make them want to do it in this fashion, if it's just validating data or there's something more to it but they asked and I answered. I don't think there's a RIGHT way to handle something like that. My 2 cents anyways.

 

[Pat Hartman]

in almost any other language like any of the .NET languages or any web language there are no bound controls

And this is relevant, why?

I believe that this is an ACCESS forum. It doesn't really matter how the .NET languages work. It doesn't matter how I would have done it in COBOL 30 years ago. What matters is how ACCESS does things. You wouldn't use FORTRAN techniques when writing .NET code. Don't use .NET techniques when writing Access VBA. Access is geared to working with bound forms. Access tries very hard to help you by saving records willy-nilly (OK, there is method to its madness but newbies don't always see it). The point is, when working with Access, you need to know how to control ACCESS. You'll be a very unhappy camper if you try to impose your own version of what's right in some other language on Access. Just because it is right in .NET doesn't make it right in Access.

 

[deputy herb]

And this is relevant, why?

I believe that this is an ACCESS forum. It doesn't really matter how the .NET languages work.

Whoa.

I regularly bring techinques from one language to another. I comment the same, I normalize a database the same, programs frequently flow the same, trap errors the same. I'm also unsure how you would do a transaction with bound controls. Also if I had to ever upsize an access program to C#.NET I would love it if the programmer trapped things on events that were common across many languages.

In the end we disagree and I think it's ok for there to be more than one opinion out there.

 

[missinglinq]

...I don't think there's a RIGHT way to handle something like that...

Sorry, but Pat is absolutely correct here! There is a right way in Access, and it's through Bound Forms! A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Several developers I know, experienced in Visual Basic/.NET database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long as it does when using Access and Bound Forms.

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security if far, far better than anything you can do in Access

The long and short of it is, if you don't like the way Access is intended to work use something else!

Linq ;0)>

 

[deputy herb]

I agree. Bound controls is the key to making Access a RAD environment. I would absolutely advise against going the all unbound route. Heck I don't think JET has a goood way of retrieving the auto number key from a insert just made.

But the OP was asking about one text box on one form. I don't know the reasons but I do know what sometimes you get into a situation where the answer isn't the typical way to do it. Pat quoted me, not the OP, in his original reply saying how wrong I was. I disagree. 1 textbox 1 form I don't think that will break Access or make maintaining the application unbearable.

 

[spikepl]

deputy herb: ppl ask lots of questions here.

"How can I put my fingers into the wall socket power outlet", on most occasions, leads to a response of "Why ?" rather than detailed instructions on how to do it.

The "Why?" may sometimes reveal a good reason, but mostly ignorance of ways Access drives the urge to do the deed. So yes, you are right and wrong: things can be done in different ways, but sometimes asking why prevents the OP from acquiring bad habits that eventually will bite him or her in the derrière.

 

[VilaRestal]

I've got to side with Deputy who seems to be being ganged-up on for suggesting the heinous crime of using an unbound control.

If unbound controls were such a bad idea in Access they wouldn't exist. There are plenty of cases where they are required - bound controls just will not do. (Complex transactions that should either succeed in bulk or fail in bulk but never in part, would be a good example.)

I'm not sure an unbound control is needed in this case but Pat's argument does seem to read as 'never use unbound forms or controls', which is bad advice to anyone reading.

And as for the statement: "if you don't like the way Access is intended to work use something else!".

Again, I wholeheartedly disagree. The sample databases on this website are full of examples of people using Access in a way it was never intended to be used to great effect. Half of the art of mastering Access is being able to do these things with it. Limiting yourself in this way is not good. Be inventive. Think outside the box. That's what makes a great developer in any language.