Function In Query Called Multiple Times

[Orlanzo]

Hello All,

I've come across a curious issue I've not encountered in the past. A
function within a query appears to be called whenever the screen is
repainted.

The query runs fine and returns a set of fields for each record. One column
calls a function based on the solution described in the following Microsoft
knowledge base article:

ACC: Adding Dynamic Counter to Query to Count Records
http://support.microsoft.com/kb/94397/en-us

Once the query results are returned, minimize and maximize the query window.
Notice the values within the coumn derived from the function have changed.
You can also place another window in front of the query results too and cause
similar behavior.

I referred to the another MS article which states the function should be
called only once.

ACC: Number of Times a Custom Function Runs in a Query
http://support.microsoft.com/kb/98788/en-us

Any help you can provide would be appreciated.

Kind regards,
Orlanzo

 

[Michel Walsh]

Hi,


Indeed, same thing occur with a page down, page up... values are recomputed,
as "required"... they are not computed once for all, for all records, at the
opening and "locally" stored, somehow. They are executed as needed, which
save a lot of time if you display only some records (out of 1000s).


Hoping it may help,
Vanderghast, Access MVP

 

[Orlanzo]

Hello Michel,

You are right - it would be faster. However, I'd say its more of a problem
than a true benefit.

I can't say the behavior is something I or most others would expect. Their
is a tremendous difference between having a function called once for
"dispalyed" records versus being called whenever the screen is repainted
(ego. multiple times) for the same set of records.

I wondered what would happen if you have a columnar expression doing nothing
more than calling the Now() function... would you see the seconds
incrementing as the screen is repainted? I tried it and you do not see the
same behavior. The value remains constant for each record. (And Now() is a
non-deterministic function too isn't it?)

I would expect similar behavior when other functions are called. Would you
not agree?

Orlanzo

 

[Michel Walsh]

Hi,

Now() won't change because the painted window, the control, is not
invalidated. The results of the VBA functions are also somehow buffered, for
the displayed records (are as the values of the fields), so, if there is
just one record new (while 15 are displayed), due to a scrolling down by
one, the function will be called just for that newly displayed record, not
for the 14 other. You can easily experience it with a static variable:



Public Function MySub(Dummy As Variant) As Long
Static i AS long
i=i+1
MySub=i
End Function


and, in the query, have something like:

SELECT MySub(someField) FROM ...


True, it may not be as someone expected, and that is probably why MS SQL
Server add an extra 'forbiddance on the function you can use: only functions
knows as returning always the same result, given arguments, are allowed...
and the actual date_time is not. Jet does not make that extra check, and
so, allows more functions than MS SQL Server, but that comes with the
problem you outlined.



Hoping it may help,
Vanderghast, Access MVP