By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,572 Members | 963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

Change report sort order in VBA..?

P: 20
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
Sep 20 '07 #1
Share this Question
Share on Google+
9 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Why not to build report with subreport?
Sep 20 '07 #2

P: 20
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.. >:(
Sep 20 '07 #3

NeoPa
Expert Mod 15k+
P: 31,485
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.
Sep 20 '07 #4

NeoPa
Expert Mod 15k+
P: 31,485
What you need to use is :
Expand|Select|Wrap|Line Numbers
  1. Me.GroupLevel(0).
  2. Me.GroupLevel(1).
  3. etc
Put the cursor on GroupLevel and hit F1 for a fuller explanation.
Sep 20 '07 #5

ADezii
Expert 5K+
P: 8,632
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  'Set the following values for the 1st Grouping Level, Level 2 would be
  3.  'Me.GroupLevel(1), Level 3 would be Me.GroupLevel(2), etc.
  4.    ' Set SortOrder property to Ascending order (SortOrder = False)
  5.     Me.GroupLevel(0).SortOrder = False
  6.     ' Set GroupOn property to Each Value (GroupOn = 0)
  7.     Me.GroupLevel(0).GroupOn = 0
  8.     ' Set GroupInterval property to 1 (GroupInterval = 1)
  9.     Me.GroupLevel(0).GroupInterval = 1
  10.     ' Set KeepTogether property to Whole Group (KeepTogether = 1)
  11.     Me.GroupLevel(0).KeepTogether = 1
  12. End Sub
Sep 21 '07 #6

P: 20
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?
Sep 21 '07 #7

NeoPa
Expert Mod 15k+
P: 31,485
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.
Sep 21 '07 #8

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

(clipped from Report_Open)

Expand|Select|Wrap|Line Numbers
  1. nStart = InStr(1, Me.OpenArgs, ";")
  2. If nStart > 0 Then
  3.     ' if there's a semi-colon in openargs, take the second param as orderby
  4.     cOrderby = Right(Me.OpenArgs, Len(Me.OpenArgs) - nStart)
  5. End If
  6. If Nz(cOrderby) = "" Then
  7.     ' default orderby for this report
  8.     cOrderby = "[DeptSort],[TitleSort],[cLName],[cFName]"
  9. End If
  10. ' make sure corderby ends with a comma
  11. cOrderby = cOrderby & IIf(Right(cOrderby, 1) <> ",", ",", "")
  12. nStart = 1
  13. nEnd = 1
  14. For nIdx = 0 To 7
  15.     ' sorting by name is actually by two fields, so we only have 8 sort fields to work with
  16.     nEnd = InStr(nStart, cOrderby, ",")
  17.     If nEnd > 0 Then
  18.         cFld = Mid(cOrderby, nStart, nEnd - nStart)
  19.         nStart = nEnd + 1
  20.     End If
  21.     Me.GroupLevel(nIdx).ControlSource = cFld
  22. Next nIdx
  23.  
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.
Sep 21 '07 #9

NeoPa
Expert Mod 15k+
P: 31,485
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 :)
Sep 22 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.