Controlling report sorting using vba

  • Thread starter Thread starter Daniel
  • Start date Start date
D

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
 
Daniel said:
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?!
Click to expand...

Set the report's Sorting and Grouping to sort on the maximum
nuber of fields the users will want to use. Once the
sort/group levels exist, you can use code in the report's
Open event to change them:

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

The last one can use any constant expression to effective
"disable" the sorting at that level.

Check Help on GroupLevel for details.
 
[...]
Me.GroupLevel(N).RecordSource = "fieldname1"
Me.GroupLevel(N+1).RecordSource = "fieldname2"
Me.GroupLevel(N+2).RecordSource = "=1"
Click to expand...

I think Marsh meant "ControlSource", not "RecordSource".
 
Daniel said:
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?!
Click to expand...

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.
 
[...]
Me.GroupLevel(N).RecordSource = "fieldname1"
Me.GroupLevel(N+1).RecordSource = "fieldname2"
Me.GroupLevel(N+2).RecordSource = "=1"
Click to expand...
Click to expand...
Dirk said:
I think Marsh meant "ControlSource", not "RecordSource".
Click to expand...


Thanks for catching that Dirk.

So many questions, so little time, numbs the mind ;-)
 
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
 
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
 
Back
Top