Modifying Report Grouping/Sorting in VBA 1

[xbigblue]

Hello All,
Can we get to the Report Sorting and Grouping "properties" (I am not sure there are such properties) programatically in Access 2000 VBA? I don't see anything in report properties to indicate that I can. Basically, I would like to be able to say FieldOne grouping is either "ON" or "OFF" based on inputs from a form. I have searched the forums and the only matches seem to be unanswered posts with other folks trying to do what I'm trying to do.

My report has grouping and sorting on four fields. This report has evolved so that it's launched from two different Forms with differing grouping requirements. One form needs grouping on all four fields, the other form will only want to group on three. Input to the report is a very complex SQL multi-table query. Common sense says I should split the effort into two reports, but this will eventually force me to split a series of 11 reports into 22, so I avoid it if possible, prefer to keep one report with two capabilities. I know I can make the group header and footers not visible, which eliminates the totalling on the unwanted field, but the sorting being done on the field causes other control groups to appear mis-sorted. So it seems I need to be able to turn off the sorting programatically. Would the ORDER BY setting on the Report do this?

Hope the question is not too vague.
Thanks, xbigblue

 

[JoeMiller]

The CreateGroup method allows you to add a group to a report, you can also use the Delete method to delete a group for your report. Access help has documentation on these methods to help you.

Joe Miller
joe.miller@flotech.net

 

[xbigblue]

Joe,

Thanks for the quick answer. I found the topics in Access Help and these properties look like exactly what I need.

Tek-tips frequently has the answer and I sure do appreciate it.

Thanks again,
xbigblue

 

[JohnHarkins]

Unfortunately, xbigblue and joe, I did not find the information in Access Help. Can you help me?

Thanks,

John Harkins

 

[JoeMiller]

Make sure you are in a code window when you type in "CreateGroup" otherwise help may not show it. If it still doesn't show it then you may not have installed the VBA help when you installed Office. You can check by putting your Office cd in the drive and checking the installed components.

HTH Joe Miller
joe.miller@flotech.net

 

[weckels]

I am trying to do the same thing but am having problems. I have checked Access Help and can't figure it out. I have created a sort group on the field 'Shift'. On the report it is called GroupFooter1. In this report footer I have placed text boxes that are needed to display 'Shift' data. The problem is that I don't want this grouping footer if the user checks a box on a form and I don't know what code to use to delete this grouping footer in the Report Open event.
if [forms]![form1]![sort] then
me.groupfooter1.delete
end if
Of course, this doesn't work. I would really appreciate some help before I go completely nuts.
Thanks,
Will