Subreport sorting on the fly

[AngeloO]

I have a subreport that is being used by two groups, therefore, it needs to be sorted two different ways.
I tried using "orderby" and got errors.
I also tried to use Me.GroupLevel(0).ControlSource = "Parent" coding. I also got errors with that.
I can work these sorts fine in a report. It's the subreport aspect that has me stymied.

Any thoughts would be appreciated!!!

Thanks!! Ang

 

[hneal98]

It would help more if you could give examples of what you are trying to do and also actually write the errors you were getting.

I know in Crystal reports you can set up variables to allow you to sort on various way and you just sort by the variable; but I am sure Access does not have that capability. You would probably have to set something up either through the source query or through VBA.

 

[dhookom]

I would change the value of a column in the subreport's record source based on a value in a form. Then sort on the "dynamic" column in the subreport's sorting and grouping dialog.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya AngeloO . . .

Are you sorting on [blue]more than one field[/blue] on the subreport?

In any case for sorting on a single field, in the [blue]On Open[/blue] event of the [blue]subReport[/blue], try one of the following:

[blue]   Me.GroupLevel(0).SortOrder = False [green]'Ascending[/green]
   Me.GroupLevel(0).SortOrder = True  [green]'Decending[/green][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

AngeloO . . .

Hit submitt too soon.

Be sure you have the right [blue]grouplevel index![/blue] Just count headers fron the top in report design view.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SeeThru]

I have alos created a calculated field in the subform data source - the calculated field varied depending on how the report is opened - i.e It pulls information from one of three different fields (using Iif) depending on the report type.

You then have the calculated fiel as the sort order for the report.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[AngeloO]

Thanks so much for all the replies. I am including the code I thought should work which was very similar to TheAceMan1's suggestion.

The keys are this is a subreport and the third sort item changes.

I put this in the "On Open" of the subreport and it errors out on the first sort line of the case statement (Me.GroupLevel(0).ControlSource = "Parent").
Here is the error...
Run-time error '2191':
You can't set the Control Source property in print preview or after printing has started.

I tried setting these back in the form before the report opened, but that didn't work either.

Here is the code:

Select Case Forms!frmAssetRequired!og_Report_Sort

 Case 1 'Location - Order #
        Me.GroupLevel(0).ControlSource = "Parent"
        Me.GroupLevel(1).ControlSource = "Child"
        Me.GroupLevel(2).ControlSource = "ld_loc"
        Me.GroupLevel(3).ControlSource = "ld_qty_oh"
        Me.GroupLevel(4).ControlSource = "ld_part"
        Me.GroupLevel(5).ControlSource = "Serial_Num"
        Me.GroupLevel(0).SortOrder = False
        Me.GroupLevel(1).SortOrder = False
        Me.GroupLevel(2).SortOrder = False
        Me.GroupLevel(3).SortOrder = True
        Me.GroupLevel(4).SortOrder = False
        Me.GroupLevel(5).SortOrder = False
    Case 2 'Expected Ship Date
        Me.GroupLevel(0).ControlSource = "Parent"
        Me.GroupLevel(1).ControlSource = "Child"
        Me.GroupLevel(2).ControlSource = "Expected Return Date"
        Me.GroupLevel(3).ControlSource = "ld_qty_oh"
        Me.GroupLevel(4).ControlSource = "ld_part"
        Me.GroupLevel(5).ControlSource = "Serial_Num"
        Me.GroupLevel(0).SortOrder = False
        Me.GroupLevel(1).SortOrder = False
        Me.GroupLevel(2).SortOrder = False
        Me.GroupLevel(3).SortOrder = True
        Me.GroupLevel(4).SortOrder = False
        Me.GroupLevel(5).SortOrder = False
    End Select

og_Report_Sort is an option group in a form with two radio buttons.

The other thing I tried was this type of code using Orderby. It worked very nicely for a report I did, but I can't seem to get the syntax for the subreport.

Reports![rptAssetsRequired_Summary]!
[rptAssetsRequired_QADOH].report.orderby = "[Status], [Expected Ship Date], [Order#]"

Reports![rptAssetsRequired_Summary]!
[rptAssetsRequired_QADOH].report.orderbyOn = true 
Those were my two best attempts.

The idea of a dynamic field is intriguing (SeeThru and dHookom's solution). I'm not quite sure how it works though.

Again, thanks so much for you input.

Ang

 

[dhookom]

Can you provide the SQL view of your subreport's record source as well as what control value determines which of the "two groups"?

Duane
Hook'D on Access
MS Access MVP

 

[AngeloO]

Yes I can supply that info.

the subreport is based on a table, and the Table is based on a query. I end up doing all my grouping and sorting in the report. The default is Case 1 of my code.

And the Case Select is based on two radio buttons:
Case 1 - we want to sort on Me.GroupLevel(2).ControlSource = "ld_loc" (which is actually an order number)
Case 2 - we want to sort on Me.GroupLevel(2).ControlSource = "Expected Return Date"

I hope that helps

Ang

 

[dhookom]

I was expecting you to provide the name of the radio option box as well as the form name. However making some assumptions, your subreport's record source could be something like:

SELECT Choose(Forms!YourFormName!optYourOpt,[ld_loc],[Expected Return Date]) as SortColumn, TableName.*
FROM TableName

Then, in your report, use the SortColumn where you would have used either ld_loc or [Expected Return Date].

Duane
Hook'D on Access
MS Access MVP

 

[AngeloO]

Thanks!! I see what you're getting at. I never thought of doing it like that. I'll give it a shot!!

Thanks so much for your help!!

Ang