Have an expression on a form update a table field

[MaDmiX]

Hi Everyone,

I might be out of luck here, but is there a way to have a table field updated via an expression on a form control? I have a text box on my form that calculates a value based on two other text boxes on the same form. The problem is that I can't get it to update the corresponding table field. If I set the text box's control source to the expression, the result displays correctly on the form but, of course it does not update the table.

I thought I could do it by putting the expression in the default value property of the text box and leaving the control source as the table field but this is not working as I expected. Is it possible to do what I want? And if so, how?

Thanks,

Ken

 

[zevw]

After getting your calculation

Run

DoCmd.RunSql "blabla"

and the then do DoCmd.Requery or DoCmd.Refresh

to get the most updated info from your table

 

[sdk]

Hi,

Normally you don't need to capture/store a calcuated value, unless it is very complex / often used etc which may impact on performance.

That said, you can use the update event for either (probably both) 'source' fields to setValue of the calculated field:

vba: yourField = field1 + field2
or use macro command SetValue

but really, I wouldn't bother - it is generally easier and more reliable to use the calculated value. eg what if the value in the table is updated - therefore out of sync with the source fields?

Cheers

Steve

 

[MaDmiX]

I have been trying to create a field in my form that will update my table with the current date everytime the form is saved (a way of keeping track of the most recent change date). I have tried the Date()expression in the before and after update properties but it doesn't do anything, and if I use it in the default value property it updates everytime the record is accessed. Can anyone help me understand what I'm doing wrong?

Thanks,

Ken

PS I would like to do the same thing with the CurrentUser () but I guess once I know how to do one, the other will be similar.