Does anyone out there know if a form will come up faster if an expression for a figured amount in a text box is done in the control source for the text box or in the underlying query? Is there any noticeable difference? Thank you in advance for your time and your responses.
If you are talking about one or two records, I have never meet anyone that could tell the difference. If you are talking about hundreds of records, I still doubt of anyone can tell the difference. But the best thing to do is to write a test form and then time it with a whole lot of records.
I do not know how the internal process to calculate controls functions, however, I would hazard a guess and say calculated controls would be rendered at the time the record is scrolled, whereas a calculated FIELD in an underlying recordset is calculated for the whole shebang, right up front. Assuming this is how things are done, which I cannot state definitively, I would expect calculated fields at the datasource level to initially take longer than a calculated control, but provide slightly better performance after the initial datasource has been constructed. Conversely, since no calculations need to occur until it's time to display a specific record, I would expect a calculated control to be much faster up front, but take a nominal performance hit as records are scrolled.
Either way, it's highly unlikely there will be a perceivable difference regardless of which is more efficient.
I believe that using expressions in quieries is a more eloquant way of handling this situation. If you use queries in a hierarchical manner i.e. you use the queries as "building blocks" throughout the application, these expressions are reuseable. I have to concatenate Size from Height Width and Depth and perform this on image and frame in both cms and ins so I only need state this once on a "bottom" query and reuse it numerously.
On the performance depeding on the number of records if the expression is contained on a form this "calculation" can dribble onto the form whereas in th query it is "predetermined" and the form is "painted" instantaneously.
I have seen comments that using expressions in queries is not advisable but to me this is why queries are so good.