Using code to position a text box

Question

I would like to print a text box for an "order total" in a specific location on a report (e.g. 7" from the left edge and 10" from the top edge)

1. The text box would go into a group footer "=sum(order_total)"
2. The order could span multiple pages, and I would only want to print the order total on the last page of the order
3. There could be multiple orders in the report

Answer 1

I'll give both suggestions a try.

Answer 2

That is simple enough. But, rather than printing the "order" groupfooter at the end of the previous section, I would like to print it at the bottom of the page (same location all of the time). I'm not sure this is possible, unless there is away to place it in the page footer section.

Oddly enough, this is being discussed right now with the Access Development team.

One solution offered was to create a dummy text box in the footer and size it on the fly to push the bottom of the group header.

This was contributed by Brent Spaulding (another MVP and my co-author)

Add a text box control (named "txtSpacer") to the very right of your Report Footer section with a control source of ="" and a height that spans the height of the design height of the Report Footer section.  Set txtSpacer's CanGrow = Yes, CanShrink = No, and Visible = Yes.  The Report Footer should be set to CanGrow = Yes.  Then in the OnFormat of the PageFooter section I set a module variable to the Top of that section.  Then in the OnFormat of the Report Footer section, I get the bottom of the section by adding the sections' Top and Height properties.  I then set the height of Report Footer section by adjusting the txtSpacer control, then I move all the other controls as if they are anchored to the bottom of the Report Footer section.

Here is some code that does I have just laid out.  This is code behind a Report object.

---

Option Compare Database

Option Explicit

Private lngPageHeaderTop As Long

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)

    lngPageHeaderTop = Me.Top

End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

    Dim lngReportFooterBottom As Long

    lngReportFooterBottom = Me.Top + Me.Height

    If lngPageHeaderTop > lngReportFooterBottom Then

        'Calc the adjustment needed to place the Report Footer on top of

        'the Page Footer.

        Dim lngAdjust As Long

        lngAdjust = (lngPageHeaderTop - lngReportFooterBottom)

        'Adjust the height of the Report Footer section

        Me.txtSpacer.Height = lngAdjust + Me.txtSpacer.Height

        'Move all the controls down to accomodate the change in height (ie: Anchor to the bottom)

        Me.txtRptFooterControl.Top = Me.txtRptFooterControl.Top + lngAdjust

    End If

End Sub

While this is designed for the Report footer, the code should be modifiable to use in a Group footer.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 3

You can print the page footer only on the last page of your grouping by doing the following:

Add [Page] and [Pages] to your report (if your pages is resetting as the end of the group seehttp://support.microsoft.com/kb/841779 for instructions).

Then on the OnFormat Event of your Page Footer Section put

Me.PrintSection = (Me.Page = Me.Pages)

Depending on when your [Pages] resets, the page footer will only print on the last page.

Hope this helps.

Answer 4

That is simple enough. But, rather than printing the "order" groupfooter at the end of the previous section, I would like to print it at the bottom of the page (same location all of the time). I'm not sure this is possible, unless there is away to place it in the page footer section.

Answer 5

If there are many orders you probably have a grouping on order number. So you should have a group footer for ordernumber. Your Order total would go int that group footer and it would print only at the end of an order.

No code required!

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries