Subform Calculations

  • Thread starter Thread starter Al Camp
  • Start date Start date
A

Al Camp

Given a Main form and continuous Subform... I have a calculated field
(TotalLength) in the footer of the Subform that correctly adds up a total
for all the Subform records. That works fine.

However, on the OnCurrent for the Subform, I want to examine that
TotalLength value and make a decision via code... as I move from record to
record on the MAIN form via my navigation buttons.

As I "browse" the Main records, the subform OnCurrent event occurs, but
there is no value returned from the footer calculation. I know OnCurrent
fires, because I put a Beep in the code.

***At this point, I'm not adding any subform records at all, just navigating
between my Main form records.

When the Subform OnCurrent fires, why can't it return the calculated value?

Thanks for any help... I've been battling this problem for a while...

Al Camp
 
Al,

Why not take the opposite approach and check the value of
the subform footer control from the OnCurrent of the Main
form? Seems like it would be a lot simpler approach as that
is the time that you want to make your decision.

Gary Miller
Sisters, OR
 
Gary,
Originally I went with that, but on "Main form OnCurrent" time, the
TotalLength calculation from the Subform was not available. No matter
what I tried, I couldn't get that value. I even used the Build function in
the MAIN module to call the value... using every addressing scheme I know
of...

Forms!frmMain!frmSubForm.Form!TotalLength
or
Me![frmSubform].Form![TotalLength] etc etc...

The value just wasn't there. So... since the calculation comes from the
subform, I'm trying to accomplish the task from within there. Believe me,
I've been round and round on this one... there's something I'm missing
here... either when initiating the code from the Main or the Subform.

Thanks very much for your help,
Al Camp
 
Al,

Did some playing around with this one and I can understand
why you have been going in circles. I placed a text box that
had the equivalent of "=Me![frmSubform].Form![TotalLength]"
for the controlsource. It picked up the data fine, but it
was real interesting when.

On the OnCurrent event it was empty, so I tried the GotFocus
of the first control in the tab order. Still empty so
neither event would pick up the value. Apparently it fills
in sometime after the first control's setfocus so you
wouldn't have an automatic trigger for what you want to do
going on to a different record unless maybe you setfocus to
a hidden control that could provide a trigger. Didn't try
that.

What I did try was to use DSum() on the OnCurrent event of
the form to check the sum of the details table and that
worked like a charm. The following references my controls
and tables of course...

MsgBox DSum("[Amount Billed]", "[Work Log]", "[InvoiceID] =
" & Me!InvoiceID)

Not the way you wanted to go, but should give you the same
result.

Gary Miller


Al Camp said:
Gary,
Originally I went with that, but on "Main form OnCurrent" time, the
TotalLength calculation from the Subform was not available. No matter
what I tried, I couldn't get that value. I even used the Build function in
the MAIN module to call the value... using every addressing scheme I know
of...

Forms!frmMain!frmSubForm.Form!TotalLength
or
Me![frmSubform].Form![TotalLength] etc etc...

The value just wasn't there. So... since the calculation comes from the
subform, I'm trying to accomplish the task from within there. Believe me,
I've been round and round on this one... there's something I'm missing
here... either when initiating the code from the Main or the Subform.

Thanks very much for your help,
Al Camp


Al,

Why not take the opposite approach and check the value of
the subform footer control from the OnCurrent of the Main
form? Seems like it would be a lot simpler approach as that
is the time that you want to make your decision.

Gary Miller
Sisters, OR

correctly
adds up a total OnCurrent
event occurs, but calculation. I
know OnCurrent at
all, just navigating the
calculated value? for
a while...
Click to expand...
Click to expand...
 
Exactly Gary!
It sounds as though you're seeing just what I'm seeing. I've tried
SetFocus, GotoControl, etc.. etc..

It appears as though even though the Main form is Current, the subform
hasn't "put it's pants on yet."

Yes, I did find that DSum will find the value, but I hate like hell to
use a DSum. I apologize for not mentioning it.
I've done every kind of "workaround" possible in my years of Access
proramming... but, I think it's time I really understand the nuts and bolts
of what happens and when... and this is a good example of that.

Someone must have run into this situation before. There's got to be a
way to address the Subform calculated value at Main form OnCurrent time, so
that it can be operated upon.

Thanks for hanging in there Gary. I may try a repost if this thread
peters out.
Al Camp

Gary Miller said:
Al,

Did some playing around with this one and I can understand
why you have been going in circles. I placed a text box that
had the equivalent of "=Me![frmSubform].Form![TotalLength]"
for the controlsource. It picked up the data fine, but it
was real interesting when.

On the OnCurrent event it was empty, so I tried the GotFocus
of the first control in the tab order. Still empty so
neither event would pick up the value. Apparently it fills
in sometime after the first control's setfocus so you
wouldn't have an automatic trigger for what you want to do
going on to a different record unless maybe you setfocus to
a hidden control that could provide a trigger. Didn't try
that.

What I did try was to use DSum() on the OnCurrent event of
the form to check the sum of the details table and that
worked like a charm. The following references my controls
and tables of course...

MsgBox DSum("[Amount Billed]", "[Work Log]", "[InvoiceID] =
" & Me!InvoiceID)

Not the way you wanted to go, but should give you the same
result.

Gary Miller


Al Camp said:
Gary,
Originally I went with that, but on "Main form OnCurrent" time, the
TotalLength calculation from the Subform was not available. No matter
what I tried, I couldn't get that value. I even used the Build function in
the MAIN module to call the value... using every addressing scheme I know
of...

Forms!frmMain!frmSubForm.Form!TotalLength
or
Me![frmSubform].Form![TotalLength] etc etc...

The value just wasn't there. So... since the calculation comes from the
subform, I'm trying to accomplish the task from within there. Believe me,
I've been round and round on this one... there's something I'm missing
here... either when initiating the code from the Main or the Subform.

Thanks very much for your help,
Al Camp


Al,

Why not take the opposite approach and check the value of
the subform footer control from the OnCurrent of the Main
form? Seems like it would be a lot simpler approach as that
is the time that you want to make your decision.

Gary Miller
Sisters, OR

Given a Main form and continuous Subform... I have a
calculated field
(TotalLength) in the footer of the Subform that correctly
adds up a total
for all the Subform records. That works fine.

However, on the OnCurrent for the Subform, I want to
examine that
TotalLength value and make a decision via code... as I
move from record to
record on the MAIN form via my navigation buttons.

As I "browse" the Main records, the subform OnCurrent
event occurs, but
there is no value returned from the footer calculation. I
know OnCurrent
fires, because I put a Beep in the code.

***At this point, I'm not adding any subform records at
all, just navigating
between my Main form records.

When the Subform OnCurrent fires, why can't it return the
calculated value?

Thanks for any help... I've been battling this problem for
a while...

Al Camp
Click to expand...
Click to expand...
Click to expand...
 
Al said:
Given a Main form and continuous Subform... I have a calculated field
(TotalLength) in the footer of the Subform that correctly adds up a total
for all the Subform records. That works fine.

However, on the OnCurrent for the Subform, I want to examine that
TotalLength value and make a decision via code... as I move from record to
record on the MAIN form via my navigation buttons.

As I "browse" the Main records, the subform OnCurrent event occurs, but
there is no value returned from the footer calculation. I know OnCurrent
fires, because I put a Beep in the code.

***At this point, I'm not adding any subform records at all, just navigating
between my Main form records.

When the Subform OnCurrent fires, why can't it return the calculated value?
Click to expand...

I believe that the reason for this effect is because the
text box calculation is performed asynchronously (at a lower
priority than the events (and probably the form painting).
This means that you can not rely on any code being able to
pick up a calculated control's value.

To deal with this situation, instead of of using an
expression in the text box, I use code (in the Current event
and necessary AfterUpdate events) to calculate the
expression and store the result in the text box. This way,
you get to control when the calculation is done.
 
It sounds as though you're seeing just what I'm seeing. I've tried
SetFocus, GotoControl, etc.. etc..

It appears as though even though the Main form is Current, the subform
hasn't "put it's pants on yet."

Yes, I did find that DSum will find the value, but I hate like hell to
use a DSum. I apologize for not mentioning it.
I've done every kind of "workaround" possible in my years of Access
proramming... but, I think it's time I really understand the nuts and bolts
of what happens and when... and this is a good example of that.

Someone must have run into this situation before. There's got to be a
way to address the Subform calculated value at Main form OnCurrent time, so
that it can be operated upon.
Click to expand...

Hi Al. This is a timing issue. The only reliable, and speedy, workaround that I
have found is to cycle through the subform's RecordsetClone and sum up the
pertinent field's values (example written in Northwind's "Orders" form):

'***
'Sum up subform's Extended Price field in "Current" event
Dim curValue As Currency
With Me.Orders_Subform.Form.RecordsetClone
If .RecordCount Then 'If records exist
.MoveFirst 'Resets position
Do While Not .EOF
curValue = curValue + !ExtendedPrice
.MoveNext
Loop
End If
End With
'Display the sum
MsgBox curValue
'***
 
Marshall,
You wrote...
To deal with this situation, instead of of using an
expression in the text box, I use code (in the Current event
and necessary AfterUpdate events) to calculate the
expression and store the result in the text box. This way,
you get to control when the calculation is done.
Click to expand...

I take it you mean a "bound" text control in the subform... so every
subform record would be carrying it's total in it's own field?
If so, this would seem to qualify for that perrenial post "How do I save
a calculation to a table."

I'll definitely give this a shot, and try to get back to you and Gary.

Thanks a lot,
Al Camp
 
To deal with this situation, instead of of using an
I take it you mean a "bound" text control in the subform... so every
subform record would be carrying it's total in it's own field?
If so, this would seem to qualify for that perrenial post "How do I save
a calculation to a table."
Click to expand...

I think that you'll find that the textbox that you were using as your calculated
control will work just fine if you leave its control source empty.
 
Exciting seeing so many qualified folks jumping in now to
the converstation.

I agree with you about not wanting to take the Domain
aggregate performance hit of the DSum(), although for the
number of records I tested it on it was very minimal.

If this is the route that you decide on going with, I would
suggest emulating the same lookup using a filtered
Recordset which should be much faster than the DSum(). Call
a recordset where the SQL criteria is similar to our DSum
criteria.

Glad that you are off and running.

Gary Miller

Al Camp said:
Exactly Gary!
It sounds as though you're seeing just what I'm seeing. I've tried
SetFocus, GotoControl, etc.. etc..

It appears as though even though the Main form is Current, the subform
hasn't "put it's pants on yet."

Yes, I did find that DSum will find the value, but I hate like hell to
use a DSum. I apologize for not mentioning it.
I've done every kind of "workaround" possible in my years of Access
proramming... but, I think it's time I really understand the nuts and bolts
of what happens and when... and this is a good example of that.

Someone must have run into this situation before. There's got to be a
way to address the Subform calculated value at Main form OnCurrent time, so
that it can be operated upon.

Thanks for hanging in there Gary. I may try a repost if this thread
peters out.
Al Camp

Al,

Did some playing around with this one and I can understand
why you have been going in circles. I placed a text box that
had the equivalent of "=Me![frmSubform].Form![TotalLength]"
for the controlsource. It picked up the data fine, but it
was real interesting when.

On the OnCurrent event it was empty, so I tried the GotFocus
of the first control in the tab order. Still empty so
neither event would pick up the value. Apparently it fills
in sometime after the first control's setfocus so you
wouldn't have an automatic trigger for what you want to do
going on to a different record unless maybe you setfocus to
a hidden control that could provide a trigger. Didn't try
that.

What I did try was to use DSum() on the OnCurrent event of
the form to check the sum of the details table and that
worked like a charm. The following references my controls
and tables of course...

MsgBox DSum("[Amount Billed]", "[Work Log]", "[InvoiceID] =
" & Me!InvoiceID)

Not the way you wanted to go, but should give you the same
result.

Gary Miller


Al Camp said:
Gary,
Originally I went with that, but on "Main form OnCurrent" time, the
TotalLength calculation from the Subform was not available. No matter
what I tried, I couldn't get that value. I even used
Click to expand...
the
Build function in
the MAIN module to call the value... using every addressing scheme I know
of...

Forms!frmMain!frmSubForm.Form!TotalLength
or
Me![frmSubform].Form![TotalLength] etc etc...

The value just wasn't there. So... since the calculation comes from the
subform, I'm trying to accomplish the task from within there. Believe me,
I've been round and round on this one... there's
Click to expand...
something
I'm missing
here... either when initiating the code from the Main
Click to expand...
or
the Subform.
Thanks very much for your help,
Al Camp


Al,

Why not take the opposite approach and check the
Click to expand...
value
of
the subform footer control from the OnCurrent of the Main
form? Seems like it would be a lot simpler approach
Click to expand...
as
that
is the time that you want to make your decision.

Gary Miller
Sisters, OR

Given a Main form and continuous Subform... I have a
calculated field
(TotalLength) in the footer of the Subform that correctly
adds up a total
for all the Subform records. That works fine.

However, on the OnCurrent for the Subform, I want to
examine that
TotalLength value and make a decision via code... as I
move from record to
record on the MAIN form via my navigation buttons.

As I "browse" the Main records, the subform OnCurrent
event occurs, but
there is no value returned from the footer calculation. I
know OnCurrent
fires, because I put a Beep in the code.

***At this point, I'm not adding any subform
Click to expand...
records
at
all, just navigating
between my Main form records.

When the Subform OnCurrent fires, why can't it
Click to expand...
return
the
calculated value?

Thanks for any help... I've been battling this
Click to expand...
problem
for
a while...

Al Camp
Click to expand...
Click to expand...
Click to expand...
 
Gary, Bruce, Marshall...
Excellent suggestions.
I tried Marshall's method of updating an unbound text control, and that
works well. I'm using a DSum to populate the text control.
So... I also want to give Bruce's recordset clone method a shot. I take
it that the Recordset clone method is just another form of a Sum, as it
doesn't address the whole table, just the subform recordset... so I'm hoping
that will be pretty fast. The Subform "table" is pretty big, but the
Subform Recordset should only be 10-20 records.
This NG is a great resource!
Thanks to all,
Al Camp
 
Back
Top