Confused with grouping and sorting reports

[Archie999]

Hi All,

I have read quite a few threads here as well as some of the online help in Access but I'm still a little confused about grouping and sorting via VBA. I have so far been unsuccessful at making this work on my DB. The part that is particularily confusing is the difference between setting the ControlSource of a GroupLevel and setting the OrderBy property.

I have a report with one level of grouping (inc header and footer). I also have a form that offers the user different sort criteria. I would like the report to display sorted as per the user's input, and still maintaining my one level of grouping.

I tried putting this in the open report event: (The SetSort function simply retrieves the name of the field I would like to sort by based on users input)

Me.GroupLevel(0).ControlSource = SetSort(Me)

This did not work because it 'broke' my existing Grouping. Not surprising. So I created a new group level on the form (but opted not to create header and footer for it) and tried the same thing as above:

Me.GroupLevel(1).ControlSource= SetSort(Me)

In this case the grouping remained intact but my records were not sorted as I had asked. I have also played with the OrderBy property but it seems to have no affect. I cannot get my report so that my grouping and my sorting works.

I don't know if I have given you enough info or not... hopefully so. If not, I can email you my DB. Perhaps someone has a working example of something similar.

If anyone could explain this or point me in the right direction it would be much appreciated.

Thanks!

 

[Pat Hartman]

Grouping implies a certain sort order. You cannot group a report by state if it is sorted by customer. To group by state, the primary sort field must be state. The secondary sort field in our example could be customer.