Results 1 to 8 of 8
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    Ascending, descending for reports

    Hi,
    I have a dialogue form with two option group (Ascending, descending), and the second option group for viewing the data as a table or as a print preview report. The user press a command button after selecting either ascending or descending for sorting the records in the report.



    How should I write my VBA code for the ascending / descending option?

    sincerely yours
    Khalil

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Programmatically managing sort order of report is not simple.

    If report has Sorting & Grouping settings in design, those will override any sort order in design.
    Last edited by June7; 08-08-2020 at 01:06 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Thank you for the reply.
    Can other alternative be sorting the query which is the record source for the report. So the query is sorted ascending or descending ?

    Khalil

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can other alternative be sorting the query which is the record source for the report. So the query is sorted ascending or descending ?
    No - as June says, reports ignore any sorting applied to the recordsource

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just tested with a simple report that does not have any Sorting & Grouping set. Still ignores query ORDER BY in both PrintPreview and ReportView. Did another test of setting OrderBY and OrderByOnLoad in design and it does work for both.

    However, only way I know to programmatically change sort order is to first open in DesignView, modify property, save report, reopen in Print or Report view.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can set the sorting as the report opens. Here's an example, in the report's open event. Sorting and grouping is set up on a field, this modifies it based on a selection made by the user.

    Code:
      If Forms!frmTripSheetMain.txtSort = 1 Then
        Me.GroupLevel(0).ControlSource = "DriverName"
      Else
        Me.GroupLevel(0).ControlSource = "DriverNum"
      End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Tried adding DESC and triggers input popup. So must need something else to specify descending. The following worked on a report that does not have Sorting & Grouping setup:
    Code:
    Private Sub Report_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then Me.OrderBy = Me.OpenArgs Me.OrderByOn = True End If End Sub

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You set SortOrder to True (descending) or False (ascending):

    Me.GroupLevel(0).SortOrder = True
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 11
    Last Post: 11-06-2019, 07:03 PM
  2. Replies: 5
    Last Post: 08-27-2019, 08:41 AM
  3. Replies: 2
    Last Post: 12-10-2018, 08:22 PM
  4. Me.SortBy descending not effective
    By GraeagleBill in forum Forms
    Replies: 3
    Last Post: 09-14-2016, 02:02 PM
  5. Replies: 7
    Last Post: 03-01-2013, 01:02 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