Results 1 to 4 of 4
  1. #1
    Mac R is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2022
    Location
    Maine, USA
    Posts
    39

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

    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.

    Click image for larger version. 

Name:	Item master no selections or sorts.PNG 
Views:	11 
Size:	110.3 KB 
ID:	49466

    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...

    Click image for larger version. 

Name:	Item master with selection and sort.PNG 
Views:	11 
Size:	118.4 KB 
ID:	49468

    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!
    Attached Thumbnails Attached Thumbnails Item master with selection no sorts.PNG  
    Last edited by Mac R; 01-08-2023 at 04:21 PM. Reason: Tried to get rid of last graphic, but couldn't!

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    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'
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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
    Code:
    Reports.Item(strReport).GroupLevel(i).SortOrder
    instead of the
    Code:
    Reports.Item(strReport).GroupLevel(i).ControlSource
    as I am doing.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 07-05-2017, 09:00 AM
  2. Replies: 3
    Last Post: 09-19-2016, 06:46 AM
  3. Replies: 5
    Last Post: 04-19-2016, 04:07 PM
  4. Replies: 1
    Last Post: 12-05-2014, 01:06 PM
  5. Replies: 9
    Last Post: 10-12-2011, 02:34 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums