Turning off report footer section based via VBA

  • Thread starter Thread starter Jim Burke in Novi
  • Start date Start date
J

Jim Burke in Novi

I have a report that has two fields defined for sorting and grouping. I have
the header and footer sections displayed for both, but there are times where
I don't want to show the footer section for the 2nd field. These fields are
called "DataSource" and "Category" ,with DataSource being defined at the
higher level - there are multiple Category values within a DataSource value,
but for certain DataSources I know there will always be only one line per
category, so I don't need totals for Category when this happens. So when
DataSource has a certain value I don't want to show the footer for Category.
I can't figure out how to do this. Any help is apreciated! To illustrate,
let's say i don't want the Category header printed when DataSource is
"Office", so it would be laid out like this

DataSource: "Hospital"
Category: "OV"
"Hospital 1" $100
"Hospital 2" $200
"OV" totals (I want the footer here since DataSource is "Hospital")
Category "Labs"
"Hospital 1" $50
"Hospital 2" $100
"Labs" totals (I want the footer here since DataSource is "Hospital")
"Hospital" totals (always want this)

DataSource: "Office"
Category: "OV"
"In Office" $100 (I don't want a footer here since DataSource is
"Office")
Category "Labs"
"In Office" $50 (I don't want a footer here since DataSource is
"Office")
"Office" totals (always want this)
 
Jim Burke in Novi said:
I have a report that has two fields defined for sorting and grouping. I
have
the header and footer sections displayed for both, but there are times
where
I don't want to show the footer section for the 2nd field. These fields
are
called "DataSource" and "Category" ,with DataSource being defined at the
higher level - there are multiple Category values within a DataSource
value,
but for certain DataSources I know there will always be only one line per
category, so I don't need totals for Category when this happens. So when
DataSource has a certain value I don't want to show the footer for
Category.
I can't figure out how to do this. Any help is apreciated! To illustrate,
let's say i don't want the Category header printed when DataSource is
"Office", so it would be laid out like this

DataSource: "Hospital"
Category: "OV"
"Hospital 1" $100
"Hospital 2" $200
"OV" totals (I want the footer here since DataSource is "Hospital")
Category "Labs"
"Hospital 1" $50
"Hospital 2" $100
"Labs" totals (I want the footer here since DataSource is "Hospital")
"Hospital" totals (always want this)

DataSource: "Office"
Category: "OV"
"In Office" $100 (I don't want a footer here since DataSource is
"Office")
Category "Labs"
"In Office" $50 (I don't want a footer here since DataSource is
"Office")
"Office" totals (always want this)
Click to expand...


You can have code in the Format event of the DataSource header section that
conditionally sets the .Visible property of the Category footer. For
example,

'----- start of code -----
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Me.GroupFooter1.Visible = (Me.DataSource <> "Office")

End Sub

'----- end of code -----

You'll need to identify the names of the relevant header and footer
sections, to use in the code.
 
Back
Top