Trying whittle down the number of reports using VBA coding

[catluvr]

Hello All!



I have three reports that are essentially identical. If I change one, I have to go change the other two. I'm hoping I can use an option group and select case to use only ONE report and the user selects which one and the report opens.

Except, I'm seeing some conflicting stuff as I do my research and I'm coming to you all for clarification.

My "base report" is called "rpt Troop Roster". I stripped out any sorting or grouping from the underlying query and from the report itself.

I see that I can use GroupLevel(0) to control all sorts of things ... just what I need! But, it looks like the GroupLevel(0) code needs to be in the Report_Open event.

My reports are not simply a different sort order ... they also have different grouping levels. One has only one sort order and no grouping while another has two sortings, followed by a grouping, followed by two more sortings.

So, I'd think that maybe I can't use the Report_Open event.

OR ... maybe that's exactly where I should put the code and pass an open args to the report_open and so a select case there?

Right now, here's what I have:

Private Sub cmdTrpRoster_Click()

Dim optGroup As Integer
Dim rptName As String


optGroup = "ogRptGrp"
rptName = "rpt Troop Roster"


Select Case Me.ogRptGrp.Value
Case 1
GoTo ByTroop
Case 2
GoTo BySchool
Case 3
GoTo ByLevel
End Select


ByTroop:
    
DoCmd.OpenReport rptName, acViewReport
rptName.GroupLevel(0).ControlSource = "Troop Number"
rptName.GroupLevel(0).SortOrder = True


BySchool:
Exit Sub


ByLevel:
Exit Sub


End Sub

I don't have the code written for the second and third reports, and I know that using "SortOrder = True" actually reverses the sorting. I did that so i could see that my code really worked.

The code hangs on the first rptName.GroupLevel(0) line. It says "Invalid Qualifier"


So, should I put the grouping code in the report_open event? Would I pass the option group value to the report and then do the select case?


Thank you all!


Susie
Kansas

[Micron]

Since we're only talking about 3 reports (and by the sounds of it, they're really not all that "identical") then I'd just go with 3 reports and choose from either a combo list, listbox or option buttons. The latter means that when the boss asks for a variation you will have to redesign the form AND the report code. If you list your report names and their descriptions in a table and use a combo, you won't have to alter much at all. Your combo (for example) would simply pass the added report names to the single line that opens the right report. Additional reports are just another table entry. This would also open up opportunities to show or not a report name in the list. All you'd need is a Level field to assign the reports to.

[DittoBird]

I've done exactly what you are describing.

You need to tell VBA to what GroupLevel belongs. rptName could refer to a table, query, form, macro, module, or a report. So try this:

Reports!rptName.GroupLevel(0).ControlSource = "Troop Number"
Reports!rptName.GroupLevel(0).SortOrder = True

One other quick thing is that GoTo statements are not considered good form (or that used to be the case - I've taken a 5 year pause from Access development and am getting back into it now). Just plop your code into the case 1, 2, and 3 clauses.

GoTo is OK in at least one situation, in specifying an error handler at the start of a sub or function.

If the above doesn't work for you, let me know. I believe I have the code I used in some older database examples I have at home and will try to remember to check this for you when I get home tonight.

Hope this helps,
--
Tim

[catluvr]

Tim,

Where did you use the code? In the command button on-click event or in the report on open event?

Thanks!

Susie
Kansas

[DittoBird]

Sorry Susie - got a flat tire on the way home yesterday and a Mr Bean type of hilarity ensued. Didn't bother with my computer there. Let me check tonight - that's in about 9 hours from this posting time.
--
Tim

[catluvr]

Hello All!

I worked out my report quandry.

Generally, here's what I did:

I made a form with an option group to choose how the report is sorted. The command button on-click event sends report the option group result as an openargs.

I counted up how many groups or sorts the report with the most had. I pulled up the basic report and added that many groups. It makes no difference what the grouping field is. Since the troop number is the most basic group or sort (no two troops have the same troop number), I just made every grouping field the troop number. I added an unbound textbox to each of the group headers. I set each group header to be not visible.

In the OnOpen event of the report, I used an IF-Then statement to evaluate the openargs. For each option, I changed each group's control source to what I wanted to sort or group on. Where ever I wanted the group header to show, I changed it to visible and made the control source for the unbound textbox equal to that group's field.

If I needed less groups or sorts than there were groups in the basic report, I just ignored them, since they were all the troop number anyway.

Now, if I want to change something in the reports, I just have to change it one place!

My partial code: (It's not the whole thing, because each report is just changing the control sources.)

Private Sub Report_Open(Cancel As Integer)

If Me.OpenArgs = 1 Then
        'Sort by Troop Number
            Me.GroupLevel(0).ControlSource = "Troop Number"
            Me.GroupLevel(0).SortOrder = False
            
ElseIf Me.OpenArgs = 2 Then
        'Sort by School
            'Sort by Area (SH or O)
            Me.GroupLevel(0).ControlSource = "Area"
            Me.GroupLevel(0).SortOrder = False
            'sort by school sort (elem, MS, HS)
            Me.GroupLevel(1).ControlSource = "SchoolSort"
            Me.GroupLevel(1).SortOrder = False
            'Group on school
            Me.GroupLevel(2).ControlSource = "School"
            Me.GroupLevel(2).SortOrder = False
            Me.GroupHeader2.Visible = True ' make the header visible
            Me.unbTxtHead2.ControlSource = "School" 'set unbound text box in header to be the value of the school field
            Me.GroupLevel(2).KeepTogether = 1
            'Sort by Program Level
            Me.GroupLevel(3).ControlSource = "PALSort"
            Me.GroupLevel(3).SortOrder = False
            'Sort by Grade
            Me.GroupLevel(4).ControlSource = "SortOrder"
            Me.GroupLevel(4).SortOrder = False
.
.
.
End If

End Sub

One note on the code ... in Access reports, the first group is numbered zero (0), so GroupLevel(4) is really the fifth level.


Susie
Kansas

[DittoBird]

Well done. Sorry I did not get back to you.

One thing you'll want to be careful of when working on the report is when you save it. If you go from report preview to design, do some cosmetic changes and save, you may save the grouping and sort order for your case 2.

You can use openargs as you have or forms!frmMain.ogRptGrp. The advantage of the latter is you may later want to include other criteria on frmMain.
--
Tim