OK - you asked < g >. I've also included the "rationale" (more of
empirical observations!) for how/why this works (when it does work) so
that it might provide more background for the idea. Note: I have found
that this method can "hang" (endlessly loop) if there is just one record
in the recordset, but sometimes it will work there too. However, you may
need to trap for this and test yourself in your own situation. Let me know
if you have questions!
-----
I think I've come up with a way to be reasonably foolproof for how to
detect when a calculated textbox has a value that can be read after you
requery the form. Unfortunately, I didn't find an event that I can
use....but, I did find a test that seems to work with 150 records in the
recordsets. Why, you ask, is this "number of records" important? Well,
stay tuned for my explanation..... and, sorry, it's a long explanation
to get to the final method, but it'll help everyone see why this seems to
work.
--------
My brainstorm was the idea of combining a global variable with a function
that is called from the calculated control. Stay with me, as this may get
a bit confusing to explain.
My thought was to wrap the calculation in the textbox's control source
with a simple function that would use the calculation as an argument, so
that the calculation would have to be done before the function could be
called. For example, let's say that my calculation expression in the
textbox were this:
=Sum([Field1]*[Field2])
My "new" expression would be this:
=IsTheCalculationDone(Sum([Field1]*[Field2]))
The IsTheCalculationDone function is very simple:
Public Function IsTheCalculationDone(varValue As Variant) As Variant
IsTheCalculationDone = varValue
End Function
Simple, eh? And then I figured that I would set the value of a global
variable in this function to tell me when the function had run:
Public gblnCalcIsDone As Boolean
Public Function IsTheCalculationDone(varValue As Variant) As Variant
IsTheCalculationDone = varValue
gblnCalcIsDone = True
End Function
And my code that forced the requery then would be this:
gblnCalcIsDone = False
Forms("Name").Controls("Subform").Requery
Do
DoEvents
Loop While gblnCalcIsDone = False
Aha, said I. This is too easy! And it was... because it didn't work quite
as planned. For you see, when a form is "queried" and there is a
calculation expression in a control source, it turns out that the
calculation is actually "done" *for each record in the form's recordset*.
And in fact, the calculation is performed with Null records, then once for
each record in the recordset. Therefore, if the form's recordset contains
5 records, the calculation is actually called 6 times.
However, the calculated control actually doesn't need to go through all
these calculations in order to get its value. In my testing, the value
appears in the textbox after the calculation is done for the first record
(the second calculation) if the recordset is small, or after the
calculation is done for the second record (the third calculation). With
very large recordsets, it might take more than that, but I found that the
third calculation call usually was enough time to then be able to read the
textbox.
I'm sure you're all seeing what the problem is. The first call, when there
are "null" records in the calculation, to the function
IsTheCalculationDone sets the global variable to True, and my code that
reads the variable merrily goes on its way, thinking that all is well,
when in fact the situation that I was trying to avoid is still present.
Namely, there is no valid value in the textbox yet to be read.
So, I decided to use the multiple calculations as a way to count the
calls, essentially using a numeric variable as the global variable, and
then to test for the value of that global variable to see if it had
exceeded a minimum value (because of my testing above, I settled on the
minimum between 2 and the number of records in the form's recordset).
So, I changed my public variable and function to this:
Public glngCalcIsDone As Long
Public Function IsTheCalculationDone(varValue As _
Variant) As Variant
IsTheCalculationDone = varValue
glngCalcIsDone = glngCalcIsDone + 1
End Function
And I changed my code that forced the requery to this:
glngCalcIsDone = 0
Forms("Name").Controls("Subform").Requery
Do
DoEvents
Loop While glngCalcIsDone <= _
VariantMin(2, Forms("Name").Controls("Subform"). _
Form.RecordsetClone.RecordCount)
VariantMin is a function that I use to return the minimum of two values
passed as the arguments:
Public Function VariantMin(ByVal var1 As Variant, _
ByVal var2 As Variant) As Variant
On Error Resume Next
VariantMin = var1
If var1 > var2 Then VariantMin = var2
Err.Clear
End Function
Voila! I seem to have completely eliminated the problem of trying to
decide when the calculated textbox contains a valid value that can be read
and used.
P.S. One additional observation I noted while doing this experimentation.
If you have a main form in continuous forms view, the calculation is not
run for every record that is in the recordset. ACCESS doesn't "fill" the
recordset completely unless you force the form to display all the records.
So, although the calculation in the calculated textbox is correct, the
calculation is called only for the records that are displayed on the form
(what the user can see).
If you scroll down the form, as each "additional" record comes into view,
the calculation is again called -- and this is where it got tricky to
figure out what is happening.
It seems that, when you scroll down by clicking in the "grey" space below
the scroll bar (causing the form to jump down *one page*, the calculation
is triggered again from the "null" records and then is done again for each
record from the first to the last one you can then see on the form. Thus,
if you have 23 records in the recordset, and if you can see 16 records at
a time on the screen, the initial screen "load" will cause the calculation
to run 17 times. If you then "page down", and expose the remaining 7
records of the entire 23 records, the total number of times that the
calculation is run is up to 41 times (1 for null, 16 for initial records
visible, 1 for null again, and 23 for all records). (It's even more
complicated to predict the number of calculations if you would have to do
multiple "page downs" to scroll through the entire recordset, but it grows
quickly.)
However, if you scroll down by clicking the down arrow for the scroll bar
(causing the form to jump down *one record*, the calculation is triggered
again only for the newly "visible" record. So, using the above example,
this method of scrolling would cause the calculation to be run 24 times
total.
This is why I decided to use a "minimum" number of calculations to decide
that the calculated textbox's value was correct. I had tried to test for
when the count equaled the number of records plus 1 and quickly ran into
"unending DoEvent loops".
--------------------------------------
Thus, here are the steps to take when you want to implement this
capability:
----------
1. Declare a global Long variable in a regular module (e.g.,
glngCalcIsDone).
2. Create a public function in a regular module:
Public Function IsTheCalculationDone(varValue As _
Variant) As Variant
IsTheCalculationDone = varValue
glngCalcIsDone = glngCalcIsDone + 1
End Function
3. For the control source of a calculated textbox that you'll want
to read, wrap the calculation with this public function:
=IsTheCalculationDone(...)
4. In the Open event of the form that contains this calculated textbox,
put this code step in order to "reset" the global variable (note
that the public function is called when the form loads, so this is
to
avoid the (possibly unlikely) scenario of the upper numeric limit
of the Long variable being exceeded during normal operations):
glngCalcIsDone = 0
5. When you do a requery and need to read the value from the
calculated textbox, use these code steps (where VariantMin
is a function similar to the one I posted above, and
MaxCalcsNeeded is a number that you would supply as the
minimum number of calculations to have done before you
read the value -- in my case, I was using 2):
Dim lngCountComparison As Long
glngCalcIsDone = 0
Forms("Name").Controls("Subform").Requery
DoEvents
lngCountComparison = VariantMin(MaxCalcsNeeded,
Forms("Name").Form. _
RecordsetClone.RecordCount)
Do
DoEvents
Loop While glngCalcIsDone <= lngCountComparison
----------
--
Ken Snell
<MS ACCESS MVP>
Al Camp said:
Ken Snell (MVP) said:
This works fine, but, if I re-click the [+] button too quickly, I
can
outpace the Sub2 Requery, and receive erroneous messages pertaining to
the
TotalOnHand "criteria" logic. (thinks TotalOnHand = 0)
Of course, normal manual data entry for Qty works fine...
That's what I was afraid of. Your code is trying to use a
calculated value. Unfortunately, the calculation task runs
at a lower priority than the VBA code execution task so your
code and user interactions can easily get ahead of control
calculations. I used to say that this is impossible to
synchronize, but Ken figured something out. I didn't fully
understand it's convoluted contortions, but he did say it
seemed to work in his situation.
I did figure out a way to have the code "count" the number of
"recalculation" firings that occur when a calculated control in a
continuous forms view is being recalculated via a form requery. I have
found that it doesn't always work if there is just one record in the
form, but if there are three (two seems to work, too) or more, it seems
to be pretty reliable. "Convoluted" is one word for it -- it involves
using a global variable and a public function.
If you're interested in trying it, Al, let me know and I'll post it for
you to try.
---------------------------------------
Please do Ken... I'm willing to give anything a go.
As I told Marsh, I may try a "timer" delay (.3 secs or so with our
disable/enable scheme) this morning. Seems to me the timer must run to
completion before the button will be re-enabled.
Al Camp
