Change group and sort options via VBA?

[JGalletta]

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]

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]

Suggest you plce your Windows Version and Access Version in your Signature.

 

[sxschech]

This might provide some idea...

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

 

[JGalletta]

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

 

[JGalletta]

This might provide some idea...

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

If this is faster, how do you sort descending?

 

[vbaInet]

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:

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]

Ok, good. Very informational. Thanks.

 

[vbaInet]

No problemo!