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:
I 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 = FalseEnd If
End Sub
When 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 Detail (default) |
Show Summary Only |
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 = FalseEnd If
End Sub
