Hiding Group Footer when only 1 record in the group

[Burton]

In my report, I have my data grouped by state and then sub-grouped by army
installation. I have a group footer for the states which aggregates all of
the data from each of the army installations in that state. My problem is
that some states only have 1 army installation and so the state totals are
meaningless because they just repeat the data for that 1 installation. I
would like to have the group footer (state totals) hidden if there is only 1
record (1 army installation) in that state. How do I do this?

Thanks,

Burton

 

[Duane Hookom]

Does an installation have more than one record in the report record source?
If so, there wouldn't be a need to be "sub-grouped". Yet you state later "is
only 1 record (1 army installation)" which seems to suggest installations are
unique records in the record source.

If there are more than one records per installation, do you have a group
header ro footer section?

 

[John Spencer]

Add a new textbox (txtLineCount) to the detail area prior to the group you
want to show/hide (set the textbox's visible property to NO)
Set its Control Source to: = 1
Set its Running Sum to: Over Group

In the groupfooter's format event that you wish to hide, you need the
following VBA code:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtLineCount = 1
End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[vtj]

If I am trying to suppress group footer printing in a report where the group
footer will be used sometimes (multiple items to total) or not at all (only
one or no lines to total), does the above apply? There is already a sub in
the report (copy below). Will Private Sub GroupFooter0_Format(Cancel As
Integer, FormatCount As Integer) run before Private Sub
GroupFooter7_Format(Cancel As Integer, FormatCount As Integer)? If there are
multiple subs for the same purpose (GroupFooterX_Format) do they all run each
time a footer is encountered? Or do they need to be strung together? My
very limited knowledge of VBA is that Private Sub GroupFooter0_Format(Cancel
As Integer, FormatCount As Integer) purpose is setting Cancel and
Me.txtLineCount to 1? Does the cancelling of printing also cancel the line
feed or does the space for the 'invisible items' still appear? If it does
still appear is there a way to do a negative line feed also? What else
should I be checking?
Private Sub GroupFooter7_Format(Cancel As Integer, FormatCount As Integer)
If recordnumber% = 1 Then Me.Line25.Visible = False Else Me.Line25.Visible =
True
If recordnumber% = 1 Then Me.Line26.Visible = False Else Me.Line26.Visible =
True
If recordnumber% = 1 Then Me.Line78.Visible = False Else Me.Line78.Visible =
True
If recordnumber% = 1 Then Me.Line77.Visible = False Else Me.Line77.Visible =
True
If recordnumber% = 1 Then Me.Line79.Visible = False Else Me.Line79.Visible =
True
If recordnumber% = 1 Then Me.Field70.Visible = False Else Me.Field70.Visible
= True
If recordnumber% = 1 Then Me.Field71.Visible = False Else Me.Field71.Visible
= True
If recordnumber% = 1 Then Me.Field19.Visible = False Else Me.Field19.Visible
= True
If recordnumber% = 1 Then Me.Field20.Visible = False Else Me.Field20.Visible
= True
If recordnumber% = 1 Then Me.Field21.Visible = False Else Me.Field21.Visible
= True
If recordnumber% = 1 Then Me.Line80.Visible = False Else Me.Line80.Visible =
True
If recordnumber% = 1 Then Me.Field69.Visible = False Else Me.Field69.Visible
= True
If recordnumber% = 1 Then Me.Field68.Visible = False Else Me.Field68.Visible
= True
End Sub

 

[Burton]

Sorry I didn't get back to you sooner, I thought it was supposed to email me
when I got a reply. In any case, let me post an example of my report since I
don't know all of the lingo and so can't do a very good job of describing my
issue.
Indiana
Fort XXX
Value 1 Value 2 Value 3 Value 4 Value 5
5 3 2 1 4
Fort YYY
Value 1 Value 2 Value 3 Value 4 Value 5
2 1 6 5 3

Indiana State Total (Group
Footer)

Value 1 Value 2 Value 3 Value 4 Value 5
7 4 8 6 7


Utah
Fort TTT
Value 1 Value 2 Value 3 Value 4 Value 5
5 3 2 1 4

Utah State Total (Group Footer)

Value 1 Value 2 Value 3 Value 4 Value 5
5 3 2 1 4

So for Indiana, I would like a group footer because it gives meaningful
information. However, for Utah, since there is only 1 installation, the total
is just redundant. In this situation, I would want it to hide the group
footer.

If you or anyone else could provide me an answer, it would be greatly
appreciated. John's didn't seam to work for me, but that could possibly be
because I am a novice and didn't do something correctly.

Thanks in advance,

Burton

 

[Burton]

Sorry, the formatting on the post didn't work very well. I will try it again.
I apologize if it pushes everything back over to the left again, but
hopefully you still get the gist of what my report looks like.

Thanks again.

Indiana
Fort XXX
Value 1 Value 2 Value 3 Value 4 Value 5
5 3 2 1 4

Fort YYY
Value 1 Value 2 Value 3 Value 4 Value 5
2 1 6 5 3

Indiana State Total (Group Footer)

Value 1 Value 2 Value 3 Value 4 Value 5
7 4 8 6 7


Utah
Fort TTT
Value 1 Value 2 Value 3 Value 4 Value 5
5 3 2 1 4

Utah State Total (Group Footer)

Value 1 Value 2 Value 3 Value 4 Value 5
5 3 2 1 4

 

[John Spencer]

Are you trying to hide the entire group footer? If so

Add a new textbox (txtLineCount) to the detail area prior to the group you
want to show/hide (set the textbox's visible property to NO)
Set its Control Source to: = 1
Set its Running Sum to: Over Group

In the groupfooter's format event that you wish to hide, you need the
following VBA code:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtLineCount = 1
End Sub

Also, RecordNumber% is a bad name for a field or a control. You may need to
refer to it as ME.[RecordNumber%] to avoid problems.

With your method you must make sure that the can shrink property of the
section is set to Yes (true).



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Burton]

John,

Thanks for the response. I am confident that it will work; however, because
I am a novice, I think I am not understanding exactly how to execute your
instructions.

I believe I understand the first part. For the second part, I am unsure
where to paste the VBA code. I have tried it in a few places, but keep
getting back a syntax error, which aside from making me feel like I have it
in the wrong place, makes me think there might be a problem with the code
itself (however, there is a better chance that I am just messing it up
myself). Please let me know where the code belongs and explain it to me as if
I were an idiot, because I am.

Thanks,

Burton

 

[Duane Hookom]

View the report in design view and select the appropriate report section
(maybe Group Footer). View the properties and find the On Format Event
property.

Enter or select [Event Procedure] then click the builder button [...] on the
right. This will open a module window containing at least the first and last
lines of your event code:

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

End Sub

Enter the code from John so that it ends up looking the same. Then, you
might want to check the code for syntax errors by selecting "Debug->Compile
....." from the menu. If you don't get any error messages, you can close the
Microsoft Visual Basic window.