Requery problem

  • Thread starter Thread starter Al Campagna
  • Start date Start date
A

Al Campagna

Hello all,
I have a main form and a continuos record subform. In the footer of that
subform is a calculated field that sums a bound field in the subform. The
calculated field is called TotalDisbursement, and has a ControlSource of...
=Sum(NZ([ActualLengthM]))

This calculation works fine, but my problem occurs when I add a new
record with another ActualLengthM value to be added to the
TotalDisbursement.

So... I Requery the subform just before I run my "decision" code.
However, after the Requery, when I try this code...
Me.Requery
(BREAK) If TotalDisbursement > (RollLength * 1.1) Then.....
the value of TotalDisbursement = 0! I placed a BREAK in the code, and
RollLength is correct, but the TotalDisbursement
value = 0.

**If I remove the Requery, the decision code runs just fine. Of course it
operates without the "new" subform value since my NEW ActualLengthM value
doesn't get added in, BUT, at least TotalDisbursement has a value when I
BREAK on the code.

Why is the Requery causing my footer calculation to return 0 when I run
my code?

TIA
Al Campagna
Candia Computer Consulting
Candia, NH
 
Al,

I don't really know the answer to your question, except to say in
vague terms that I have previously experienced a problem related to
the time taken to Requery a subform. However, in this instance, I
wonder whether the Requery is really necessary or applicable anyway.
I would try it by explicitly saving, if necessary, the latest record
in the subform
DoCmd.RunCommand acCmdSaveRecord
and then use Me.Recalc to update the value of TotalDisbursement.

- Steve Schapel, Microsoft Access MVP
 
Thanks Steve,
I'll give ReCalc a try. I've tried Refresh and Requery to no avial...
I also found a "workaround". After the Requery, a DSum of that field in
my subform yields the correct value, but... I try to never not use any
"aggregate" D functions in my forms (performance issue). So this is bugging
me...
Seems to me (in a perfect world) Access should do it's Requery
"completely", and then attemp to determine the value I'm calling for.
Thanks for your help. I'll try to reply to this thread when I've had a
chance to try it.
--
Al Campagna
Candia Computer Consulting
Candia, NH


Steve Schapel said:
Al,

I don't really know the answer to your question, except to say in
vague terms that I have previously experienced a problem related to
the time taken to Requery a subform. However, in this instance, I
wonder whether the Requery is really necessary or applicable anyway.
I would try it by explicitly saving, if necessary, the latest record
in the subform
DoCmd.RunCommand acCmdSaveRecord
and then use Me.Recalc to update the value of TotalDisbursement.

- Steve Schapel, Microsoft Access MVP


Hello all,
I have a main form and a continuos record subform. In the footer of that
subform is a calculated field that sums a bound field in the subform. The
calculated field is called TotalDisbursement, and has a ControlSource of...
=Sum(NZ([ActualLengthM]))

This calculation works fine, but my problem occurs when I add a new
record with another ActualLengthM value to be added to the
TotalDisbursement.

So... I Requery the subform just before I run my "decision" code.
However, after the Requery, when I try this code...
Me.Requery
(BREAK) If TotalDisbursement > (RollLength * 1.1) Then.....
the value of TotalDisbursement = 0! I placed a BREAK in the code, and
RollLength is correct, but the TotalDisbursement
value = 0.

**If I remove the Requery, the decision code runs just fine. Of course it
operates without the "new" subform value since my NEW ActualLengthM value
doesn't get added in, BUT, at least TotalDisbursement has a value when I
BREAK on the code.

Why is the Requery causing my footer calculation to return 0 when I run
my code?

TIA
Al Campagna
Candia Computer Consulting
Candia, NH
Click to expand...
Click to expand...
 
Steve,
I think I found the problem. When I requery the subform, I actually call
Stephen Leban's code that prevents the subform from "jumping" up during
refresh/requery/recalc. You know... if you're on rec 10 and Refresh, Rec 10
jumps up to the top of the subform... yada yada...
I've used this code several times before, and it works very well.
However, there is a DoEvents in the code. It was the only thing in the
code that I thought might cause the calculation value problem. Once I rem'd
out the DoEvents it appears to be working fine, and I can deep-six the DSum
"workaround".
I apologize for not mentioning that in my first post, but I really didn't
see anything in Stephen's code that would cause this.
I have more testing to do, but I think that's it.
Thanks again,
--
Al Campagna
Candia Computer Consulting
Candia, NH


Steve Schapel said:
Al,

I don't really know the answer to your question, except to say in
vague terms that I have previously experienced a problem related to
the time taken to Requery a subform. However, in this instance, I
wonder whether the Requery is really necessary or applicable anyway.
I would try it by explicitly saving, if necessary, the latest record
in the subform
DoCmd.RunCommand acCmdSaveRecord
and then use Me.Recalc to update the value of TotalDisbursement.

- Steve Schapel, Microsoft Access MVP


Hello all,
I have a main form and a continuos record subform. In the footer of that
subform is a calculated field that sums a bound field in the subform. The
calculated field is called TotalDisbursement, and has a ControlSource of...
=Sum(NZ([ActualLengthM]))

This calculation works fine, but my problem occurs when I add a new
record with another ActualLengthM value to be added to the
TotalDisbursement.

So... I Requery the subform just before I run my "decision" code.
However, after the Requery, when I try this code...
Me.Requery
(BREAK) If TotalDisbursement > (RollLength * 1.1) Then.....
the value of TotalDisbursement = 0! I placed a BREAK in the code, and
RollLength is correct, but the TotalDisbursement
value = 0.

**If I remove the Requery, the decision code runs just fine. Of course it
operates without the "new" subform value since my NEW ActualLengthM value
doesn't get added in, BUT, at least TotalDisbursement has a value when I
BREAK on the code.

Why is the Requery causing my footer calculation to return 0 when I run
my code?

TIA
Al Campagna
Candia Computer Consulting
Candia, NH
Click to expand...
Click to expand...
 
Very good, Al. Glad to see that you're making progress with it.
Unless I am missing something else, I would still contend that Requery
is not applicable to the situation :-)

- Steve Schapel, Microsoft Access MVP
 
Steve,
I agree. I'm going to have to bone up on the differences between
Refresh, Requery, and Recalc. I've never been able to tell the difference.
But, I found this in DoEvents Help....

Caution Any time you temporarily yield the processor within an event
procedure, make sure the procedure is not executed again from a different
part of your code before the first call returns; this could cause
unpredictable results. In addition, do not use DoEvents if other
applications could possibly interact with your procedure in unforeseen ways
during the time you have yielded control. (sounds like my situation)

And also, I'm going to have to keep an eye on Stephen's code... now that
I've removed the DoEvents. I'm sure he put it in there for a reason
(api/gui/windows calls?)

Anyway... thanks again.

Al Campagna
Candia Computer Consulting
Candia, NH
 
Back
Top