Report Re-Sorting Command

[gbwiii]

My report is sorted using a query by Work Center, but it would be great if at the bottom of the report I could click on a button and have it resort the report by Aircraft Tail Number. Open to suggestions, my programming skills are limited !



Thanks

[ItsMe]

OrderBy does not seem to be available after the report is opened. I can only get it to work as the report loads, in the load event.

[gbwiii]

Yes - I am trying to resort a report not a form

[ItsMe]

Sorry, typo. I meant to say report. Once the report opens the only function that seems to be available to alter the report is filter.

Up until today I was unaware that you could even do that or place a control button on a report. Quite frankly, I am befuddled.

[pbaldy]

The report would have to be in Report view (new in 2007). I do this type of thing:

  Select Case Me.FraSort
    Case 2
      Me.BreakDownSheet.Report.GroupLevel(0).ControlSource = "SortOrder"
    Case Else
      Me.BreakDownSheet.Report.GroupLevel(0).ControlSource = "Car_No"
  End Select

[gbwiii]

Thanks - Will keep searching !

[pbaldy]

Looks like we were posting at the same time, so wanted to make sure you saw post 5.

[gbwiii]

Yes - I see it now but I am trying to make sense of it. So if I create a command button on my report and then on the Click Function enter this code.

[pbaldy]

Mine is used with an option group in the main report changing a subreport. You can certainly use a button, I was just showing what property you'd change.

[gbwiii]

Thanks - I will try the options group also

[ItsMe]

Paul

Have you gotten it to work on anything other than a subreport? I must still be befuddled.

[pbaldy]

If you really have 2003, it uses a view you haven't got. Report view, new with 2007, allows interactivity like buttons and such.

[ItsMe]

Paul,

I don't want to Hi-Jack the OP's thread. I tried it out in Access 2010 and could not get it to work anywhere other than the load event. That is when I posted with Post # 2. I read the help files and they mentioned Grouplevel was only good at the time the report loaded.

Later I will try again and duplicate what you have there. Although I have had Access 2010 for over 3 years, I am just now starting to learn it. Hence, I am often befuddled.

[pbaldy]

Make sure it's talking about Report view rather than Preview/Print. I would certainly agree that the above code would only work at the time the report loaded with Preview or Print. The db I copied that from is 2007, but I'd think 2010 would work similarly. I can try it with 2010 tomorrow on a VM. If memory serves, I used a report/subreport so that the various option groups would remain at the top, allowing users to change the filtering/sorting of the subreport at will. That code is all in a function called from the click events of the option groups. The function sets the record source of the subreport, then the group level as above. It has worked perfectly for years.

[ItsMe]

I had the Report View vs. Preview thing correct. Not sure what exactly was going wrong but I built out a different report/subreport and got it to work on both straight report and on the subreport. Very cool indeed.

I like the idea of having the case select with an option group in a Main report to control sort order in a subreport. Anyway, tested the following in basic click event.

for a subreport
Me.SubReportContainer.Report.GroupLevel(0).Control Source = "TextControlName"

or


in the haeder of a report
Me.GroupLevel(0).ControlSource = "TextControlName"