We help IT Professionals succeed at work.

Access 2007 Report with subreports not working correctly

schmir1
schmir1 asked
on
1,059 Views
Last Modified: 2013-11-28
I've got a report with two subreport that I put into the Page Footer of the main report.  Problem is when these subreports are blank (sometimes there is no subreport data) then a blank page is added for each subreport.  Is there any way to get rid of these blank pages?  Also, the second subreport (Commitment Report) only lists one report even if there are more.

I've attached the mdb that contains the report.  Open the form Issued_Traveler_Form and press the Print button in the upper right.  This runs my report (Issued_Traveler).  I think this will show my problems.  

Note: You can change the ID's around to make the subreports come up empty.

DTLDB-JustITReport.zip
Comment
Watch Question

You have to set the "CanShrink"-Events of all section (footer, subreport) to true.

The second subreport (Commitment Report) is linked throught the "Commitment_ID", so there is always just one report shown (this is the primary key of your table). If you want to show all, remove the linked fields to your sub.

Author

Commented:
The "CanShrink" works great.  Thanks.

On the problem of the Commitment Report only showing one report:
I tried removing the "Commitment_ID" from the Link Master and Link Child fields but then the Commitment Subreport show every commitment in the system (in my db there are thousands).  The Commitment Report links differently then the MRB report.  In the case of the MRB, the linking field (Issued_Traveler_ID) is contained in the parent and the subreport.  In the case of the Commitment Report, the link is through the link table (IT_Commit_Links) since the Commitment doesn't contain any direct links to the Issued Traveler.  I don't think I've got this query set up right.
From your last comment, I asume that you have to include the link-table in your sub-report query to get all records to the issued traveller (all commitment_ids out of the link table) and set the report link fields to the taveller id, but I didn't checked this at the moment. Let me know if you need further help on this.

Author

Commented:
This is the only query that I used in the report and it is used on the main report's Record Source.  Don't know how to make one work on the SubReport?  

Note: I filter the report in the code:
  DoCmd.OpenReport stDocName, acViewPreview, , "[Issued_Traveler].[Issued_Traveler_ID] = " & Me.txtIssuedTravelerID

SELECT Issued_Traveler.*, Issued_Traveler_Steps.*, IT_Commit_Links.Commitment_ID
FROM (Issued_Traveler INNER JOIN Issued_Traveler_Steps ON Issued_Traveler.Issued_Traveler_ID = Issued_Traveler_Steps.Issued_Traveler_ID) LEFT JOIN IT_Commit_Links ON Issued_Traveler.Issued_Traveler_ID = IT_Commit_Links.Issued_Traveler_ID;

Open in new window

The second sub report should show all commitments if you link it to the master through the traveler id. But I cannot look at your database to verify at the moment. I check it tomorrow.
OK, here we go:

Change the RecordSource of your commitment sub-report to the following query:

SELECT IT_Commit_Links.Issued_Traveler_ID, Commitment.*
FROM IT_Commit_Links INNER JOIN Commitment ON IT_Commit_Links.Commitment_ID = Commitment.Commitment_ID;


Change the LinkChildFields/LinkMasterFields of this sub-report to

Issued_Traveler_ID

So all commitments (actual two in your database) shown for traveler 1. Is this what you're looking for?

Author

Commented:
That is what I wanted.  Unfortunately, I get a Enter Parameter Value box which asks for the Issued_Traveler_ID?
Yes, you have to put the Issued_Traveler_ID field to your subreport (take it out of the fieldlist and name it same, Issued_Traveler_ID)

Author

Commented:
What I'm saying is that I got an error which ask me to define Issued_Traveler_ID?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks you very much.  It was a struggle but I finally got it.  I was using the Subreport Field Linker wizard and it was putting in Issued_Traveler_ID.Issued_Traveler_ID which was causing the error.

Anyhow, my report is working perfectly now, thanks to your help.

Author

Commented:
Excellent answers.
Your welcome :-)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.