Reference SubTotals on SubForm too slow

[Tony_VBACoder]

With Access 2000/2002, I am using a MainForm with one SubForm, where on my
Sub Form I have a text box titled "txtSubTotal" in the Form Footer, whose
ControlSource = Sum([Quantity]), so that it will calculate the total
Quantity. On my MainForm, I have a text box located below my SubForm titled
"txtSubTotal", whose ControlSource references my "txtSubTotal" text box on my
SubForm, so that the user can see the SubTotal of the records on the SubForm.

My problem is, I am finding that this text box on my "MainForm" is
recalculating too slow, because I am using code to reference this field to do
some visual highlighting on my Main form based on the total in this text box.
But because this field is updating too slow, my code is actually still
seeing the old value when I try to reference this text box. In my subform's
AfterInsert event, I call a Function that does some calculations based on the
"txtSubTotal" text box on my Main Form to highlight various labels (lblLow,
lblMed, lblHigh) on my Main Form, based on this subtotal. An example is
below:

With Me
If IsNull(.txtSubTotal) Then
.Parent![lblLow].ForeColor = 32768
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 0
Else
Select Case .txtSubTotal
Case 0 To 2
.Parent![lblLow].ForeColor = 32768
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 0
Case 3 To 5
.Parent![lblLow].ForeColor = 0
.Parent![lblMed].ForeColor = 32768
.Parent![lblHigh].ForeColor = 0
Case Else
.Parent![lblLow].ForeColor = 0
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 32768
End Select
End If
End With

The problem is, the calculation is too slow and when my code tries to
evalutate the value in txtSubTotal, it is the old value, because the field
has not had time to update quick enough. However, if I put a breakpoint on
the line where it evaluates the total, and I wait a second or two, and then
step through my code, the textbox shows the updated subtotal, because it has
had time to update.

Is there a work around to this?

 

[Jonathan Parminter]

Hi Tony,
you could try inserting the line

doevents

before the calculation. This line forces a pause for
window events to complete before 'moving' to the next line.

This line is useful before loops. It avoids for example
the situation where you alt+tab to another application
during the running of code. Then alt+tab back, only to
have the system wait till the code has completed
processing before actually returning.

Luck
Jonathan

-----Original Message-----
With Access 2000/2002, I am using a MainForm with one SubForm, where on my
Sub Form I have a text box titled "txtSubTotal" in the Form Footer, whose
ControlSource = Sum([Quantity]), so that it will calculate the total
Quantity. On my MainForm, I have a text box located below my SubForm titled
"txtSubTotal", whose ControlSource references

my "txtSubTotal" text box on my

SubForm, so that the user can see the SubTotal of the records on the SubForm.

My problem is, I am finding that this text box on my "MainForm" is
recalculating too slow, because I am using code to reference this field to do
some visual highlighting on my Main form based on the total in this text box.
But because this field is updating too slow, my code is actually still
seeing the old value when I try to reference this text box. In my subform's
AfterInsert event, I call a Function that does some calculations based on the
"txtSubTotal" text box on my Main Form to highlight various labels (lblLow,
lblMed, lblHigh) on my Main Form, based on this subtotal. An example is
below:

With Me
If IsNull(.txtSubTotal) Then
.Parent![lblLow].ForeColor = 32768
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 0
Else
Select Case .txtSubTotal
Case 0 To 2
.Parent![lblLow].ForeColor = 32768
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 0
Case 3 To 5
.Parent![lblLow].ForeColor = 0
.Parent![lblMed].ForeColor = 32768
.Parent![lblHigh].ForeColor = 0
Case Else
.Parent![lblLow].ForeColor = 0
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 32768
End Select
End If
End With

The problem is, the calculation is too slow and when my code tries to
evalutate the value in txtSubTotal, it is the old value, because the field
has not had time to update quick enough. However, if I put a breakpoint on
the line where it evaluates the total, and I wait a second or two, and then
step through my code, the textbox shows the updated subtotal, because it has
had time to update.

Is there a work around to this?
.

 

[Tony_VBACoder]

Jonathan, thanks for the suggestion, however, it did not work or solve my
problem. I even put in a Debug.Print before the DoEvents and after the
DoEvents, to see what the value would be before and after, but they were both
the same. I would have expected to see the value after the DoEvents to be
the new value. Again, the very strange thing that happens is, if I put a
breakpoint in on my Debug.Print statement after the DoEvents, and I check
what the value is, I get my updated value because of the pause in code gives
it some time to update.

Any other thoughts?

Hi Tony,
you could try inserting the line

doevents

before the calculation. This line forces a pause for
window events to complete before 'moving' to the next line.

This line is useful before loops. It avoids for example
the situation where you alt+tab to another application
during the running of code. Then alt+tab back, only to
have the system wait till the code has completed
processing before actually returning.

Luck
Jonathan

-----Original Message-----
With Access 2000/2002, I am using a MainForm with one SubForm, where on my
Sub Form I have a text box titled "txtSubTotal" in the Form Footer, whose
ControlSource = Sum([Quantity]), so that it will calculate the total
Quantity. On my MainForm, I have a text box located below my SubForm titled
"txtSubTotal", whose ControlSource references

my "txtSubTotal" text box on my

SubForm, so that the user can see the SubTotal of the records on the SubForm.

My problem is, I am finding that this text box on my "MainForm" is
recalculating too slow, because I am using code to reference this field to do
some visual highlighting on my Main form based on the total in this text box.
But because this field is updating too slow, my code is actually still
seeing the old value when I try to reference this text box. In my subform's
AfterInsert event, I call a Function that does some calculations based on the
"txtSubTotal" text box on my Main Form to highlight various labels (lblLow,
lblMed, lblHigh) on my Main Form, based on this subtotal. An example is
below:

With Me
If IsNull(.txtSubTotal) Then
.Parent![lblLow].ForeColor = 32768
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 0
Else
Select Case .txtSubTotal
Case 0 To 2
.Parent![lblLow].ForeColor = 32768
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 0
Case 3 To 5
.Parent![lblLow].ForeColor = 0
.Parent![lblMed].ForeColor = 32768
.Parent![lblHigh].ForeColor = 0
Case Else
.Parent![lblLow].ForeColor = 0
.Parent![lblMed].ForeColor = 0
.Parent![lblHigh].ForeColor = 32768
End Select
End If
End With

The problem is, the calculation is too slow and when my code tries to
evalutate the value in txtSubTotal, it is the old value, because the field
has not had time to update quick enough. However, if I put a breakpoint on
the line where it evaluates the total, and I wait a second or two, and then
step through my code, the textbox shows the updated subtotal, because it has
had time to update.

Is there a work around to this?
.