Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

Access 2007 Report with subreports not working correctly

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
schmir1
Asked:
schmir1
  • 7
  • 6
1 Solution
 
coffeeshopCommented:
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
 
schmir1Author 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.
0
 
coffeeshopCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
schmir1Author 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

0
 
coffeeshopCommented:
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
 
coffeeshopCommented:
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
 
schmir1Author Commented:
That is what I wanted.  Unfortunately, I get a Enter Parameter Value box which asks for the Issued_Traveler_ID?
0
 
coffeeshopCommented:
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
 
schmir1Author Commented:
What I'm saying is that I got an error which ask me to define Issued_Traveler_ID?
0
 
coffeeshopCommented:
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
 
schmir1Author 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.
0
 
schmir1Author Commented:
Excellent answers.
0
 
coffeeshopCommented:
Your welcome :-)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now