[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Report details section being overwritten by subreport

Posted on 2004-09-24
6
Medium Priority
?
433 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
[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
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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