Set a report's Sort/Group programmatically?

[Dennis]

I'm opening up a form that lets a user select what criteria he wants to
filter a report by. Things like tech Name, Start and End Date range, Company
Name, etc. All fine. Done it all before.I pass that information into the
query that underlies the report. No sweat. HOWEVER (heh), I want the user to
be able to choose how he wants to sort the report (minor sort, not the major)
via a radio button/frame. Never having done THAT before, I realize that I
need to be able to add a sort/group to the report programmatically. (Note
that this will not be a permanent change to the report, so I don't want to
save the change when the report is closed.)

I will also need to create a Group Footer and add SUM controls to it, and
place them at the appropriate x-y coordinates. I have not done ANY of that,
ever, in 5+ years of working with Access. Can someone please point me in the
right direction on all this? I'd sure appreciate it!

Access 2003 in this case.


TIA!

 

[Allen Browne]

Set the ControlSource of the GroupLevel.

Details and example:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

 

[Dennis]

Thanks Allen. That's half of it. Then I have to create a group footer and
place controls on it, also programmatically.

 

[Allen Browne]

What I'd suggest is that you create the section in design view and add
whatever controls you need. If necessary, you can then programmatically
assign their ControlSource with code in Report_Open.

In general, it's not a good idea to use design view at runtime. For example,
this prevents you from ever using the runtime with your database.

If you really must do it, it is possible. See VBA help on
CreateReportControl.

 

[Dennis]

Good points.

My issue is that the user wants to select the major/minor sort for the
report via radio buttons. I would like to *avoid* (at all cost if possible)
creating duplicate reports with the various changes to the detail and footer
control placement. If you have some thoughts on a viable alternative, I'd
seriously LOVE to hear them!

Thanks (as always)

;^)

Dennis

 

[Marshall Barton]

My issue is that the user wants to select the major/minor sort for the
report via radio buttons. I would like to *avoid* (at all cost if possible)
creating duplicate reports with the various changes to the detail and footer
control placement. If you have some thoughts on a viable alternative, I'd
seriously LOVE to hear them!

Just set the control's Left/Top properties as needed in the
report's open event (base on the same options used for the
sorting).

If you do not want to see a header/footer in some case, then
set the section's Vislble property to False.

Note that you can "disable" an existing groupsort by setting
the GroupLevel's control source to a constant expression
(eg. =1)

 

[Dennis]

Thanks for the additional information. I'll be in a position to experiment
with it in a few days. I appreciate your responses.