Solved

Access 2007 Report with subreports not working correctly

Posted on 2009-05-05
13
1,018 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
  • 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
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.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 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