Text Box recalculates when pressed

[HedgeKAM]

I have a form that has a subform with lots of text boxes. The text boxes source is a VBA function that calculates some number. The problem is, each time I scroll the subform or click into one of the text boxes, it recalculates the numbers. This makes the form very slow and jerky. Is there a way to make the subform just calculate the text box numbers when it is loaded and not after that?

Thanks in advance for any help on the issue!

 

[smercer]

yes there is. I'd say you have your event procedure on "OnClick" in the text box and/or "OnScroll" in your form. this is what is making your calculations each time you Click on a text box or scroll.

1) choose the declarations drop down list and pick on load

2) copy and paste the code that is in "OnClick" in the text box and/or "OnScroll" in your VBA and put it in "OnLoad".

3) in your form properties delete the entry in"OnScroll"
and choose "Event Procedure" in "On Load"

4) Same again for the text box in "OnClick"

5) test

Does this help

 

[HedgeKAM]

no, its on the ControlSource of the Text Box. (=fncFundName([FundID]))

There is no on load event on the text box?

 

[smercer]

You have to be in the form properties or the on load won't appear. when you are in design view what you do is where the two rulers meet in the subform (Top left) is a little squire. click on this and a black squire will appear in this. right click and choose properties. Now you will be able to see the onload under the events tab.

Did this help?

 

[HedgeKAM]

I know the form has a OnLoad event, but that wont help my problem. My subform is a continous form, showing a recordset. it has a Text Box in its Detail area. The Text Box's Control Source is a VBA function that returns a number. When the recordset is large, the subform will ex. have 100 of the text box listed. Everytime I scroll or press one of the Text Boxes, it reruns the function in the ControlSource. I cant set the OnLoad of the subform to the function, because the function just returns a number, it would make no sense to put it there as it has to be linked to the Text Box, as thats where I want the number shown. The Text Box has no OnLoad event, so I was hoping there is some setting that disables the fact that the Text Box updates its ControlSource everytime one clicks it or scrolls.

 

[smercer]

You can use the macro "SetValue". what this does is changes a value in a text box (And even more if you want me to go on about it). even if you have this macro in the onload in the form it will still change for the txt box, you just have to specify which control you want the calculation in and will work it's wonders.

 

[HedgeKAM]

That would have worked if the Record Source of the subform was static, but it isnt . I have another subform in the main form, each record in that subform has a button that changes the Record Source of the subform I am trying to do. If I have a OnLoad macro, it will only load the first time, not the times I change the Record Source and requery the subform.

 

[smercer]

What I would do is have a macro with event "After Update" (or simalar) to repaint the subform when you change the subforms. Use "repaint" to refresh forms. use "requery" to refresh queries.

You need to be positive about the way you approach things. and remember if you can not do it this way, there is a way around it.

 

[HedgeKAM]

Thanks for the help!
It doesnt work yet, but you have given me a few ideas of how it might be solved. Ill post if I find a solution

 

[Rich]

using a Macro is not being positive, you could always use the function in a query and base the form on that, though it's not always the best way