Is there a way to use an option group on a form to open a report with the data in either ascending or descending order?

Is there a way to use an option group on a form to open a report with the data in either ascending or descending order in the detail section of the report?
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Yes, but we'd need to know more about the report.

Is it grouped? If so, then grouping always takes precedence over datasource filtering, or the Filter argument of the Report. You can change the Field your Group is based on through code, however, but that often makes the report oddly formatted.

If it's not grouped, then you can set the Orderby argument in the Open even of the report. You have to do a little magic to do this, but it's not too difficult:

In the Open event of the form, use code like this:

If Forms("FormWithYourOptionGroup").YourOptionGroup = 1 Then
  Me.OrderBy = "SomeField DESC"
Else
  Me.OrderBy = "SomeField"
End If

Me.OrderByOn = True

As stated, however, if your report is grouped this won't have any effect.

And obviously you'd have to change that code to reflect the values in the OptionGroup. If you've given the Option of "Descending" a value of 2, for example, you'd have to change the code to look for that.
0
 
SteveL13Author Commented:
There is no grouping on the report.  I'll try this.  

You wrote, "In the Open event of the form, use code like this:" but I assume you meant open event of the report, correct?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes - in the Open event of the REPORT. Sorry about that.
0
 
SteveL13Author Commented:
No problem.. I understood and accepted your solution.  Thanks much!
0
All Courses

From novice to tech pro — start learning today.