Update field from form textbox

[alorenzini]

Using Access 2007, I have a form with a unbound control that uses an expression to calculate a total: =[TxtReimburseTotal]+[txtElectTotal]+[txtEducationTotal]+[txtTravelTotal]+[txtMiscTotal] as the control source. I need to take this value and update a bound textbox which controlsource is set to a field in the database and if the amounts changes to the unbound they need to automatically be reflexed in the bound text box.


Thanks Art

 

[pbaldy]

Generally you shouldn't store a calculated value, in part due to the difficulty in keeping it updated. If you must:

http://support.microsoft.com/kb/209172/en-us

though I'd use code rather than a macro.

 

[alorenzini]

I tried to create a macro but there is not a SetValue option. I did it with an expression but it does not update if I change values.

 

[pbaldy]

I still wouldn't store it, but

Me.BoundTextBoxName = Me.UnboundTextBoxName

will copy the value in VBA.

 

[alorenzini]

If you wouldn't store it, how would you save the value to use on like summary reports later on?

 

[pbaldy]

I guess I should clarify that opinion is based on the assumption that the other values (like TxtReimburseTotal) are saved. Presuming they are, then on reports or whatever you just recreate that calculation. If they're not, and you're only saving the total, them I'm all wet.

If you read up on "normalization" you'll see the concept I'm talking about. In general, if I have Quantity and Price saved, I don't have to save Total. I can always multiply Price times Quantity to get it. That saves me having to worry about updating Total any time Price or Quantity gets changed. As a pragmatist, I'll admit that there are times when it's more practical to go ahead and save a calculated value, and I've done it myself. It's just that as a general rule, you shouldn't.

 

[alorenzini]

Your are correct, that the txtReimburseTotal as well as the other fields referred in the expression are not save but calculated on the form. That is why I was trying to move the calculated GrantTotal if you will into the database field.

 

[pbaldy]

Well then I'm all wet. If you're not saving the pieces, you obviously have to save the total. The code in post 4 should copy the value for you.

 

[alorenzini]

I appreciate all your help, but if I can ask this, what event would I use for this? My main goal is to have the database field update on the fly as the form total field is updated.