Results 1 to 4 of 4
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018

    Question Trying to set Report sort order from Form using VBA

    Hi all,

    I'm trying to do some "tidying up" in my database application.

    One of the things I'd like to do is reduce the number of reports. Many are copies that just vary by sort order.

    As an example, I have two cookout reports that are identical except the sort order. One sorts by UnitID then by Day and the other sorts by Day and then UnitID. I know I could just use VBA open one report or the other, but I realized when I made changes to one report and not the other that it sure would be nice to have just one report with the sort option passed to it.

    Unfortunately, everyplace I've looked has things like "and then you can pass this string to the report using Open Args" or "the Order By property is over-ruled by the sort of the query" or something else I can't wrap my brain around. Or, it just talks about using the Group/Sort options within the report.

    I'm sure it's doable with VBA, I just can't figure out how!

    So far, on my form I have:

    Private Sub cmdCookoutDays_Click()
    Dim optGroup As Integer
    Dim rptName As String
    Dim sortField1 As String
    Dim sortField2 As String
    Dim strSort As String
    optGroup = ogCOSortOrder                  'name of option group so it can be changed for other reports
    rptName = "rpt Cookout Days"             'name of report so it can be changed later
    If optGroup = 1 Then
        sortField1 = "UnitID"
        sortField2 = "Day"
        sortField1 = "Day"
        sortField2 = "UnitID"
    End If
    strSort = sortField1 & ", " & sortField2   'strSort = sortField1, sortField2
    DoCmd.OpenReport rptName, acViewReport
    End Sub
    Now, what do I do with the strSort string?

    The way the report is set up currently is with no sorting and "Order By On Load" set to "yes". I've seen mention of using OpenArgs in the Order By property, but ... how?

    I'm sure I'm missing something really basic here ...

    Girl Scout volunteer

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    If you have Sorting/Grouping settings in report, those take precedence over any query ORDER BY clause or report ORDER BY property. And ORDER BY property overrides ORDER BY clause in query.

    So if this is a very simple report with no sorting or grouping, try:

    DoCmd.OpenReport rptName, acViewReport, , , , strSort

    Then code behind report:

    Private Sub Report_Load()
        Me.OrderBy = Me.OpenArgs
        Me.OrderByOn = True
    End Sub

    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    If it is more complicated, I do this type of thing to change the field in Group & Sort:

      If YourTestHere Then
        Me.GroupLevel(0).ControlSource = "OneField"
        Me.GroupLevel(0).ControlSource = "AnotherField"
      End If
    Paul (wino moderator)
    MS Access MVP 2007-2019

  4. #4
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018

    Thanks! You nearly always answer my questions ... perhaps you need to move next door to me here in Kansas! It'd be much faster for me to just pop over with a question!

    Let me know about my proposal ... I'll have to figure a way to get the neighbors to sell their house

    Girl Scout Volunteer

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

Similar Threads

  1. Replies: 1
    Last Post: 09-08-2015, 11:02 AM
  2. Sort a Report in Alphabetical Order
    By jmitchelldueck in forum Reports
    Replies: 1
    Last Post: 07-21-2015, 08:11 AM
  3. Report sort order from list field
    By john3588 in forum Reports
    Replies: 3
    Last Post: 03-01-2013, 02:41 PM
  4. Sort order in a sub form
    By roar58 in forum Forms
    Replies: 1
    Last Post: 03-17-2012, 08:57 PM
  5. Report will order/sort 4 columns but not 5. Why?
    By TomHolden in forum Reports
    Replies: 6
    Last Post: 12-17-2011, 04:25 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