Sorting records using Grouplevel.Controlsource

[sjdk]

Ok, I just don't get how this works....

I have a form called frmOptions that allows the uer to select (among other things) the sort order. If they select sort A from the combobox, the report should be sorted by Seg1, Seg2, Obj, Seg4, Seg5. Sort B would be Seg1, seg3, seg2, obj, seg4. WHen the report opens using sort A, I want it sorted, and then group headings for Seg2, a group footer for obj, and a group footer for seg2. When the report opens for sort B, I want it sorted, and then group headings for seg3, group footer for obj, group footer for seg3.

In the report, I used the 'sorting and grouping' button to put in group headings/footers for each of the sort items.

In the on open event of the report, I have the following...

Select Case Forms!frmOptions!cboSort
Case "A"
Me.GroupLevel(0).ControlSource = "glma_seg1"
Me.GroupLevel(1).ControlSource = "glma_seg2"
Me.GroupLevel(2).ControlSource = "glma_obj"
Me.GroupLevel(3).ControlSource = "glma_seg4"
Me.GroupLevel(4).ControlSource = "glma_seg5"
Case "B"
Me.GroupLevel(0).ControlSource = "glma_seg1"
Me.GroupLevel(1).ControlSource = "glma_seg3"
Me.GroupLevel(2).ControlSource = "glma_seg2"
Me.GroupLevel(3).ControlSource = "glma_obj"
Me.GroupLevel(4).ControlSource = "glma_seg4"
End Select

End Sub

I guess the problem is that I really don't fully understand how this whole thing works. Can some kind soul explain to me in SIMPLE english how this works, if I can do what I want to, and where I went wrong?

At this point, when I open the report I am not getting all of the data, and I can tell that the sorting/grouping isn't working properly.

Any assistance would be greatly appreciated.

 

[dhookom]

Your report should have 5 levels of sorting pre-defined and saved. These levels are numbered 0-5. Your code should be re-defining the sort order when the report opens.

The code should have no effect on "getting all of the data". You can test this by placing a text box in the Report Header or Footer with a control source of:
=Count(*)
Different sorts should not have an effect on this number.

What makes you think the sorting and grouping isn't working properly?
What are the significant properties of the combo box?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sjdk]

Thanks for the response!

In playing with it more, I realized that I had accidentally made a group heading invisible...thus it appeared some data was missing.

I think that the sorting was working properly. I just couldn't get the group headers/footers to show what I wanted them to. Sort A worked fine, but I wonder if that is because when I created the report, in the 'Sorting and Grouping' dialog box, I set it up for sort A. Then, I tried to add the code (above...) to the on open event. Sort B didn't come out the way I wanted it to. Honestly, I can't seem to figure out what it did. It did sort by seg1, then it was supposed to sort/group by seg3. It didn't do that. I think things were also complicated because Sort A had less subtotals than Sort B.

I kept searching tech tips and did finally find somewhere that said you can programmatically change the sorting, but not the grouping. So, I have decided to take a different approach to this problem. I suppose it is the 'lazy' way to do it, but I am using radio buttons to select the sort, then I have a preview button that, when clicked, runs a select case code to choose the appropriate report. (either rptSummarySortA or rptSummarySortB).

Thanks a million for taking the time to look at my problem!

 

[dhookom]

Changing the ControlSource of the Levels will not change the Group Headers/Footers. Also, the controls in the headers and footers will not change by changing the levels. Using code to update text box control sources can be done so you only need to maintain a single report.

However, if you are having success with creating multiple similar reports then go with it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sjdk]

Ok, what you said makes perfect sense! That is the answer I was looking for! Right now the multiple reports is working, however, I think I will try (with less complicated reports!) using code to change the sorting, and use code to update the textbox control sources.

Again...many thanks!