Dynamically creating groups and sorting

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

I use a form to allow the user to select what fields to sort and group
by for a report. I can dynamically create the sort order. I am using
the CreateGroupLevel method to establish the group in the report. The
problem with my method is that I have to set this feature in the design
of the form and then the user is prompted to save the report. I do not
want any prompt. If I save the report in code then new groups get added
instead of replaced.

Is there a better technique to use to create a dynamic group in a
report? The report will have only one field to group by each time and
that group will only have a footer. I also need to place a line that
spans the width of the group footer. I do not want any prompt for the
user to have to save anything.

If anyone can give me advice to improve my current approach or a new
approach that will accomplish the same thing, I would appreciate it.
 
Pre-create the levels and then set their Control Source properties at
run-time in the On Open event of the report.
 
Since you only need one group level, you don't need to create more group
levels on the fly. Just reassign that one.

It is just a matter of setting ghe ControlSource of the GroupLevel in the
Open event of the report. Example here:
Sorting Records in a Report at run-time
at:
http://allenbrowne.com/ser-33.html

Even if I needed 10 groupings, I would still do it that way, setting the
ControlSource of any unused groupings to the same as the last used one so
they had no effect, and setting the Visible property of the unneeded group
footer sections to No. Not only does this avoid the save prompts, but it
also leaves the door open for you to use the approach with an MDE if you
want to do that some day.
 
Thanks for the quick response. Allen, I changed to your approach and it
is a simple solution to most of my problem.

Here is what I see happening now:
When I build the reports by hand, I can sort by multiple fields and
then assign the group separator line, the reason I need groups at all,
to the appropriate field regardless of its sorting position. Now that I
am sorting and grouping dynamically, this feature is not yet right.

Let's say the user wants three fields to sort by but chooses the second
as the one to group by, my setup now, unintentionally, assigns the
grouping field as the primary sort and overrides the user's wish. I
only allow one group field but the user may sort by up to seven fields.


My sort form has seven combo boxes to determine sort fields and then
builds a combo box dynamically to let the user choose one of those
sorted fields to use as a group. What should I modify in my report
sorting so that I accurately reflect the user's sorting and grouping
sequence?
 
I think it would be very unusual to need to give more than 3 or 4 options to
sort by *before* the grouping one, but you could create (say) 10 levels of
sorting, with the grouping taking place on the 5th one. Repeat the same
field name for any unused GroupLevels.

This gives you 4 choices before the grouping, and 5 afterwards. Is that
enough?
 
Back
Top