Updating an unbound field based on a calculated field

[Nosdron]

Help !!

I have a calculated field on a form. I want to look its value up in a table and return a corresponding value from that table to an unbound field. To do this I have used a dlookup.

It works when I add the initial data in the form to generate a value in the calculated control, but when that calculated field subsequently changes the unbound field doesn't update.
How can I get it to 'refresh' each time the data on the form change and subsequently causes changes in my calculated control ?

Apologies if this is a 'easy' question.....

 

[SBendBuckeye]

Put your lookup code in the After Update event for the calculated field. That way it will force a recalc every time it is updated.

Good Luck!

 

[Nosdron]

Hi thanks for the quick reply, but I had already tried that and it didn't work.

When I looked in to the help files it tells me that the afterupdate event doesn't work on calculated unbound fields....any ideas if that is correct or have I misinterpreted it ?

The look up works when I put the dlookup expression in another field, referring to the value of the calculated field, but it only works once, and not continually (dynamically) whilst the form is open and changes are being made to the data.

Any further help would be much appreciated, many thanks....

 

[Rolliee]

us an event to open the recordset and find the value and set the box to whatever you wish - do it with code.

rollie@bwsys.net

 

[Nosdron]

thanks for your reply I will look at some code to do this but how do I trigger the event ?

the items on the form that will change are all calculated fields, will they trigger an afterupdate event ?

 

[SBendBuckeye]

If you put your code in the Form Current code it will be calculated every time a new record is retrieved. If you have controls on the form that would impact the calculation then put your code in the After Update event for those controls.

If you are using the same code multiple times put it in a procedure and call it from wherever.

Good Luck!