Is there a better way?

[LAS]

I have a report where I must have the results of a function for use 5 times
in each of my three groups. The function is fncTotalSchoolTime. The
function executes a DLookup and work through dozens, sometimes hundreds of
rows. It does not use the same tables that are in the report's record
source. Is there a way I can execute the function just once in each report
group and store it in a variable for use in the 5 places? I thought about
storing the results in a text box, but how can I count on the text box's
being populated before these three other controls are populated? Here are
the three control sources that use the function.


=fncMinutesFormatted(fncTotalSchoolTime("class",[class_code],"",""),True)
=IIf(fncTotalSchoolTime("class",[Class_Code],"","")=0,0,FormatPercent(Sum([Reint])/fncTotalSchoolTime("class",[Class_Code],"",""),0))
=IIf(fncTotalSchoolTime("class",[Class_Code],"","")=0,0,FormatPercent(Sum([Excl])/fncTotalSchoolTime("class",[Class_Code],"",""),0))

 

[John Spencer]

You could make the three controls unbound and then use VBA function to
populate them. Use the format event of the relevant section.

Assumptions: The five controls are all in the same section of the report. For
instance they are all in the Group Section.
The function - fncTotalSchoolTime - always returns a number value.

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
Dim X as Double

X = fncTotalSchoolTime("class",[class_code],"","")
Me.SomeControl =fncMinutesFormatted(x,True)
If X = 0 Then
Me.SomeOtherControl = 0
Me.AnotherControl = 0
Else
Me.SomeOtherControl = FormatPercent(Me.txtSumReint/X)
Me.AnotherControl = FormatPercent(Me.TxtSumExcl/X)
End IF

End Sub

You should have two controls on your form in the section that will calculate
the Sum of Reint and Excl. The controls could be invisible.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

I have a report where I must have the results of a function for
use 5 times in each of my three groups. The function is
fncTotalSchoolTime. The function executes a DLookup and work
through dozens, sometimes hundreds of rows. It does not use the
same tables that are in the report's record source. Is there a
way I can execute the function just once in each report group and
store it in a variable for use in the 5 places?

You don't need to worry about this. The Access expression service
will do it for you. If the expression is the same in all 5 uses, the
expression service will calculate it once and use the result in all
5 places.

Now, if the expression uses data from a row of the recordsource of
the report, it will be calculated for each row, but if all 5
instances use the same input date, it will still be calculated only
once for each row.

In short, you don't have to worry about this -- Access already takes
care of it. Indeed, by trying to optimize it yourself, you'd likely
subvert the expression service's ability to realize that it's the
same expression.

 

[LAS]

This is truly elegant! Thanks so much. I feel much relieved.

It worked as written (except for control names) as soon as I figured out
that the format event doesn't trigger in Report view, only in Print Preview
view.

You could make the three controls unbound and then use VBA function to
populate them. Use the format event of the relevant section.

Assumptions: The five controls are all in the same section of the report.
For instance they are all in the Group Section.
The function - fncTotalSchoolTime - always returns a number value.

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
Dim X as Double

X = fncTotalSchoolTime("class",[class_code],"","")
Me.SomeControl =fncMinutesFormatted(x,True)
If X = 0 Then
Me.SomeOtherControl = 0
Me.AnotherControl = 0
Else
Me.SomeOtherControl = FormatPercent(Me.txtSumReint/X)
Me.AnotherControl = FormatPercent(Me.TxtSumExcl/X)
End IF

End Sub

You should have two controls on your form in the section that will
calculate the Sum of Reint and Excl. The controls could be invisible.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a report where I must have the results of a function for use 5
times in each of my three groups. The function is fncTotalSchoolTime.
The function executes a DLookup and work through dozens, sometimes
hundreds of rows. It does not use the same tables that are in the
report's record source. Is there a way I can execute the function just
once in each report group and store it in a variable for use in the 5
places? I thought about storing the results in a text box, but how can I
count on the text box's being populated before these three other controls
are populated? Here are the three control sources that use the function.


=fncMinutesFormatted(fncTotalSchoolTime("class",[class_code],"",""),True)
=IIf(fncTotalSchoolTime("class",[Class_Code],"","")=0,0,FormatPercent(Sum([Reint])/fncTotalSchoolTime("class",[Class_Code],"",""),0))
=IIf(fncTotalSchoolTime("class",[Class_Code],"","")=0,0,FormatPercent(Sum([Excl])/fncTotalSchoolTime("class",[Class_Code],"",""),0))

 

[LAS]

Oh! Interesting!

You don't need to worry about this. The Access expression service
will do it for you. If the expression is the same in all 5 uses, the
expression service will calculate it once and use the result in all
5 places.

Now, if the expression uses data from a row of the recordsource of
the report, it will be calculated for each row, but if all 5
instances use the same input date, it will still be calculated only
once for each row.

In short, you don't have to worry about this -- Access already takes
care of it. Indeed, by trying to optimize it yourself, you'd likely
subvert the expression service's ability to realize that it's the
same expression.