Change Sort Order Based on Selection From Form?

[zulu1ghz]

I'd be grateful if anyone could answer this question.

I have a report based on a query, I'd like to be able to change the sort order of the report based on the criteria user selects on the form. The three sort options I have on the form are "Office", "Issue Reason" and "LOS".

I've been trying to see if there is a way you can get the grouping/sorting dialogue box in Access to reference those controls, the criteria on the form match the field names in my tables.

I've searched the forum and can't see what I'm looking for, any help appreicated. Thanks

 

[jebry]

Hi!

You want the GroupLevel property of the report. Assuming you have a combo box from which the user selects the field to sort on, you can use the following in the report's open event:

Me.GroupLevel(0).ControlSource = Forms!YourForm!YourControl

Please note, that a grouping level will already need to be created in the report. All you can do with this command is change the field that is being grouped. If you want to create the group level on the fly, then you will need to open the report in design view first (hidden if you want) and use the CreateGroupLevel method:

CreateGroupLevel("YourReport", "YourField", 0, 0)

The last two numbers say that you do not want a header or footer for this group level.

Hope I haven't totally confused the situation.


hth
Jeff Bridgham
bridgham@purdue.edu

 

[celtc]

DoCmd.OpenReport "ErrorReport", acViewDesign
Reports!ErrorReport.Visible = False
Reports!ErrorReport.GroupLevel(0).ControlSource = "Field Name"
DoCmd.OpenReport "ErrorReport", acViewPreview

This is some code that dynamically changes the field the sorting on a report is done on.
to avoid surplus messages, on reports on open event docmd.setwarnings false, and on close docmd.setwarnings true.

 

[MichaelRed]

I usually avoid the soloutions presented above as they involove opennin the report in design mode and then switching to either preview or normal. The alternative which I use is to add a field to the recordsource of hte report, and populate this field with the content of which ever field(s) the report is to be sorted by. The sort and group does not need to be modified, so the design view isn't ever instantiated. It is necessary to manipulate the recordsource of the report, so mine are always queries. I just change the .sql property of the querydef object (and save it) prior to opening the report.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places