Selecting a report's sort order & sort fields at runtime

  • Thread starter Thread starter Alp
  • Start date Start date
A

Alp

Hi Experts,

I would like to have opinions, advices or pointers on how to achieve the
subject matter. App is in A2K, the report has quite a few columns. There are
several fields that are/will be used for the sort order such as comp_name,
reg_date, tot_items, comp_priority. The aim is, if possible, to give the
user the option to select which (or more that one) field the sort should be
on and Asc/Desc.
This should be achievable via a form but just can't seem to focus where or
how to start.

Any help is much appreciated.

Thanks in advance.

Alp
 
Alp said:
I would like to have opinions, advices or pointers on how to achieve the
subject matter. App is in A2K, the report has quite a few columns. There are
several fields that are/will be used for the sort order such as comp_name,
reg_date, tot_items, comp_priority. The aim is, if possible, to give the
user the option to select which (or more that one) field the sort should be
on and Asc/Desc.
Click to expand...


First, use the report's design view to add a sufficient
number of sorting levels using the Sorting and Grouping
window(View menu).

Then use code in the report's Open event procedure to change
the level's ControlSource and other properties. E.g.

Me.GroupLevel(0).ControlSource = Forms!theform.sort1field
Me.GroupLevel(0).SortOrder = Forms!theform.sort1order

You can stop a group level from sorting by setting its
ControlSource to a constant expression. E.g.

Me.GroupLevel(2).ControlSource = "=1"

Check GroupLevel in Help for other properties and more
details.
 
Hi Duane,

Thank you for your message advising Allen's solution. I was actually going
through your DH_qbf and wondering if I could incorporate that into my
application to work with my report. I am also thinking of how to add another
option (Ascending/Descending) to your form which would help more. Then I
will need to figure out a way to use the output as the record source for the
report. The frmQBFSource is very tempting (!) eventhough I do not need all
that it offers at present.

If I can manage to do so, then it will clearly end my headache. One question
though; would there be any potential problems in an mde?

Alp
 
Hi Marsh,

Thank you for your advice. Thus, I need to run the report via a form that
should stay open at least in the background or hidden once the report opens.
In my case I have 4 groups that sorts (in the order of sorting) the type
(Asc), Month (Asc), Day (Asc) and total (Desc) at present where I need to
add yet another on priority (Asc) which would supercede the previous 4.

In short, I need the sorting done either via the set of 4 or the last one
(per the requirement).

I'll give it a try, and come back if I get stuck.

Alp
 
The qbf builds a dynamic query. You could add code that would select the
fields from the report.
 
Back
Top