Solved

MS Access 2007 ADP - how do I keep the heading and detail for a subreport together?

Posted on 2011-03-01
9
975 Views
Last Modified: 2012-05-11
I have a quotes document that includes a summery page with corresponding detail.  In the detail, I have 22 sub reports, but only a max of 4 will print for any given quote, based on certain conditions being met.  I would like the heading and at least 1 record from the detail to print together on the same page.  If all sub reports can fit on the same page, that's fine.  If not, I want them to overflow to another page correctly with the heading and the detail (at last one record) printing together on the same page.  Please see attached file.  Since this is an external document, we need it to look asthetically nice.  I have been trying to get this right for at least 3 days now, so I am attaching a large point value as it is very imperative that I get this right.  Thanks for any help offered:)
Quotes---Experts-Exchange-Explan.docx
0
Comment
Question by:mburk1968
[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
  • 5
  • 2
  • 2
9 Comments
 
LVL 84
ID: 35010813
If you only show 4 subreports, then why not just add 4 Subreport Controls to the Detail section and set the SourceObject of thost Subreport controls to the 4 that you need to show. This would be MUCH easier to format than 22 different Subreport controls (and should be more performant as well).

0
 

Author Comment

by:mburk1968
ID: 35010878
At any given time only a max of 4 will show.  However, each sub report varies by different columns and number of columns.  Regardless, I still need to be able to print the sub report with the heading and at least one detail record together on one page.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 35010942
Right - so just change the SourceObject for each of your 4 Subreport Controls, and then you can use the Format options available to manage this.

To change the SourceObject, you'd do this in the Report's Open event:

Me.SubreportControl1.SourceObject = "rptCustomers"
Me.SubreportControl2.SourceObject = "rptContacts"
etc etc

Also, you can make your subreport controls very "short", and then set the CanGrow property as needed. This allows Access to properly format those subreports.

I really don't see how you'll ever get a satisfactory layout of a report with 22 subreports, especially so much of your formatting is dependent on what has been shown/hidden prior to a control. With 22 subreports, there's just too many options to deal with.
0
Independent Software Vendors: 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!

 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 35010973
<<At any given time only a max of 4 will show.  However, each sub report varies by different columns and number of columns.  Regardless, I still need to be able to print the sub report with the heading and at least one detail record together on one page.>>

  If I'm understanding you correctly:

1. Add a group in the sub report with the expression of =1
2. Set the group header for this to yes, footer to no.
3. Set the group headers repeat section property to yes.
4. Place your headings in the group header.

  This will make sure the headings appear at the top of each page for the subreports and you won't get them just once on the first page that the subreport appears on.

JimD.
0
 
LVL 57
ID: 35010983

 BTW, try it with one sub report first as a test to make sure your getting what you want.  Then go back and modify the others.  

 I'm not positive this is what you are looking for.  If not, a screen shot would help.

JimD.
0
 

Author Comment

by:mburk1968
ID: 35018742
0
 

Author Comment

by:mburk1968
ID: 35018772
I included a sample page of the issue I am having, along w/the design behind the sub report.  I need to have a group footer because I have totals for each sub report.  I could not get the =1 Group to work.  It still did not page break correctly.
0
 

Author Comment

by:mburk1968
ID: 35018799
LSMConsulting...
I did take your advice and created just 4 sub report controls, and then based on certain conditions, specified the correct Source Object.  It is much more manageable this way, but I'm still having the issue where I'm page breaking between the header and detail.  
0
 

Author Closing Comment

by:mburk1968
ID: 35021171
Thanks for the prompt responses...
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

756 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