Dynamic Report Grouping

K

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
I need to create several reports containing the same fields. The only difference between the reports will be the grouping. I have a form (frmMenuReport) where the criteria for the report is entered and then the required report is chosen from a listbox (lstReports).

I created the report and added the required grouping levels (9 in total) with group headers and group footers.

The first report in the listbox displays all records so no grouping is required. I have the labels in the Page Header for this report. For all the other reports I have a textbox, which displays the grouped field (="Location: "&[Location]) and the labels in the Group Header. All of the controls are in the detail section.

In each Group Footer, I have a textbox which counts the records in each group. The Page Footer has the Date/Time on the left and Page Numbers on the right. The Report Footer has a textbox which displays the total records.

Now to the problem.

I have the following code in the Open event of the report but only the first three reports are displayed correctly (all records, grouped by location, grouped by file type). The others display all records without any grouping. There is no sorting on the report. To test, I created a new report using one of the grouping levels that is displaying incorrectly and copied the labels and controls from the Group Header, Detail and Group Footer to the new report and it displays correctly. Can anyone figure out why I am not getting all of the grouping levels to be displayed correctly?

Code: 
Private Sub Report_Open(Cancel As Integer)
    If Forms!frmMenuReport.lstReports.Value = 1 Then
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 2 Then
        Me.GroupLevel(0).ControlSource = "LocationID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = True
        Me.Section(6).Visible = True
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 3 Then
        Me.GroupLevel(0).ControlSource = "FileTypeID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = True
        Me.Section(8).Visible = True
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 4 Then
        Me.GroupLevel(0).ControlSource = "IssueID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = True
        Me.Section(10).Visible = True
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 5 Then
        Me.GroupLevel(0).ControlSource = "ClientID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = True
        Me.Section(12).Visible = True
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 6 Then
        Me.GroupLevel(0).ControlSource = "DivisionID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = True
        Me.Section(14).Visible = True
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

If Forms!frmMenuReport.lstReports.Value = 7 Then
        Me.GroupLevel(0).ControlSource = "ActionID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = True
        Me.Section(16).Visible = True
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 8 Then
        Me.GroupLevel(0).ControlSource = "AccountStatusID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = True
        Me.Section(18).Visible = True
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 9 Then
        Me.GroupLevel(0).ControlSource = "BranchID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = True
        Me.Section(20).Visible = True
        Me.Section(21).Visible = False
        Me.Section(22).Visible = False
    Exit Sub
    End If

    If Forms!frmMenuReport.lstReports.Value = 10 Then
        Me.GroupLevel(0).ControlSource = "TermTypeID"
        Me.Section(3).Visible = False
        Me.Section(5).Visible = False
        Me.Section(6).Visible = False
        Me.Section(7).Visible = False
        Me.Section(8).Visible = False
        Me.Section(9).Visible = False
        Me.Section(10).Visible = False
        Me.Section(11).Visible = False
        Me.Section(12).Visible = False
        Me.Section(13).Visible = False
        Me.Section(14).Visible = False
        Me.Section(15).Visible = False
        Me.Section(16).Visible = False
        Me.Section(17).Visible = False
        Me.Section(18).Visible = False
        Me.Section(19).Visible = False
        Me.Section(20).Visible = False
        Me.Section(21).Visible = True
        Me.Section(22).Visible = True
    Exit Sub
    End If
End Sub

Thanks!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
If you don't mind a guess, I'd say that you're using the wrong event. Maybe the OnFormat event?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
365
bobsan42
bobsan42
T
  • Solved
Access table set start of day and end of day to my schedule
Replies
2
Views
621
Tcurtis
T
I
  • Question
Advice to remove old code for a new code
Replies
2
Views
179
ipbr21054
I
D
  • Question
Grouped row visibility based on the condition of main row
Replies
0
Views
404
Dhruvil
D
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
492
offthelip
O

Forum statistics

Threads
1,221,771
Messages
6,161,847
Members
451,723
Latest member
Rachetsely

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top