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.


  • Count per Day

    • 1Visitors today:
    • 33Visitors per day:
    • 0Visitors currently online:
  • ScrapBook Software - MyMemoriesSuite
  • Latest Pins on Pinterest

    • @suppose_quilts I totally OMG'd outloud when I saw this quilt! I love it! By @suppose_quilts "It just snowed here but we're pretending it's spring. Pattern: Amsterdam, free at cloud9fabrics.com. Fabric: Pam Kitty Garden" via @PhotoRepost_app

    • Striking Strip Quilts: 16 Amazing Patterns for 2 1/2"-Strip Lovers: Kate Henderson: Jelly roll tulip quilt. Flower quilt pattern. affiliate link.

    • Fall Table Runner | from Marta with love http://frommartawithlove.com/fall-table-runner/

    • Scrappy Butterfly Baby Quilt Tutorial | Let your imagination take flight with this baby quilt!

    • All In A Row Again!

    • Feathered stars are fun to quilt! #freemotionquilting #longarmquilting #quiltedthistle

    • Westalee - Curly Q's

    • 18 Easy tutorials for making your own fabric labels at home. Click through to browse the list! DIY Crush

    • 6a0120a5f3f908970b015437f40faf970c-pi 3,312×4,416 pixels