Solved

Report details section being overwritten by subreport

Posted on 2004-09-24
6
417 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
Comment Utility
Do you have the sub-report in the Detail section?
0
 
LVL 28

Expert Comment

by:bdreed35
Comment Utility
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 50

Accepted Solution

by:
Steve Bink earned 125 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Xawble
Comment Utility
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
Comment Utility
Thank you routinet.  The OnFormat worked!
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
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…

771 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

12 Experts available now in Live!

Get 1:1 Help Now