Results 1 to 12 of 12
  1. #1
    xboxown is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    28

    How to sort ascending/descending behind the scene?

    Hey guys,

    I have full written a full functioning a report and well designed form but I want the user to be able to select under the form before clicking display report button the column name and sort type they want to display in the report when they click that button. For example, in the report it have 3 columns (as an example) one is called Item, second is called Item Description and finally third it is called SKU. I want in the interface (form) in access that they user want to sort by Item Description first, followed by Item then by SKU ascending. Then they click the button and it generates the report where it is ascending first by Item Description, then Item, then SKU. When the user is satisfied with the result they want to display the same report but instead they want the sorting to be like this:

    SKU, Item and Item Description as descending.

    How do I do that in the VBA side of the coding?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I do this type of thing in the open event of the report. It changes the field specified in Group & Sort based on an option group.

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

  3. #3
    xboxown is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    I do this type of thing in the open event of the report. It changes the field specified in Group & Sort based on an option group.

    Code:
      If Forms!frmReportCriteria.fraSort = 1 Then
        Me.GroupLevel(0).ControlSource = "DriverNum"
      Else
        Me.GroupLevel(0).ControlSource = "FullName"
      End If
    But what if I want driverNum to be desceneding or ascending. How do I do that?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry:

    Code:
    Me.GroupLevel(0).SortOrder = True
    False is ascending, True is descending.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    xboxown is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    28
    Thank you so much for helping me! Appreciate it!

  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
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    xboxown is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    28
    It is not working at all

    I have tried and it keeps giving the same result. Here is the code:

    Code:
    If UOMandItem = True Then
        GroupLevel(0).ControlSource = "UOM"
        GroupLevel(1).ControlSource = "Item"
        GroupLevel(0).SortOrder = False
        GroupLevel(1).SortOrder = False
        OrderByOn = True
    End If
    
    
    If UOMSKUandItem = True Then
       GroupLevel(0).ControlSource = "UOM"
       GroupLevel(1).ControlSource = "SKU"
       GroupLevel(2).ControlSource = "Item"
       GroupLevel(0).SortOrder = False
       GroupLevel(1).SortOrder = False
       GroupLevel(2).SortOrder = False
       OrderByOn = True
    End If
    
    
    If UOMandItemDescription = True Then
       GroupLevel(0).ControlSource = "UOM"
       GroupLevel(1).ControlSource = "Item Description"
       GroupLevel(0).SortOrder = False
       GroupLevel(1).SortOrder = False
       OrderByOn = True
    End If

  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
    Where exactly is the code and what are the objects being tested, variables or?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    xboxown is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    Where exactly is the code and what are the objects being tested, variables or?







  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I only see one line setting those variables, so unless I'm blind they will have the same values no matter what selections are made.

    I don't see the report code, it should be in the open event.

    What you have could work if the variables were set appropriately. Personally I might not bother with the variables, I'd just use the combo value and a Select/Case in the report to handle the result (1,2,3,etc.).

    As a general FYI:

    http://www.baldyweb.com/OptionExplicit.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    xboxown is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    28
    Quote Originally Posted by pbaldy View Post
    I only see one line setting those variables, so unless I'm blind they will have the same values no matter what selections are made.

    I don't see the report code, it should be in the open event.

    What you have could work if the variables were set appropriately. Personally I might not bother with the variables, I'd just use the combo value and a Select/Case in the report to handle the result (1,2,3,etc.).

    As a general FYI:

    http://www.baldyweb.com/OptionExplicit.htm

    Thank you for pointing out my mistake Now it is working! 100%!!!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! It's a small thing, but presuming only one variable can be true I'd do:

    Code:
    If UOMandItem = True Then
        GroupLevel(0).ControlSource = "UOM"
        GroupLevel(1).ControlSource = "Item"
        GroupLevel(0).SortOrder = False
        GroupLevel(1).SortOrder = False
        OrderByOn = True
    ElseIf UOMSKUandItem = True Then
       GroupLevel(0).ControlSource = "UOM"
       GroupLevel(1).ControlSource = "SKU"
       GroupLevel(2).ControlSource = "Item"
       GroupLevel(0).SortOrder = False
       GroupLevel(1).SortOrder = False
       GroupLevel(2).SortOrder = False
       OrderByOn = True
    ElseIf UOMandItemDescription = True Then
       GroupLevel(0).ControlSource = "UOM"
       GroupLevel(1).ControlSource = "Item Description"
       GroupLevel(0).SortOrder = False
       GroupLevel(1).SortOrder = False
       OrderByOn = True
    End If
    As you have it, even if the first variable is True the code will still test the others. The above code will drop out after a test is met. In reality you won't see a speed difference in this situation, I just always try to use more efficient code.
    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: 5
    Last Post: 08-27-2019, 08:41 AM
  2. Replies: 2
    Last Post: 12-10-2018, 08:22 PM
  3. Replies: 6
    Last Post: 01-19-2017, 11:23 AM
  4. Replies: 7
    Last Post: 03-01-2013, 01:02 PM
  5. Make TEXTBOXES sort by DESCENDING order
    By taimysho0 in forum Programming
    Replies: 1
    Last Post: 12-05-2011, 04:52 PM

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