My collection of things.

jKafer on the Net

May 8th, 2014 at 1:27 pm

MS Access Report – dynamically collapse groups

Here is another bit of programming to make things easier with reports.

As we all know, you can put in many levels of grouping in MS Access reports. And there is always the request – I want to see detail and then I don’t want to see detail. The simplest thing to do is have 2 reports – one with many levels of groupings and one without. But is that the best way?

Depends – if you are not comfortable writing VBA code, then yes that is the simplest thing to do, but beware you will have to update both(all) reports when any changes are asked for by the boss.

Here is what I did:

rptDesignModeI have a report that is setup with 3 levels of grouping: Territory, Salesman, Month – what I found is to set up the report with the most detail/grouping that you will want to have, then you can not view the ones you do not want. This gives you the ability to add text to the header, totals to the footers and/or any other custom things you may want in the header/footer.

With the report in design view, click on Event tab – go down to “On Open” and click on the down arrow and select “Event Procedure”. This should open the VBA screen.

You should see something like this:

Private Sub Report_Open(Cancel As Integer)

End Sub

Here is my code:

Private Sub Report_Open(Cancel As Integer)

Dim vdet As Integer

vdet = MsgBox(“Do you want to see Salespreson Totals?”, vbYesNo)

If vdet = 7 Then

Me.GroupLevel(0).ControlSource = “Territory”
Me.GroupLevel(1).ControlSource = “Month”
Me.GroupLevel(2).ControlSource = “Month”

Me.GroupHeader3.Visible = False
Me.GroupFooter4.Visible = False

End If

End Sub

 

rptPromptWhen the report is opened (usually by a button on a menu) you will see a prompt asking which report you want to see:

Here is an example (without numbers) using the above code to show or not show details.

Show Salesperson DetailfullDetailsRpt (default) Show Summary OnlySumRpt

Alternative: Have a checkbox or radiobutton (rptChoice) on your form that would make this decision before the report is opened. Here is the code to do that:

Private Sub Report_Open(Cancel As Integer)

Dim vdet As Integer

vdet = me.rptChoice.value

If vdet = 1 Then

Me.GroupLevel(0).ControlSource = “Territory”
Me.GroupLevel(1).ControlSource = “Month”
Me.GroupLevel(2).ControlSource = “Month”

Me.GroupHeader3.Visible = False
Me.GroupFooter4.Visible = False

End If

End Sub

-

Comments are closed.

  • Pages

  • Latest Pins on Pinterest

    • Follow Me on Pinterest
  • Quilting Projects I Want to Make

    • Leading Edge Quilt Pattern PDF Download - Etsy

    • Homecoming Quilt- My Peppered Cotton version & tester quilts! – Lo & Behold Stitchery

    • Homecoming Quilt- My Peppered Cotton version & tester quilts! – Lo & Behold Stitchery

    • On the Edge is a quick and fun modern quilt pattern with a lot of custom design possibilities! This PDF pattern includes directions for making a twin-size quilt (72 x 96), a throw-size quilt (56 x 64), a baby size (38.5 x 44), and a pillow/mini quilt (18 x 18). See the Instagram hashtag

    • Adelita Quilt Pattern digital Download - Etsy

    • Follow Me on Pinterest