Change group and sort options via VBA? (1 Viewer)

JGalletta

Windows 7 Access 2010
Local time
Today, 04:32
Joined
Feb 9, 2012
Messages
149
I have a report currently grouped by one field name and sorted by a few others. What's the best way to change the group and sort options via VBA so I can make group/sort buttons for each field on the form? - OR - What are other methods of accomplishing this?
 

RainLover

VIP From a land downunder
Local time
Today, 19:32
Joined
Jan 5, 2009
Messages
5,041
There is a Grouping/Sort option in the design view of the Report.

This overrides anything you may have in the recordsource.

I am using 2003. Don't know about later versions but I would assume they have something similar.
 

RainLover

VIP From a land downunder
Local time
Today, 19:32
Joined
Jan 5, 2009
Messages
5,041
Suggest you plce your Windows Version and Access Version in your Signature.
 

JGalletta

Windows 7 Access 2010
Local time
Today, 04:32
Joined
Feb 9, 2012
Messages
149
This might provide some idea...

http://allenbrowne.com/ser-33.html

To Anyone:

Would the OrderBy property sort faster?

Like the creator of the tip in the quote above, I'm sorting by multiple fields. This takes quite some time (15s +) with a small amount of sample records. I've programmed a waiting dialog into this event, but if there is a faster way, I'd definitely prefer it.

Thanks in advance,
John
 

vbaInet

AWF VIP
Local time
Today, 08:32
Joined
Jan 22, 2010
Messages
26,374
If this is faster, how do you sort descending?
You will need to give both a try to see which one works better for you. GroupLevel has SortOrder method. Look into that.

If you want to use the other method you would use this in the Open event of the report:
Code:
Me.OrderBy = "ID DESC"
Me.OrderByOn = True

Now I would imagine that if your records are sorted in the Record Source it will appear in that same order in the report. But like RainLover mentioned, if you apply a Sort in the Report itself, it will override the sorting in the Record Source.

So the quickest way is, remove any Grouping or Sorting you have in the report, sort and/or group in the Record Source. But note that if you want to do something like Group on each category and Force a New Page per category, this method won't work. You will need to do it via one of the report's sorting/grouping methods.
 

JGalletta

Windows 7 Access 2010
Local time
Today, 04:32
Joined
Feb 9, 2012
Messages
149
Ok, good. Very informational. Thanks.
 

Users who are viewing this thread

Top Bottom