[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How sort records in a subreport based on the selection in a combobox on a form

Posted on 2014-08-01
Medium Priority
Last Modified: 2014-08-03
Is there a way to sort the records in the detail section of a subreport based on the selection in a combobox on a form.

There are three choices:  Category, Location , and Room.  Each of this is a field in the query which is the reports recordsource.

I don't know if some code in the onopen event of the report would work?

Question by:SteveL13
LVL 31

Accepted Solution

Helen Feddema earned 1000 total points
ID: 40235214
I would do the sorting (and filtering, if that is needed) in code running from a control that opens the report, such as the Report button on my New Style Main Menu sample database.  Create a saved query using the selected sort, and make that query the record source of the subreport.  Here is a link for the Access Archon article featuring this database:
and here is a screen shot of the main menu:
New Style main menu
LVL 58
ID: 40235284
You'll have to do it in the OnOpen of the report.  Report's ignore any sorting in an underlying data source.  Set the OrderBy property, and the OrderOnLoad property to True.

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 total points
ID: 40235319
Something like this worked OK for me...

Obviously there will be other ways to do this, ...but sometimes I just like to manipulate the sorting in the Report's recordsource
 (if no grouping levels are present)
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 85
ID: 40236026
Note too that sorting is impacted by Groups as well - so if you're Grouping by FieldA but tell Access to Sort by FieldB, then your records would show on the form "sorted" for FieldA, and for each of those Groups, the records would then be "sorted" by FieldB.

Author Comment

ID: 40236164

I mistakenly accepted your suggestion before I realized that I could not download your example.  Could you report it for me please?

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40236668
<I could not download your example. >
Could not download it, ...or could not open it...?

In any event here it is again, in access 2003 mdb format
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40236671
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40237355

Note that I used the recordsource here.

What the other experts posted about using the OrderBy Property is the more used technique.
For whatever reason,  I was having trouble with Ordeby in a SubReport, (opened by a form)

So my old fallback is to just grab the Recordsouce and sort that instead.
What this meas is that you ever add a sort in the report itself, the recordsouce sort will be ignored (as Jim States)

Finally note that this may all change if your report includes grouping levels.

So play around with this and also play around with Ordeby.
again, I can think of about half a dozen approaches to this...


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question