Flashing during requery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Me.requery with a form's timer to access new data in the
underlying table. Everything works well except for the calculated text boxes
in the form footer which flash as they are recalculated. Most intervals the
values do not change, but the flashing is VERY distracting. Is there some way
to avoid the flashing? The data in the body of the form doesn't flash.

I found a similar question in a related forum, but there were no replies.
Any advice will be greatly appreciated.

Jeff
 
The only thing I can think of to try (without changing your setup) would be
to turn off the screen echo until the requery is finished. The other option
would be to do the calculations in code and fill the textboxes from there
instead of placing the calculation in the textboxes' control source.

Example for timer event:
Application.Echo False
Me.Requery
DoEvents 'give the requery a chance to finish
Application.Echo True
 
Thank you for the suggestion. The echo modification doesn't seem to stop the
flashing. I originally thought to use a function to calculate the values for
the text box, but was unsure of a couple of things (like whether it would
help.) I tried it this morning but it gets a little complicated. The current
text boxes are:

=Avg([O2%])
=StDev([O2%])/Avg([O2%])*100

and similar functions. The AVG() and StDev() functions are not available in
the VB module environment. Will I have to use something like ADO to recreate
the recordset open in the form, or can I somehow access the recordset already
open by the form's query (and use the ADO AVG() functions)? Or am I missing
the easy way to do the calculations?
Thanks again!
 
Check out the Domain Agregate Functions.

DAvg
DCount
DLookup
DFirst, DLast
DMin, DMax
DStDev, DStDevP
DSum
DVar, DVarP
 
OK. Worked on it a little more, but I'm still confused.

If I create a function, lets say:

Function O2Avg()
O2Avg() = DAvg("[O2%]", "DataRaw")
End Function ;

AND set the text box data control source to =O2Avg();

How is that different from the original situation of using the built in
fucntion =Avg([O2%])? (From a flashing/recalculation viewpoint?)

I apologize if I'm missing something obvious.

Maybe there is some other way to update the form besides using Requery on
the form timer?


Wayne Morgan said:
Check out the Domain Agregate Functions.

DAvg
DCount
DLookup
DFirst, DLast
DMin, DMax
DStDev, DStDevP
DSum
DVar, DVarP

--
Wayne Morgan
MS Access MVP


Jeff_C said:
Thank you for the suggestion. The echo modification doesn't seem to stop
the
flashing. I originally thought to use a function to calculate the values
for
the text box, but was unsure of a couple of things (like whether it would
help.) I tried it this morning but it gets a little complicated. The
current
text boxes are:

=Avg([O2%])
=StDev([O2%])/Avg([O2%])*100

and similar functions. The AVG() and StDev() functions are not available
in
the VB module environment. Will I have to use something like ADO to
recreate
the recordset open in the form, or can I somehow access the recordset
already
open by the form's query (and use the ADO AVG() functions)? Or am I
missing
the easy way to do the calculations?
Click to expand...
Click to expand...
 
You're correct, that wouldn't be any different and in fact would probably be
worse. I was referring to assigning the value to the textbox via code, such
as the form's Current event.

Example:
Me.txtMytextbox = O2Avg()

There may still be a slight delay before the value appears, but it shouldn't
flash. The problem is that it won't auto update either. You'll have to make
sure that you reassign this value every time you make a change that would
cause this value to change.

--
Wayne Morgan
MS Access MVP


Jeff_C said:
OK. Worked on it a little more, but I'm still confused.

If I create a function, lets say:

Function O2Avg()
O2Avg() = DAvg("[O2%]", "DataRaw")
End Function ;

AND set the text box data control source to =O2Avg();

How is that different from the original situation of using the built in
fucntion =Avg([O2%])? (From a flashing/recalculation viewpoint?)

I apologize if I'm missing something obvious.

Maybe there is some other way to update the form besides using Requery on
the form timer?


Wayne Morgan said:
Check out the Domain Agregate Functions.

DAvg
DCount
DLookup
DFirst, DLast
DMin, DMax
DStDev, DStDevP
DSum
DVar, DVarP

--
Wayne Morgan
MS Access MVP


Jeff_C said:
Thank you for the suggestion. The echo modification doesn't seem to
stop
the
flashing. I originally thought to use a function to calculate the
values
for
the text box, but was unsure of a couple of things (like whether it
would
help.) I tried it this morning but it gets a little complicated. The
current
text boxes are:

=Avg([O2%])
=StDev([O2%])/Avg([O2%])*100

and similar functions. The AVG() and StDev() functions are not
available
in
the VB module environment. Will I have to use something like ADO to
recreate
the recordset open in the form, or can I somehow access the recordset
already
open by the form's query (and use the ADO AVG() functions)? Or am I
missing
the easy way to do the calculations?
Click to expand...
Click to expand...
Click to expand...
 
Ahhhh...much better.
Thank you! The missing link was assigning the text box a value in code
bypassing the Access interface. I wondered about this a few times before but
never quite pieced it together. (I'm no programmer, just an end user that
sometimes swims out over my head...)

I ended up with:

Function Calc()
Dim O2Av, O2Stdv, O2RSD, SO2Av, SO2Stdv, SO2RSD As Long
O2Av = DAvg("[O2]", "dataraw")
O2Stdv = DStDev("[O2]", "Dataraw")
If O2Av = 0 Or IsNull(O2Av) Then O2RSD = 0 Else O2RSD = O2Stdv / O2Av * 100
SO2Av = DAvg("[SO2]", "dataraw")
SO2Stdv = DStDev("[SO2]", "Dataraw")
If SO2Av = 0 Or IsNull(SO2Av) Then SO2RSD = 0 Else SO2RSD = SO2Stdv / SO2Av
* 100

Me.O2Avg = O2Av 'Bless Wayne Morgan!
Me.SO2Avg = SO2Av
Me.Text20 = O2RSD
Me.Text27 = SO2RSD

End Function

I call this function after the requery and it all seems good.

Thanks again for taking the time!


Wayne Morgan said:
You're correct, that wouldn't be any different and in fact would probably be
worse. I was referring to assigning the value to the textbox via code, such
as the form's Current event.

Example:
Me.txtMytextbox = O2Avg()

There may still be a slight delay before the value appears, but it shouldn't
flash. The problem is that it won't auto update either. You'll have to make
sure that you reassign this value every time you make a change that would
cause this value to change.

--
Wayne Morgan
MS Access MVP


Jeff_C said:
OK. Worked on it a little more, but I'm still confused.

If I create a function, lets say:

Function O2Avg()
O2Avg() = DAvg("[O2%]", "DataRaw")
End Function ;

AND set the text box data control source to =O2Avg();

How is that different from the original situation of using the built in
fucntion =Avg([O2%])? (From a flashing/recalculation viewpoint?)

I apologize if I'm missing something obvious.

Maybe there is some other way to update the form besides using Requery on
the form timer?


Wayne Morgan said:
Check out the Domain Agregate Functions.

DAvg
DCount
DLookup
DFirst, DLast
DMin, DMax
DStDev, DStDevP
DSum
DVar, DVarP

--
Wayne Morgan
MS Access MVP


Thank you for the suggestion. The echo modification doesn't seem to
stop
the
flashing. I originally thought to use a function to calculate the
values
for
the text box, but was unsure of a couple of things (like whether it
would
help.) I tried it this morning but it gets a little complicated. The
current
text boxes are:

=Avg([O2%])
=StDev([O2%])/Avg([O2%])*100

and similar functions. The AVG() and StDev() functions are not
available
in
the VB module environment. Will I have to use something like ADO to
recreate
the recordset open in the form, or can I somehow access the recordset
already
open by the form's query (and use the ADO AVG() functions)? Or am I
missing
the easy way to do the calculations?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top