Solved

Access 2007 Report with subreports not working correctly

Posted on 2009-05-05
13
1,009 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 42
Syntax Error in Query 7 30
Set focus on next field when character count = 5 9 10
ms/access vba - how to wait for a form to close 2 5
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now