Controlling report sorting using vba

[Daniel]

Hello,

Could someone give me an example code of how I can control the sorting
for a report? The user have requested that they be capable of controling
how they sort the report (include or exclude sub-sort....). Is this a
simple task?!

On a seperate note... I'm always a little confused about newsgroup
ediquette. When I have a question about programming a report (such as the
question above), should it be posted in the programming newsgroups or the
report newsgroup? Does it really matter or bother anyone?!

Thank you in advance for the help (with both issues),

Daniel

 

[Dirk Goldgar]

[...]

Me.GroupLevel(N).RecordSource = "fieldname1"
Me.GroupLevel(N+1).RecordSource = "fieldname2"
Me.GroupLevel(N+2).RecordSource = "=1"

I think Marsh meant "ControlSource", not "RecordSource".

 

[Dirk Goldgar]

On a seperate note... I'm always a little confused about newsgroup
ediquette. When I have a question about programming a report (such
as the question above), should it be posted in the programming
newsgroups or the report newsgroup? Does it really matter or bother
anyone?!

That's a good question. Since there's not a "reportsprogramming"
newsgroup, IMO you would be justified in cross-posting the question to
both groups. You do that by listing both groups on the To: or
Newsgroups: line of your message, not by sending the message separately
to both groups.

 

[Marshall Barton]

[...]

Me.GroupLevel(N).RecordSource = "fieldname1"
Me.GroupLevel(N+1).RecordSource = "fieldname2"
Me.GroupLevel(N+2).RecordSource = "=1"

I think Marsh meant "ControlSource", not "RecordSource".

Thanks for catching that Dirk.

So many questions, so little time, numbs the mind ;-)

 

[Alex Dybenko]

Hi,
one more solution to add to Marsh's one:
you can base report source SQL statement, which you construct on a fly based
on what sorting field you need.

for example to sort on name:
me.recordsource="SElect *, Name as OrderField from MyQuery"

and make your report to be sorted on OrderField

now you can easy set report recordsource and change OrderField alias there
for different fields.

You can also add more orderfields, and you can change report grouping

 

[Duane Hookom]

I have used a non-vba method by creating several option groups on a form:
optgPrimary, optgSecondary,...
Each of these option groups have values 1 through whatever and display
friendly field titles.

My query then has calculated columns like:
PrimarySort:Choose(Forms!frmSort!optgPrimary, [FieldA], [FieldB],
[FieldC])
SecondarySort:Choose(Forms!frmSort!optgSecondary, [FieldA], [FieldB],
[FieldC])
etc

The report sorting and grouping contains levels based on these calculated
fields
PrimarySort
SecondarySort