473,574 Members | 5,835 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Change report sort order in VBA..?

20 New Member
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 EmployeeHistori es 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?

Sep 20 '07 #1
9 20799
2,653 Recognized Expert Specialist
Hi, there.

Why not to build report with subreport?
Sep 20 '07 #2
20 New Member
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
32,564 Recognized Expert Moderator MVP
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
32,564 Recognized Expert Moderator MVP
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
8,834 Recognized Expert Expert
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
20 New Member
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).C ontrolSource on Open(), based on the user sort selection..

GroupLevel(0).C ontrolSource = "[cDept]"
GroupLevel(1).C ontrolSource = "[cTitle]"
GroupLevel(2).C ontrolSource = "[cFullName]"
GroupLevel(3).C ontrolSource = "[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 CreateGroupLeve l 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
32,564 Recognized Expert Moderator MVP
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
20 New Member
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
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...eventu ally.
Sep 21 '07 #9
32,564 Recognized Expert Moderator MVP
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

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

Similar topics

by: Nathaniel Price | last post by:
I'm new to this list, so I'm not sure if this is the right place to post this. If not, please direct me to where it would be better to post it. Anyway, I'm creating a report generation tool of sorts in PHP for a database. As part of this report generation tool, I'd like to allow the user to reorder these results arbitrarily. In other words:...
by: Mike MacSween | last post by:
I have a report based on: PARAMETERS Forms!frmProductions!ProdID Long; SELECT Sum(qryRoleEvent.Fee) AS SumOfFee, First(qryMusician.LastName) AS LastName, First(qryMusician.FirstName) AS FirstName FROM (qryEvent INNER JOIN qryRoleEvent ON qryEvent.EventID = qryRoleEvent.EventID) INNER JOIN qryMusician ON qryRoleEvent.MusoID =...
by: Mike MacSween | last post by:
tblProductions one to many to tblEvents tblEvents contains StartDate I want a report where the data are grouped by tblProductions.ProdID, and sorted by the earliest date in each Production. So this as a query (I've stripped out surplus fields): SELECT qryEvent.EventName, qryProduction.ProdName, qryEvent.ProdID,
by: Rene | last post by:
I have another post regarding this "attendance" report of mine...this one concerns one of my sub-reports. It loses its alphabetical sorting when inserted in the master report. When run separately, it's perfect. Once inserted and printed in the master report...the data is in a random order. Any suggestions?
by: James P. | last post by:
Hello, I have a report with the Priority field is used as sort order and grouping. The problem is the data in this Priority field if sorted in ascending order is: High, Low, and Medium. How could I sort it as: Low, Medium, High? Any suggestion is greatly appreciated, James
by: Mike | last post by:
Hello All, I have a report based upon a query. I have added a control to report footer that calcs the total cost of the inventory: =SUM(). When this total calculation is NOT on the report, the report displays as it should in ascending order by inventory number (inv_num). 1102 1103
by: fecket | last post by:
The following code is used in my db to change the sort order of the report to various fields. I want to take this one stage further and use maybe a Case Select to give 2 or 3 different options to sort the report. Is ther any way I can adress the report name as is used in strDocName and strSQL so instead of having Reports!.OrderBy =...
by: Student at college | last post by:
Something is wrong with my report in Access. In the query for the report I have an order by clause. However, when I run the report, it comes out in a different order. Is there something else that controls the report ordering of the detail section that I am not considering? Thank you.
by: OldBirdman | last post by:
On a Report, can I change any of the fields in the "Sorting and Grouping Dialog" from within Visual Basic??? Changing the OrderBy property of the Report doesn't change anything, nor does adding an "ORDER BY" clause to the RecordSource SQL Statement. I have a table, named tInventory. It contains a text field called Item. There may be more...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.