Improve company productivity with a Business Account.Sign Up

x
?
Solved

Report details section being overwritten by subreport

Posted on 2004-09-24
6
Medium Priority
?
439 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
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…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

608 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