CODEWITHSUNDEEP
ms-accessvbaonloadms-access-2010
m3z
m3z

Reputation: 990

Change the Visibility of Group Headers Of a Microsoft Access Report

I'm using Access 2010 to create several reports and i can't figure out how to hide a group heading if its empty.

Imagine I have a table such as (n.b. i know nothing about cars, its just an example):

H1  | H2       | H3     | H4       | DATA1   | DATA2   | DATA3
car | chassis  | engine | pistons  | data_a1 | data_a2 | data_a3
car | chassis  | engine | pistons  | data_b1 | data_b2 | data_b3
car | chassis  | engine | pistons  | data_c1 | data_c2 | data_c3
car | chassis  | engine | cylinder | data_a1 | data_a2 | data_a3
car | chassis  | engine | cylinder | data_b1 | data_b2 | data_b3
car | interior |        | seats    | data_a1 | data_a2 | data_a3
car | interior |        | seats    | data_b1 | data_b2 | data_b3

I have got 3 group headers of columns H1,H2 and H3 in that order. The problem i have is, when H3 contains no text (i.e. "" (I think access evaluates that as null)) i want to hide the group header for H3 or at least make it so it takes no space on the report. Currently wherever H3 is blank, the report still contains a blank row.

I've tried this and similar in GroupHeader3's Paint and or Print events

If IsNull([H3]) Then
    Me.GroupHeader3.Height = 0
    Me.GroupHeader3.BackColor = vbRed
    Me.GroupHeader3.Visible = False
Else
    Me.GroupHeader3.Height = 5
    Me.GroupHeader3.BackColor = vbGreen
End If

The idea seems to work, that is if i comment out the Visible=False line i get a red or a green background in the right places, however it completely ignores the height option. If i put the Visible=False on it complains that it can't be put in the on Paint event.

I've also tried putting similar code in GroupHeader2's paint event but to no avail. The closest I've got it just sits there and flickers.

I'm kinda new to vba so i might be missing something obvious, but at the moment I'm not really sure how to proceed so any help would be much appreciated.

EDIT For clarity:

The report is laid out a bit like so:

H1
    H2
          H3
               H4
                    DATA1       DATA2       DATA3

So with the example data above it would appear as:

car
    chassis
              engine
                      pistons
                              data_a1    data_a2    data_a3
                              data_b1    data_b2    data_b3
                              data_c1    data_c2    data_c3
                      cylinder
                              data_a1    data_a2    data_a3
                              data_b1    data_b2    data_b3
    interior

                      seats
                              data_a1    data_a2    data_a3
                              data_b1    data_b2    data_b3

Its the blank line (group header 3) between interior and seats that i want to avoid. Thanks

Upvotes: 1

Views: 8254

Answers (2)

PhillipOReilly
PhillipOReilly

Reputation: 609

This is an older post, but Mr. Jacot-Descomes' post helped me figure out some conditional formatting for headers in Access. I needed a different header after the first page.

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  Dim reportPageNumber As Integer
  Dim pageHeaderHeight As Double

  pageHeaderHeight = Me.PageHeaderSection.Height

  reportPageNumber = Me.Page
  Me.PageHeaderSection.Height = 0
  PageHeaderSection.Visible = False

  If reportPageNumber > 1 Then
    Me.PageHeaderSection.Visible = True
    Me.PageHeaderSection.Height = pageHeaderHeight
  End If
End Sub

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

You are showing us column headers not group headers. What sense does it make to change the hight of H3 if the other headers are still here? You can't save space that way.

Probably you want to hide the whole column H3 by moving the columns at the right of H3 to the left by the width of H3. There is no easy way to do that. You will have to calculate the positions of the text fields and lables involved and change their "Left" position accordingly in a OnFormat event.

EDIT:

Use the Cancel parameter of the Format event procedure of the group headers:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = IsNull(Me!H0)
End Sub

Since the VBA code never runs in design mode, you will only see its effect in the print preview and when printing.

Upvotes: 3

Related Questions