Solved

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

Posted on 2011-03-01
9
961 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

13 Experts available now in Live!

Get 1:1 Help Now