Passing a user selected sort option to a report via a form in code

[Mac R]

Hi, this may already be answered somewhere, it may be that I just don't know how to search for it and find the solution.



I have a form/sub-form that allows the user to interact with various selection criteria and sort options to produce exactly what they wish to see. Here is the form as it opens with no selections and no sort.



As the user interacts, the form 'updates' based on an event procedure for each update-able control. So, for example, if the user selects a certain supplier, the 'update' kicks off after the update of the supplier field, and re-displays the results. The event "re-builds" the subform's data source in SQL in three stages, Select, Where and OrderBy to produce what the user is asking for, then concatenates the three strings into one SQL statement that is used as the subform's record source and re-querries the sub-form displaying the data. This process works flawlessly, including the sort options (on the form). Example...



The user has options to open a report (PDF) or export to Excel (xlsx) based on the records selected, and the current logic works flawlessly exporting only the records on screen. The process to open the report is this;

Private Sub Command50_Click()

RefreshDisplay

Dim varWhere As String

If SQLWhere <> "" Then

varWhere = Right(SQLWhere, Len(SQLWhere) - 5)

End If

DoCmd.OpenReport "r_item_master_report", acViewPreview, , varWhere

End Sub

Where RefreshDisplay is a function that re-builds the SQL statement and re-querries the subform, and the variable SQLWhere is a declared modular variable containing the string for selection criteria used in the RefreshDisplay function. This routine grabs the "SQLWhere" part of the SQL build process, removes the "Where" from it, and uses it as the 'criteria' argument for opening the report. This all works exactly as planned.

My question is passing the SORT criteria the user selects to the report open process, which, by using DoCmd OpenReport, doesn't work, as there are no options to pass a sort using this command. I know what the sort is based on the variable SQLSort used in the RefreshDisplay function. Using the above example it's...


Order By [USalesLY] Desc

Exporting to Excel isn't an issue as the user could do their own sorting, but exporting to PDF doesn't pass the user selected sort.

I've thought of (via code) copying the existing 'standard' report to a temp report name and then modifying it as an object based on the user's sort selction(s), then opening the report, then deleting the temp report once the displayed report was closed, but that process seems like a very long, long road 'round the problem. In a little reading here, I see that maybe doing the same thing but maybe updating the sort, like updating a filter?

Am I missing something? is there an easier way? Better yet IS there a way?

As always, appreciate thoughts from y'all...

P.S. sorry about the size of the graphics... I had no idea they would be that big!

[Welshgasman]

Reports take no notice of the sort order of the data. Has to be done within the report. So perhaps google on 'sort access report vba'

[Welshgasman]

Here is one such link
https://learn.microsoft.com/en-us/of...form-or-report

[Gicu]

Here is a sample of a dynamic report I used to have in some of my apps. While the sample does not do the specific task to inquire about (changing the sort order) you can apply the same technique to add it. You would need to manipulate the

Reports.Item(strReport).GroupLevel(i).SortOrder

instead of the

Reports.Item(strReport).GroupLevel(i).ControlSource

as I am doing.

Cheers,