Change report sort order in VBA..?

[thatguyNYC]

Hey there--

I have a report with a one-to-many relationship on it. The EmployeeName field is grouped (by primary key) and shown in its own header and the EmployeeHistories are displayed in the detail section. Above the EmployeePkey grouping, I have several sorts - Dept, Title, Location and FullName.

Everything is working fine, until one day my boss asks if she can have different sort options. One day, she may want a report sorted by FullName only, or by Title and Dept, or by Location only.

Can anyone suggest a way to accomplish this? I've made up a form to allow her to select the different sort fields, but passing those in as an ORDER BY clause is obviously pointless if the report has those group/sort fields set.

Any thoughts?

Thanks

[FishVal]

Hi, there.

Why not to build report with subreport?

[thatguyNYC]

Why not to build report with subreport?

I started out with a subreport, but for some reason couldn't get it to work.. After about two days of screaming, I gave up. But I just started from scratch on the subreport idea again and somehow it's working now. The parent report has no grouping/sorting tied to it - that's all set by the orderby clause, which I pass in thru openargs.

I love Access.. >:(

[NeoPa]

Sorting/Grouping is a bitch to find out about but I managed to find the relevant info once. I'll dig it up. I found it extremely useful.

[NeoPa]

What you need to use is :

Me.GroupLevel(0).
Me.GroupLevel(1).
etc

Put the cursor on GroupLevel and hit F1 for a fuller explanation.

[ADezii]

Sorting/Grouping is a bitch to find out about but I managed to find the relevant info once. I'll dig it up. I found it extremely useful.

Private Sub Report_Open(Cancel As Integer)
 'Set the following values for the 1st Grouping Level, Level 2 would be
 'Me.GroupLevel(1), Level 3 would be Me.GroupLevel(2), etc.
   ' Set SortOrder property to Ascending order (SortOrder = False)
    Me.GroupLevel(0).SortOrder = False
    ' Set GroupOn property to Each Value (GroupOn = 0)
    Me.GroupLevel(0).GroupOn = 0
    ' Set GroupInterval property to 1 (GroupInterval = 1)
    Me.GroupLevel(0).GroupInterval = 1
    ' Set KeepTogether property to Whole Group (KeepTogether = 1)
    Me.GroupLevel(0).KeepTogether = 1
End Sub

[thatguyNYC]

I played around with GroupLevel, but it would seem that you would need a number of predefined GroupLevels above the Employee.pkey grouping (which has a group header), and then redefine each GroupLevel(x).ControlSource on Open(), based on the user sort selection..

GroupLevel(0).ControlSource = "[cDept]"
GroupLevel(1).ControlSource = "[cTitle]"
GroupLevel(2).ControlSource = "[cFullName]"
GroupLevel(3).ControlSource = "[pkey]" (+ header)

The trouble is, how many GroupLevels should there be? What if the user wants a really complex sort based on 5 fields? What if they only need 1 sort field? I looked at the CreateGroupLevel method, but I saw that the report needs to be in design view to use that method, so I decided to try to the subreport route.

The problem now (using the subreport), is that I can't [easily] filter the records in the subreport from the main report. For example, I can't show only the Employee Histories within a date range; I can only show Employees that have a History within that date range, but the report shows all of the Employee's Histories, regardless of date. When the Histories were in the detail section, I had that option. Instead, I'll have to parse out the fields in the Where clause that relate to the Histories and pass that clause onto the subreport.

I guess with Access, you can never have it all?

[NeoPa]

Define the maximum number of levels and simply set the unnecessary ones to the same as the one at the next higher level.
Don't think this is simple design though. Not for the faint of heart.

[thatguyNYC]

So here's the solution that seems to work...so far...

(clipped from Report_Open)

nStart = InStr(1, Me.OpenArgs, ";")
If nStart > 0 Then
    ' if there's a semi-colon in openargs, take the second param as orderby
    cOrderby = Right(Me.OpenArgs, Len(Me.OpenArgs) - nStart)
End If
If Nz(cOrderby) = "" Then
    ' default orderby for this report
    cOrderby = "[DeptSort],[TitleSort],[cLName],[cFName]"
End If
' make sure corderby ends with a comma
cOrderby = cOrderby & IIf(Right(cOrderby, 1) <> ",", ",", "")
nStart = 1
nEnd = 1
For nIdx = 0 To 7
    ' sorting by name is actually by two fields, so we only have 8 sort fields to work with
    nEnd = InStr(nStart, cOrderby, ",")
    If nEnd > 0 Then
        cFld = Mid(cOrderby, nStart, nEnd - nStart)
        nStart = nEnd + 1
    End If
    Me.GroupLevel(nIdx).ControlSource = cFld
Next nIdx
 

If anyone sees a problem here (that hasn't shown itself in my testing yet), please feel free to mention.

And thanks for all the replies. This solution will allow user-defined sorting and also filters on the child table -- the best of both worlds...eventually.

[NeoPa]

I'm really pleased for you.
Clever coding like that is always rewarding and going the extra to achieve that deserves a pat on the back :)