Sort Report based on Option Group on Form

[Design by Sue]

I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. I am stumped on how to write the code for the sorting. Any suggestions?

Thanks
Sue

 

[jdraw]

See this article

 

[Design by Sue]

Thanks - this is new to me so here is another question - how do I define the GroupLevel or set it's property to 0? I found the following which I think applies but can't figure out how to do it.

"If a group is already defined for a report (the GroupLevel property is set to 0), then you can use the ControlSource property to change the group level in the report's Open event procedure."

 

[Design by Sue]

I am asking because I am getting the following error message (2464)

"There is no sorting or grouping field or expression defined for the group level you used."

So is it the GroupLevel part of the code giving me the problem or is it the rest?

Here's my code:

Select Case Forms!DateRangeFRM!SortBy
Case 1 'Date
Me.GroupLevel(0).ControlSource = "QTDate"
Case 2 'Line
Me.GroupLevel(0).ControlSource = "Line"
Case 3 'Observation
Me.GroupLevel(0).ControlSource = "Observation"
Case 4 'Description
Me.GroupLevel(0).ControlSource = "Description"
End Select

 

[jdraw]

I haven't used the technique. I have seen it referenced.
Here is the original knowledgebase article.
http://support2.microsoft.com/kb/q146310

I know that Reports have their own sorting and grouping, so using a query to Order data for a report is not going to work.

I have a lot of respect for Pat Hartman who made this comment in a similar situation.

If you want to change the report's sorting option, change it in the report's Open event rather than in the query. You can use global variables to store the order by clause or you can pass it to the report by using the OpenArgs argument of the OpenReport Method. Do NOT attempt to change the report from outside of the report, you would need to open it in design view and then save the design change. This will NOT work in a multi-user environment and besides, the OpenArgs requires less code.

 

[Design by Sue]

I followed the steps in your support link and it is more complex that what I need (I do not need extra levels of sort, just sort on one field) So I adapted it as best I could and only got part success. Problems are:

1. I need to limit the selection to 4 of the fields - not all of them

2. I only need one sort not five as they show (though I was able to overcome that)

3. And the biggest problem. the report does open in design view behind the form as it is supposed to but when I click the button on the form the report does not switch to the correct view. I reread the instructions over and over and do not see where it is to switch the view to preview.

4. If I manually switch the view to preview it appears that the sort is working but does in it descending order not ascending order.

Because this method is much more than what I need - does anyone else have any suggestions?? or way to make this code work for me??

 

[Design by Sue]

I found the solution. For others here is what I did:

On the form that allows the users to select the date range for which they want the report I created a combo box. For the Row Source Type I selected value list and in the Row Source, I listed the 4 choices. (limit to list and not allow edits). I created 4 queries, one for each of the sort orders I wanted, one for each of the choices in the combo box. Then on my control button on the form I set the record source for the report based on the selection in the combo by using an if/else statement

If Forms!SelectFormName.ComboName = "Line" Then
  Me.RecordSource = "LineSortQRY"
  Else ...
End If

This works exactly as I wanted.

Thanks for any responses.

Sue