Changing the value of a calculated text box

[Boreal2009]

Hi Everyone,

I'm still pretty new to forms so hopefully this is not a silly question. I have a text box field that needs to be a calculation based on other fields in the form. Most of the time this calculated value will be correct but the client wants to have the option to edit this field to a more accurate value when it is available.

When the calculation is put in the control source, it is not possible to change this calcuated value. I have tired putting the calculation in the default value but then when a more accurate value is entered, every instance of the field is updated to that value.

Is here a way to overwrite a calculated value in a text box?

Thanks in advance.

 

[pbaldy]

You could use the technique described here, which would allow the value to be overridden:

http://allenbrowne.com/casu-14.html

By the way, is "Boreal" anything to do with the ski area of the same name?

 

[Boreal2009]

Thanks for the info Paul. The solution that you recommended seems to give me the same results as when I place my calcuation in the default value - all the instances of that field are replaced with the hard entered value. Any other ideas?

PS - Boreal is not directly associated with the ski area. It is the name of my company which was based on the type of forest (boreal forest) that covers a good portion of northern Canada.

 

[pbaldy]

You have it in a continuous form? I would expect the textbox to be bound to the field in the underlying table. That should stop all instances from getting the same value.

Ah; there's a Boreal ski area in California, USA.

 

[Boreal2009]

The only way I know how to bind the text box is through the control source - that is, my calculation is located in this spot. But as soon as I do that then I can't update the field.

I've reviewed your original idea and I see that it says that I could store the calculated result. Does this mean that I would have to add a field in my table to store this value. I don't really want to store the calculated field in my table but is that the only way around this?

 

[pbaldy]

Well, an unbound control on a continuous form will always repeat the value for all displayed records. I guess I'm not clear on what you're trying to accomplish.

 

[Boreal2009]

Good Morning - Hope you are still willing to help me out with this problem.

Ok - I'll start from the begining again but using my actual data. I currently have a text box called "Hatch Date" that I want to equal "Midpoint Hatch Date" (which is also a calculated text box) most of the time. But, sometimes a more accurate date will be obtained and at that point we want to be able to overwrite the value with this more accurate value.

What I have done to date is to set the control source of "Hatch Date" to = [Midpoint Hatch Date]. Please see the attached word document for a screen shot. However, by doing this I cannot overwrite the value.

So my question is: Is there a way to overwrite a calculated value in a text box? If the answer is yes then how do I do it. If the answer is no then how can I calculate a value and be able to overwrite it?

Thanks again for any time you spare!

Sorry forgot to attach the word doc.

 

[pbaldy]

Well, as the link detailed, you do the calculation in code rather than in the control source:

Me.HatchDate = Me.MidpointHatchDate

Sorry, I can't bring myself to use spaces in field names. You'll need to add brackets if you continue to use them. Doing the calculation in code allows the control source to be bound to a field or left unbound, either of which allows the user to change the value.

You would have that type of code in the after update event of whatever controls affect the calculation. You might also want it in the current event, which fires when the user changes records.

 

[Boreal2009]

Thanks for all your help! I finally got it work.