SubReport Error

[Kim]

I develop and test in Access 2002 But have an issue that occurs only on SOME
computers using Access 2003.
I have a Report with a sub report on the report header. In the On Format
event of the report header I check for data in the sub report by checking on
Text box for data. If there is none I set the subreport visible property
false.
The problem is on some computers I get an "invalid reference to a
form/report" when I try to reference this field. This is interpreted as a
false and the sub report is set invisible even though there is data.
This code has been working fine for years, all the way back to Access 97.
I thought it was a version bug - It worked OK on machines using Access
11.5xx.xxx but not 11.6xx.xxx. But a 'helper' upgrded the the 11.5s to 11.6
and they still worked OK.
Is this a known issue? As I said this has worked fine for years. I do it
this way and in the on format rather than on print becuase we have some users
with a lot of data and we are doing a LOT of lookups in on format and I do
not want this event firing more than once. The report is slow enough already
as it is gathering data from up to 20 tables many with record counts in the
100000+ range with joins to equally large tables.

Thanks for your help.
Kim

 

[SA]

Kim:

You mention that you have the code in the On Format event because you don't
want the event firing many times. Actually, using On Format code WILL cause
the event to potentially fire many many times more than On Print (which
fires once). Normally we recommend that any code in the On Format event be
wrapped in code like this:

If FormatCount = 1 Then
'....Your code here
End if

To keep On Format code runing only once for each time a section is
formatted, or move code like you are discussing to the On Print Event which
fires after the On Format event.

Secondly, you indicate that your code references a control on the sub
report. You should rather use the .HasData property of the subreport to
determine if any records are return as in:

If Me!MySubreport.Report.HasData = False Then
Me!MySubreport.Visible = False
Else
Me!MySubreport = True
End if

This would stop referencing a specific control which may or may not be
populated and referenceable.

Last, this appears to be a timing issue mostly, which can be a result of how
fast the data is pulled accross the network to populate the subreport. So
to ease this, a.) make sure that fields that the sub-report's data selection
is based on are indexed appropriately and b.) minimize data pulled by
eliminating extraneous fields and looking at the underlying query of the sub
to possibly restrict the actual pull (i.e. adding where criteria to the
query as opposed to just using the link master / link child properties of
the sub report which results in pulling all the data accross the network and
filtering it when its displayed on the report.

Hope that helps