subreport displays only once

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to run a report with a subreport that was created in access 2000.
It used to run just fine. The subform now only displays once (for the first
value of the linked master/child field) instead of for each value in the
linked master/child field. What is contained in that subreport is correct
but where did the other ones go?

I tried to run other reports that were similar to this one for comparison
purposes, but those reports did the same thing. I am currently using Access
2003.

What is different now? Is it a matter of a property setting in the subform?

Thank you in advance for your assistance.

Bryan
 
Open the main report in design view.
Right-click the edge of the subreport control, and choose Properties.
On the Data tab of the Properties box, check these properties:
LinkChildFields
LinkMasterFields

Chances are the wrong fields are named there, or there are too many field
names there, so the result is too restrictive.
 
Thanks Allen,

There is only one item for each of the Master and Child fields. I have
created and run this and many other reports like it for years with no
problems. There must be something else that changed... and I think it has to
do with Access 2003. This report has not been altered in any way since it
worked. It last worked in Access XP.
 
I experienced the same problem and ended up getting a hotfix from Microsoft,
which solved it. I believe I reported it in a previous posting in this
newsgroup, but I've been unable to find it after an hour of searching. I will
keep looking.

Bruce
 
Okay, if the query is returning all the right results, and the
LinkMasterFields/LinkChildFields is being applied correctly, there must be
some other issue at work here.

Examples:
1. Access is confused (corrupted) about what these names refer to, and Name
AutoCorrect is the most likely culprit. Details:
http://allenbrowne.com/bug-03.html

2. The LinkMasterFields/LinkChildFields involve an unbound field or a field
bound to an expression or a calculated query field, and Access (JET) is
misunderstanding the data types. Details:
http://allenbrowne.com/ser-45.html

3. One of the fields is of type Decimal, which has heaps of issues, e.g.:
http://support.microsoft.com/default.aspx?scid=kb;en-us;319547
http://support.microsoft.com/default.aspx?scid=kb;en-us;837148
http://support.microsoft.com/default.aspx?scid=kb;en-us;243467

4. The data selection involves some reference to the subreport, and the
reference omits the ".Report" bit. This was sometimes acceptable in previous
versions, but A2003 seems to be more anal about it.
 
FOUND IT!

Here's the link to the Microsoft service bulletin

http://support.microsoft.com/default.aspx?scid=kb;en-us;889186

One of the problems it addresses is:

• When you use a form as a subreport in an Access 2003 report, all pages of
the subreport after page 1 are blank. This problem occurs after you install
Microsoft Office 2003 Service Pack 1 (SP1).

I emailed Tech Support for the hotfix and applied it. The problem went away.

Bruce
 
Allen,

I have been on the telephone with Microsoft all morning... You can easily
duplicate the problem that I am having by opening the Northwind database...

You will basically re-create the orders form as a report...

Start a new report and base it on the Orders Query. Drag all fields from
the field list into the page header. Click on the subform/subreport button
on the toolbar and make a rectangle in the detail section of the report to
contain the subreport. Click on the Orders Fubform (form) to base the
subform on. Accept the default Master/child relationship. Click Finish.
Run the report... You will see that the subform only shows up once. You can
scroll through the pages and you will see that the data that the report is
based on is there... But no subform... This has to be a Microsoft problem.

Is there a workaround?
 
Bryan, thanks for posting details.

You say, " ... drag all fields ... into the Page Header ..."
So you have the LinkMasterFields in Page Header???

The Page Header is a page-oriented section, not a data-oriented section. Its
events fire for the page, and the data is not reliable.

Use the Sortin'n'Grouping dialog to create an OrderId header section. Move
the controls into this data-centric section. Does this solve the problem?
 
Allen,

"LinkMasterFields" is not a field in Orders Query... If you drag all fields
into the page header, you are basically forcing all fields in the Orders
Query to be visible. You can run this first without the subreport in the
"Details" section and see that the info contained in the Orders Query.
 
If you right-click the subreport (in design view), and choose Properties, on
the Data tab of the Properties box you will see properties named:
LinkMasterFields
LinkChildFields

The LinkMasterFields contains the name of the field or fields from the main
report that the subreport is being filtered on.

If that field/those fields are in the Page Header section, the report will
not work properly. Move them into a Group Header instead of the Page Header.
You can create a group header on (say) OrderId by using the Sorting And
Grouping dialog (View menu.)

I can guarantee you that using the Page Header will yield unreliable results
in many reports in Access, because it is not a data-centric event and so is
subject to whatever advancing and retreating Access must perform to fit the
data onto the page.
 
Thank you, Allen.

Actually, through the example that I posted previously, Microsoft was able
to duplicate the problem at their end. At the end of the day, they emailed a
patch to me to fix the problem. Once I applied the patch, my reports ran
perfectly, as they had previously. See MS Knowledgebase article number
889186 for further details.

I do appreciate your efforts to help.
 
Thank you for your assistance.

It seems that I went through the same steps that you did. I walked the
technicians at Microsoft through the problem so that they could duplicate it
at their end. They told me that they would work on it for a while and get
back with me. I then received the hotfix at the end of the day. When I
applied the hotfix, the problem went away. My reports ran perfectly, just as
they did previously.
 
Back
Top