Group Footer and Page Footer

[cschulz]

I am new at Access. For a report, on pages that there is a group footer I do
not need a page footer. How do I suppress the page footer when there is a
group footer?

 

[Larry Linson]

That seems an "unusual" report design. If you can describe in more detail
what information you are showing in the footers, perhaps someone could
recommend a design approach to eliminate this need.

However, you can declare variables in a standard module to use for
"communication", save the page number in the Print event of the Group
Footer, and then in the Format event of the Page Footer, check the saved
page number, and set all the Page Footer Controls' Visible properties to
True of the current page is not the same as the saved page, or to False if
the current page is the same as the saved page number. My approach to this
would use VBA code, but it's possible that it could be accomplished with
macros.

Larry Linson
Microsoft Office Access MVP

 

[cschulz]

The form/report design is set by the Wisconsin Dept of Transportation.
Usually the report is created in Word or Excel. There are a lot of data
relationships that I believe would be better managed in Access. So I am
trying to re-write their report in Access. Visually it needs to look the
same. That being said....the report is a list of items with a quantity and a
unit. Each row has two empty fields for HANDWRITTEN DATA. The fields are
"Unit Price" and "Amount". At the bottom of each page is a box for a
HANDWRITTEN sub total of the data above. I have a text box with "sub-total"
displayed and then an empty text box with a border only.
(Hope you are following.)

The top of the next page has an "Amount Brought Forward" box and then the
list continues. At the bottom of the list are two fields; again set up to
hand write in a total. The fields are "Subtotal" and beneath that "Total".

 

[Larry Linson]

Do I understand that the format of the report requires that the Subtotal and
Total Controls appear immediately following the data in the group footer, so
that the Subtotal control in the Page Footer (required on every other page)
should not be visible?

I presume, it being a state requirement, that you do not have the leeway to
show both Subtotal and Total in the location of the Page Footer at the
bottom of the page on pages where a group is completed. But, that would be
no simpler.

Seems to me my idea for making the Page footer Controls not Visible or
Visible depending on the value of the saved variable in a standard module
(not a form's module, not the report's module) is what you need to do --

In the standard module:

Dim lngPgNo as Long

in the Print event of the Group Footer section, you have the VBA code:

lngPgNo = Page

in the Format event of the Page Footer:

If lngPgNo = Page Then
Me.txtSubtotalText.Visible = False
Me.txtEmptyBoxForManualEntry = False
Else
Me.txtSubtotalText.Visible = True
Me.txtEmptyBoxForManualEntry = True
End If

Barring some unexpected order of formatting and printing (as sometimes
occurs in reporting), it seems that should accomplish what you want. Caveat:
I did not create a sample database to try this out, but have done something
similar in the past.

Larry Linson
Microsoft Office Access MVP

 

[cschulz]

Thanks,
Yes, your presumption is correct. The report requires that the subtotal and
total controls appear immediately following the data in the group footer.

I tried to implement what you have described below. It did not work. I am
a beginner at Access so I very likely did something incorrectly. You
instructions to sent up the variable in the "STANDARD MODULE" confused me.
Would you please clarify. The text box with the subtotal in the footer is
txtPageFooterSubtotal. Would please go through your instructions one more
time? Thank you so much.

 

[Larry Linson]

A standard module, in Access terms, is a module that is not a "class module"
(the modules associated with Forms and Reports are, officially, "class
modules"). The newsgroup is not appropriate for trying to teach details of
programming to a novice, so just let me tell you how to do it. BTW, all
these instructions apply to Access 2003 or earlier... if you are using
Access 2007, you need someone else to describe how to navigate to the
functionality I describe:

(1) On the Database Window, click Modules, then Choose
"New". That will open up a module Window with a new
Standard Module, in which you can code

Dim lngPgNo as Long

Then Save and Close that Window, by clicking the little
red X in the upper right-hand corner. Because the lngPgNo
variable is defined in a standard module, it will be visible to
code in any other module, either standard or class module.

(2) In the Property Sheet for the Group Footer (if it is not
visible in Design View, right-click the Group Footer, and
choose Properties), click the Events Tab, click the line for
the Print Event, click the box with the three dots, and in
the resulting dialog, choose "Code Builder". In the module
that is opened, the cursor will be positioned between a
"Private Sub " statement and an "End Sub" statement, and
you should type:

lngPgNo = Page

Then you can click the red X in the upper right hand corner
to close the module window.

(3) Follow the same steps to open the module window for
the Page Footer, and in that module, type:

If lngPgNo = Page Then
Me.txtFooterSubtotal = False
Else
Me.txtFooterSubtotal = True
End If

Close the module window.

That should do what you want. However, before, you wrote about two text
boxes one telling the user to enter the subtotal and the other empty for the
user to write the data -- you may have mis-described a Text Box and its
associated Label. In any case, if one of the boxes is still visible,
please just add it to the code above in both the first and second
sections -- no need to report back here that one of the items is still
visible.

Frankly, I generally do not write detailed step-by-step VBA code solutions
in newsgroups for users at a level where they have no idea how to get to the
point of entering code. I am not sure what gave me the idea that you were
at a level of knowledge where giving you the code would be enough. I caution
that I have not yet expended the time and effort to create a sample (which
might or might not be close enough to what you are working with to even be
meaningful) and try this, so there may yet be problems.

If it does not work, I invite one of my colleagues to step in and follow
through, because I have exceeded the time and energy that I can allocate for
answering a simple newsgroup question.

Larry Linson
Microsoft Office Access MVP

 

[cschulz]

Thanks Larry,

I am self taught but a quick learn. What you described below is what I had
done, even before your additional instructions...I just thought I was missing
something. It didn't work but I will work out the problem myself. You gave
me a good start. I like this site and really appreciate your help. Have a
good day.
C