Why does my Textbox Control Source function get run so many times?

[MatthewGrace]

Hello and thank you for taking time out of your day to help me.

I have query which is correctly yielding dozens of records. I have a report which is correctly displaying said records. No problems so far.

In the Report Footer, I placed a textbox with a Control Source of =getMatthewGrace() which is a custom VBA function I made. By chance, I had made a debug.print statement in that function. When I ran the report just once I noticed that the debug.print statement was getting run dozens of times:



It then occured to me that... this isn't a debug.print thing, no, this is evidence that the Report is running my function many many times just to paint the screen once. Not sure why this is. I feel like I need to understand better what's happening to my program so it correctly runs once. Initially I hypothesized that the function is run 1x per row in the query resultset. This is not true. Then I thought it was the number of rows in the resultset + 1. Not true either. Sometimes I'll even pop back and forth between the Report and the Code module only to discover the immediate window is still being populated at machine gun speeds right before my eyes with another few dozen more lines of "Hi, I'm MatthewGrace".

Any ideas how to make it run only once?
Thank you,


Matthew

[June7]

I tested this with textbox in Detail section - 13 records in report with 13 Debug.Print outputs. And with textbox in report footer there is only one output. This was opening report in PrintPreview. Open in ReportView and I get 13 for Detail section and 14 for Report Footer section.

[MatthewGrace]

For your first experiment with the textbox in the detail section, my thoughts:
- Yes that makes sense that the function is executed 1x per record. This concurs with my results and seems to make sense why Access does this.
- The most interesting part, and what I've learned from your experiment is that running in Print Preview seems to only fire it once. If the report is 2 pages however, it does the debug.print 1x initially, and then another time when I flip to page 2. Hm.

For your second experiment with the textbox in the footer section in Report View, my thoughts:
- It seems like you're getting the same results that I got in my hypothesis - 1x per resultset record + 1. In your case, that was 14 debug.print entries.
- However, in more complex queries like mine which use Group By and multiple WHERE's and even a Count... you can get more than 1 extra entries.

The only thing I can say is that I've learned debug.print will fire greater than the number of records in the recordset. How many exactly, depends on the query for reasons I don't understand.

It sounds like you're unsure why this is too, and I applaud your experiments. Let me tell you why it matters:

1) When I view a report in Report View, the textbox remains blank for a few seconds before it loads the result of running that darn function. And then when it does finally load and display correctly, if I scroll up and down the report during normal casual viewing in such a way that the textbox goes off the screen, but then scroll back down to where it is visible again... it's blank all over again for another few seconds while it loads. Predictably during this time, the debug.print will fire a bunch more times as the function is executed repetitiously for unknown reasons. Bottomline... there's lots of blank delay time before the textbox displays and it feels amateurish. However, if the function runs only 1x, this is much faster and the textbox loads instantly and everything is right in the world.

2) If the function pulls data from linked tables online, I'm under the impression that each invocation of the function has to pull lots of unnecessary data transfer from the server over and over again. I know bandwidth shouldn't be an issue with modern computers but it seems like you can blow through a lot of unneccessary transfer (which Amazon Web Services bills you for btw). I don't know how much data will end up getting pulled in a larger scale application, but I can imagine gigabytes pretty easily. Imagine a report like mine where I have the function getting executed 100x every time you scroll to the textbox in the footer, and each time it calls to AWS to send the results of a query which has a few megabytes of data. My VERY limited understanding of servers leads me to believe that my server will literally have to run the query and do all the thinking necessary to comb through Joined tables with over 10,000 records and return a small subset of those records... 100x. I don't know if that's really how it works, but it feels weird as heck.

So far all I can think of is to produce the Report in Print Preview based on what I've learned from you, thank you June! You're still my database hero all these years later I'll mark as solved soon and accept this as a workaround.

In the meantime, if anyone else can either shed light on why Access has this behavior, or can ease my mind that I'm not asking my server to execute the same Queries dozens of times (or more) for each report with a footer textbox, please let us know.

[CJ_London]

reports are not like forms, they work differently - in particular the order of events
https://support.microsoft.com/en-us/...86553682f9#bm3


In particular take note of

Security You can use Report View to enable user filtering of reports. But unlike Print Preview, the Format and Print events in any section don’t occur in Report View. This also applies to VBA function results and user defined procedures that display in controls (such as label captions, display status, conditional formatting, resizing of controls and so on) within those events. Therefore, do not use code in this event to format, hide, or print confidential data, which might become exposed. We recommend pre-filtering the data or disabling Report Preview by setting the AllowReportView property to No.

This would explain why the function is triggered once in print preview since each section will only be formatted once and multiple times in report view.

For your two page experiment - formatting only occurs when the section is about to be displayed. Not tested, but pretty sure if you return to page one, the function will not fire since formatting has already been done.