Solved

Report details section being overwritten by subreport

Posted on 2004-09-24
6
419 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 50

Accepted Solution

by:
Steve Bink earned 125 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 50

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime 2010 Error 17 32
Sum Multiple Columns in Access Query 5 50
Track name AutoCorrect info 14 47
tabctrl with page click event 9 7
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

920 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

15 Experts available now in Live!

Get 1:1 Help Now