Solved

Access 2007 Report with subreports not working correctly

Posted on 2009-05-05
13
1,025 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
0
Comment
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24306391
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.
0
 

Author Comment

by:schmir1
ID: 24307087
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.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24308848
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:schmir1
ID: 24308954
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

0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24309754
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.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24314093
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?
0
 

Author Comment

by:schmir1
ID: 24321275
That is what I wanted.  Unfortunately, I get a Enter Parameter Value box which asks for the Issued_Traveler_ID?
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24321341
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)
0
 

Author Comment

by:schmir1
ID: 24326653
What I'm saying is that I got an error which ask me to define Issued_Traveler_ID?
0
 
LVL 7

Accepted Solution

by:
coffeeshop earned 500 total points
ID: 24326810
OK, I give you back what I have changed in your report "Issued_Traveler". Take a look at the recordsource of the commitment-sub and the links.
DTLDB-JustITReport.zip
0
 

Author Comment

by:schmir1
ID: 24329712
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.
0
 

Author Closing Comment

by:schmir1
ID: 31578068
Excellent answers.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24330070
Your welcome :-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question