Subreports Not Updating in Detail Section

[Guest]

I have a report with a number of subreports located in the detail section.
Each of the subreports represent the results from various queries that all
tie to the same Customer ID. The Customer ID is found in a properly defined
public variable. The value of the variable is changed through a function in
a group header On Format event.

Here's the problem: The function that updates the variable is functioning
properly (i.e., places the new customer ID in the varialbe when the customer
changes). Each query that is basis for a subreport is able to access the new
global variable value and is functioning properly, independent of the report.
Each of the subreports is functioning properly, independent of the "host"
report. However, the subreports are not refreshing/requerying when viewed in
the "host" report. Instead, all of the subreports show the results of the
various queries for the last Customer ID that was placed in the global
variable the last time the report was run. So, instead of updating the
subreports for each client, I end up with the same incorrect data for all
Customers.

Any ideas as to why the subreports are not updating despite each component
(update global variable, source query, source subreport) is properly
functioning?

Thanks in advance for your time!

 

[Allen Browne]

Queries and reports cannot read VBA variables directly, so it makes sense to
use a function call to get the value of the Customer ID. Sounds like there
is some kind of timing problem with the way this value is retrieved.

You might work around that issue by using the
LinkMasterFields/LinkChildFields of the each subreport, instead of having
the function call in the queries of the subreport.

If you already have a text box on the main report that contains the Customer
ID value, just include the name of that text box in the subreport's
LinkMasterFields property, and the name of the matching field in the
subreport into its LinkChildFields.

If the main report does not have the CustomerID value, you could add a text
box with control source of:
=MyFunc()
so that it makes the function call at the time it is needed. You still use
the name of the text box in LinkMasterFields, and the timing issue is
resolved. (The function call is omitted from the queries.)

 

[Guest]

Thanks for taking the time to reply, Allen. I may end up using the
Master/Child technique to resolve the problem.

The thing I can't understand is that the report was working fine prior to a
memory error (resulting from a minor top margin change that threw the whole
layout of the report off and generated a lot of blank pages => excess memory
usage) that caused Acess to crash. I'm thinking that maybe the main report
was somehow corrupted in a way that hasn't been repaired using the normal
methods.

Anyway, thanks for this and your other contributions to the forums. I've
found a number of the ideas on your website, and your postings, to be helpful.

 

[Guest]

After reading the article that is linked below, I tried updating a text box
on a form that is open but made not visible. After changing the criteria in
the various queries to reference the text box on the hidden form, the report
functioned as desired.

I have no idea why this works but updating a global variable that was
accessed by a simple function as a query criteria did not. Anyway, in case
others encounter the same problem, please try the ideas expressed in this
document:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart03/html/sa03k8.asp