?
Solved

Report details section being overwritten by subreport

Posted on 2004-09-24
6
Medium Priority
?
435 Views
Last Modified: 2012-06-27
I have an Access report report that has a details section that displays the results from a query that match up with the current date.  Then I have a subreport beneath the details section that displays data that is related to the results from the record displayed in the details section.  The problem is that one of the fields that is printed in the details section is a memo field so I had to set the CanGrow to yes to allow it to re-size as necessary to display the data.  Whenever this happens the subreport ends up having it's data displayed with the second line of the details text displayed on top of it.  How can I get around this?  It seems as though I need to be able to move the subreport dynamically for each record.

The report was originally created in Access 2000 and now it is in Access 2003.  Anything I do to the database must be backwards compatible with Access 2000.

Thanks.
0
Comment
Question by:Xawble
6 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12144751
Do you have the sub-report in the Detail section?
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 12144802
I think your best bet is to group on an id field so that a group header and footer section is created.
You can then move your subreport into the group footer.
The details section will have to finish printing before the group footer will print.  In order for the detaisl section to finish, the memo field wil grow until its finished.
Once it is finished, the GRoup Footer will print and run the subreport, which will start below the bottom of the memo field.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 12144823
I should let you know right off that I am not very familiar with reports or their events.

You can look at the OnFormat event.  The excerpt from Office XP's help file is shown below for you.  I have never experimented with it, so I lack the details to explain exactly how to go about using it.  My general idea is to use the Format event to prepare the .Top property of your subform control for every record.  Supposedly, the event fires for each section, and for each record within the detail section, so it should do just what you want.  Here's an idea for you.  It takes the .Top value of the memo control, adds the .Height value to it, then adds an arbitrary 'spacer' value before assigning the result to the .Top property of the subform control.  We'll probably need some syntax help to make this work properly.

Private Sub MyReportSection_Format()

Me!MySubform.Top = Me!MyMemoBox.Top + Me!MyMemoBox.Height + 5

End Sub

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Format Event

The Format event occurs when Microsoft Access determines which data belongs in a report section, but before Microsoft Access formats the section for previewing or printing.

Remarks
To run a macro or event procedure when this event occurs, set the OnFormat property to the name of the macro or to [Event Procedure].

A Format event occurs for each section in a report. This allows you to create complex running calculations by using data from each section, including sections that aren't printed.

For report detail sections, the Format event occurs for each record in the section just before Microsoft Access formats the data in the record. A Format macro or event procedure has access to the data in the current record.

For report group headers, the Format event occurs for each new group, and a Format macro or event procedure has access to the data in the group header and the data in the first record in the detail section. For report group footers, the Format event occurs for each new group, and a Format macro or event procedure has access to the data in the group footer and the data in the last record in the detail section.

By running a macro or an event procedure when the Format event occurs, you can use data in the current record to make changes to the report that affect page layout. For example, you can display or hide a congratulatory message next to a salesperson's monthly sales total in a sales report, depending on the sales total. After the control is displayed or hidden, Microsoft Access formats the section by using the values of format properties, such as CanGrow, CanShrink, HideDuplicates, KeepTogether, and Visible.

For changes that don't affect page layout or for event procedures or macros that should run only after the data on a page has been formatted, such as a macro that prints page totals, use the Print event for the report section.

There are times when Microsoft Access must return to previous sections on a report to perform multiple formatting passes. When this happens, the Retreat event occurs as the report returns to each previous section, and the Format event occurs more than once for each section. You can run a macro or event procedure when the Retreat event occurs to undo any changes that you made when the Format event occurred for the section. This is useful when your Format macro or event procedure carries out actions, such as calculating page totals or controlling the size of a section, that you want to perform only once for each section.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Xawble
ID: 12144900
Yes, the subreport is in the details section.

The way this report works is it looks for records that have todays date in them.  It will spit out that record in the details section then directly below that it has a subreport that does a query for any other history records that match todays record and then it will print them out.  It keeps doing this until it has finished all of todays records.  I don't think I can put the subreport in the footer because this would cause all of the history data to become separated from todays records.
0
 

Author Comment

by:Xawble
ID: 12145044
Thank you routinet.  The OnFormat worked!
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12147096
OMG...I actually scored a question on reports.  I'll have to print and frame this one.  :)

I am happy you found a use for my humble reporting abilities.  Good luck on the rest of your project!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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