You can adjust the height of the Total Access Memo control and if the report section's CanGrow property is set to Yes, the section grows as well. However, when the control shrinks, the report section's height does not automatically shrink and the it cannot be changed programmatically. Basically, once a report section increases its height, it cannot be reduced without re-running the report.
Fortunately, there is a simple way to trick Access into resizing its report section:
- In the report section where your Total Access Memo control resides, size the section to its maximum height: click on the Section and set it's height property to 22 inches. Then, set the Can Grow and Can Shrink properties of the Section to "Yes".
- Create a text box in the section where the Total Access Memo control resides. The Left and Top properties of this text box should each be 0. This places the text box at the extreme top left of the section.
- Size the new text box so that it is as high as the section. Do this by setting the height property of the new text box to 22 inches, then set the CanGrow and CanShrink properties of the text box to "Yes".
- Set the Visible property of the new text box to No. This "tricks" Access into shrinking its section properly, but is not visible when the report is previewed or printed.
- Paste the following code in the module for the report:
Private Sub Detail_Format(Cancel As Integer, _ FormatCount As Integer) Me.tamNotes.Height = Me.tamNotes.HeightOfText End Sub
where tamNotes, is the name of your Total Access Memo control and theDetail_Format is the section where the Total Access Memo control resides. For more information please review the Total Access Memo help file under the section Using the Control on Access Reports.
Please note that Microsoft has resolved this issue in MS ACCESS 2000 by providing a method to shrink the detail section in code! This is not resolved in MS ACCESS 97 and this workaround is still needed there.