Change Text Box Value When Changing Records 2

[vikin9]

I have an unbound text box in a Form footer that calculates work days based on two dates in a form. The work day value is calculated with code (per faq 181-261, which works great I might add). I have it working properly using a command button.

However, I want the value to calculate automatically for whatever record is selected with the mouse or nav buttons (as if it were simply an [EndDate]-[StartDate] calculation in a text box).

To my knowledge, there isn't an "On Change Record" event property for Forms. How can I get the control to calculate automatically?

Better yet, is there a way to run this same code calculation within the query for all the records?

I'd be grateful for any suggestions. Thanks.

 

[SBendBuckeye]

You can put a calculation in a query by creating a calculated field that points to a function defined in a module. Put your date calculation code in a function and have it return the result back to your query.

Good Luck!

 

[Rolliee]

vikin8,

It sounds to me like you want the form's on current event. It updates with each new filliing of a record from the recordset.

rollie@bwsys.net

 

[vikin9]

Thanks for the fast responses!

Rolliee:
OnCurrent worked great, thanks!

SBendBuckeye:
That makes sense. How do you refer to a function in a calculated query? I've done some pretty involved calculated query fields, but I don't see how to pull the Function into it.

 

[vikin9]

Thanks, I've got it.

Put the Function in a Module and then refer to it.

WorkDays: DeltaDays([StartDate],[EndDate])