Solved

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

Posted on 2011-03-01
9
976 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

751 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