Report w/subreport; Subreport not displaying related records

[Leighton.d]

I have created a very simple report with one subreport to experiment with.
The main report contains student demographic data, such as ID, name,
gradelevel, etc. The subreport contains assessment test scores. The common
field between the two is a field named "StudentID", which is a GUID (this
data is imported from a SQL server database).

In the properties of the subreport, the "Source object" is set to the name
of the subreport. The "Link Child Fields" and "Link Master Fields" are both
set to "StudentId". When I run the report, no data is displayed in the
subreport.

I can open the subreport by itself and it shows the data. I can link the
two tables together that are used for the master report and the subreport in
a query, and it pulls the data together with no problems. If I clear the two
"link" fields in the properties of the subreport, it displays all records in
the subreport for every student.

I appear to be doing everything properly, yet I'm unable to get the
master/subreport combo to work. I've tried adding the subreport manually,
and I've also tried adding it via the wizard.

Any ideas?

Thanks!

 

[Leighton.d]

I failed to mention that I'm running Access 2003 w/SP2...

 

[Leighton.d]

Solved it!

The fields I used to link the report to the subreport on were "Replication
Id" fields, which I use to store the GUID field that's imported from the SQL
Server table. When I switched to using the "StudentNumber" field, which is a
text field that contains the District assigned student number for each
student, it started working properly.

I also discovered that the labels in the page header of the subreport do not
display when part of a master report. I moved the column headings to the
"report header" of the subreport, and now they display just fine.

Gee! I'm more efficient that I realized...

 

[Larry Linson]

I failed to mention that I'm running Access 2003 w/SP2...

I haven't had any problems with LinkMasterFields and LinkChildFields not
working in Access 2003, even before SP2. Are the two fields named
identically in the RecordSource for the main Report and the Report embedded
in the Subreport Control? To be just a little pedantic, strictly speaking,
there's no such Object in Access as a "Subreport" or a "Subform" -- just a
"Subreport Control" and a "Subform Control" into which you can embed a
Report, Form, or Datasheet View -- but that is beside the point here; that's
not your problem.

You don't have Criteria in the RecordSource for the Report embedded in the
Subreport Control, do you? (You should not, as that will "prefilter" the
data and interfere with the working of the LinkMasterFields and
LinkChildFields.)

Larry Linson
Microsoft Access MVP

 

[Leighton.d]

There are no criteria or filters set for each the master or the subform
"control" ;-). In the record source for both the master and subform, there
are two fields named "StudentNumber" and "StudentId". StudentNumber is the
district assigned 9 digit value assigned to each student when they enter the
district, and the StudentId is a GUID generated by the database when the
record is created within the SQL server database. When I link on the
StudentId GUID, no records are displayed in the subreport. When I link on
the StudentNumber fields, it works just fine.

I've occasionally run into weird quirky things like this in Access, and just
chocked this up as another occurance, solved my problem, and moved on.

Thanks...