Show last record in subreport based on ID

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I have a report with multiple subreports. The report lists
various projects and the subreports show details relevant
to each project. The subreports are linked to the main
report by ProjectID.

One subreport is based on a query designed to show only
the last record based on the value of ProjectID on the
main report. (The query also uses a LEFT JOIN so that if
the subreport data is null the subreport (with its labels)
will still appear.)

The SQL is:

SELECT TOP 1 tblProjects.ProjectID, tblIRB.Submission
FROM tblProjects LEFT JOIN tblIRB
ON tblProjects.ProjectID = tblIRB.ProjectID
WHERE (((tblProjects.ProjectID)=[Reports]![subrptPrepIRB]!
[ProjectID]))
ORDER BY tblIRB.Submission DESC;

My problem is that when I open the main report, I am
prompted for the value of ProjectID on the subreport:

Enter Parameter Value:

Reports!subrptPrepIRB!ProjectID

Perhaps what I need to do is add code to the On Open event
of the main report (or the On Print event of the Detail
section?) to populate the ProjectID value in the
subreport. If so, any idea how to go about doing this?

Kurt
 
kurt,
you need to link the child and master fields to the projectid of the main
and subreport.
-rob
 
All of the subreports are linked to the main report by
ProjectID.
-----Original Message-----
kurt,
you need to link the child and master fields to the projectid of the main
and subreport.
-rob
I have a report with multiple subreports. The report lists
various projects and the subreports show details relevant
to each project. The subreports are linked to the main
report by ProjectID.

One subreport is based on a query designed to show only
the last record based on the value of ProjectID on the
main report. (The query also uses a LEFT JOIN so that if
the subreport data is null the subreport (with its labels)
will still appear.)

The SQL is:

SELECT TOP 1 tblProjects.ProjectID, tblIRB.Submission
FROM tblProjects LEFT JOIN tblIRB
ON tblProjects.ProjectID = tblIRB.ProjectID
WHERE (((tblProjects.ProjectID)=[Reports]! [subrptPrepIRB]!
[ProjectID]))
ORDER BY tblIRB.Submission DESC;

My problem is that when I open the main report, I am
prompted for the value of ProjectID on the subreport:

Enter Parameter Value:

Reports!subrptPrepIRB!ProjectID

Perhaps what I need to do is add code to the On Open event
of the main report (or the On Print event of the Detail
section?) to populate the ProjectID value in the
subreport. If so, any idea how to go about doing this?

Kurt
Click to expand...


.
Click to expand...
 
Back
Top