Using GroupLevel in Report_Open Event

[Norris68]

I am trying to set the sort order of my report by modifying the Me.GroupLevel.ControlSource within the Report_Open event. When the report is previewed from design view everything works fine. When it is previewed from closed it never contains any data (NoData event fires too). The documentation clearly states you can alter it, even giving an example in the help.

Is it me? I can find only one KnowledgeBase article that mentions 'GroupLevel' at all.

Code example:
All the 'PlanningReport' variables are global, set within the Print button event before opening the report (most reliable way of getting non-bound data into a report I've found). The PlanningReportSortField is set to the name of the field I want to sort on - there is already a GroupLevel(0) with a header/footer section, so we are setting GroupLevel(1) to sort within each group.

Private Sub Report_Open(Cancel As Integer)
'*** This line here ***
Me.GroupLevel(1).ControlSource = PlanningReportSortField
'*** Yes, that one ***

Me.Caption = PlanningReportTitle
lblReportTitle.Caption = PlanningReportTitle
lblCriteria.Caption = PlanningReportCriteria

'This bit sets the data binding on the first of four text boxes on the detail section - works OK
txtField1.ControlSource = PlanningReportField1
Select Case PlanningReportField1
Case "iipLOCDate": lblField1.Caption = "LOC Date"
Case "iipLastReviewDate": lblField1.Caption = "Last Review"
Case "orgNoSiteEmployees": lblField1.Caption = "No Emps"
End Select

'etc.

BTW: Running Access 2000 SP3

Many thanks in advance.

 

[PaulBricker]

I'll take a wild stab at this before going to far eith your code. To the best of my knowledge you have to open the Report in Design View to make the changes you want. So your code can't go in the Open event of the Report you would have to put it in something like the Click Event for a Button. Then you would do something like this.
Docmd.OpenReport....acViewDesign
Do your changes here, and change any Me.'s to Reports!ReportName.GroupLevel(1)
Docmd.OpenReport....acViewPreview
I've never see grouping changes accomplished in Preview mode.

Paul

 

[Norris68]

Thanks for the reply, but the help clearly states:
/quote

If a group is already defined for a report (the GroupLevel property is set to 0), then you can use the ControlSource property to change the group level in the report's Open event procedure. For example, the following code changes the ControlSource property to a value contained in the txtPromptYou text box on the open form named SortForm:

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0).ControlSource _
= Forms!SortForm!txtPromptYou
End Sub

/end quote

The CreateGroupLevel method can only be used in design mode, but I already have my group level defined.

 

[Norris68]

... besides, I want to issue this database as an mde file - no design mode possible!

 

[jiqjaq]

Yes you can set the sort/grouping order within the report code, provided that the groupings already exist when the code is executed. You cannot create the groupings themselves within the Report_Open. (Access doesn't know which groupings are on the report when you compile, since the GroupLevel array is predefined.)

If you need to have more groupings on some runs of the report than others, you must have the max number of the groupings needed on the report for all of them. For example: if you run sometimes with 4 groupings, but other times use only 2 groupings -There must be 4 groupings on the report even when you're only using 2 of them.

Since Access forces you to assign groupings to some field, the last used sort field should be used for all the remaining ones. So, in the above example for 2 groupings, the 3rd and 4th groupings would be defined to have the same assignments as the 2nd. When the report was run needing the 4 groupings, you would assign the 3rd and 4th groupings to have a different control source using the GroupLevels in the Report_Open.

As you mentioned, this topic is only touched on in most of the documentation. You can do a lot with it provided that you're given the time to figure it out.

 

[PaulBricker]

Norris68, point me to that help file please. I just recently responded to a post where they used the .ControlSource property but I've never actully seen any such property for a Header. If it's for the Textbox in the header then that would make more sense to me but then
Me.GroupLevel(1).ControlSource would seem to need some reference to that textbox. Anyway, just looking to learn a little something here.
Thanks
Paul

 

[Norris68]

jigjag, I have two group levels only, both defined in the design. The first level is fixed - never changes and has a header & footer section. The second level changes for each of three very similar reports. The second level has no group header or footer and is used purely for sort ordering. As mentioned above, whenever I change the ControlSource in the Report_Open event it loses all of the records and fires the Report_NoData event. No errors are raised. If the report is open in design mode it works perfectly when run via my form VBA (DoCmd.OpenReport). It should work, but doesn't. I'm stumped.

If I open the form in design mode then immediately open it in preview mode it works fine.

DoCmd.OpenReport "rptPlanningReport", acViewDesign
DoCmd.OpenReport "rptPlanningReport", ViewMethod, , Criteria

However, I want to issue this database as an mde, and design mode would not be available. Plus, it then prompts the user to save the report each time it is run - undesirable.

PaulBricker, in Access 2000 open a report and go into the VBA Report_Open event. Type 'Report.GroupLevel(0)', make sure the cursor is within the word 'GroupLevel' and hit the F1 key. Both the GroupLevel and TextBox objects have a ControlSource property. You can (or should be able to) set both within the Report_Open event. My code sample above does both - the textbox is called txtField1. (Although there are actually 4 textboxes I have cut down the code sample) The GroupLevel object refers to the Sorting & Grouping level of the report (on the report design menu View -> Sorting and Grouping)