Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    267

    Unanswered: Changing the Reports Sort Order with VBA Code

    I have a rather involved report with a lot of data on it. The Clients now what to be able to select the sort order of the report (about 15 different sort orders being requested).

    Is there a way to change the sort order of the report using code (so I don't have to create 15 variations of the same report (each with its own sort order)

    S-

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Are you doing the sorting/grouping within the report itself (as part of the design)? If it's within the query/table the report runs on you can do as you wish order-wise ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2003
    Posts
    267
    As part of the Report design, because of the nature of the queries, I don't want to do it at query level.

    S-

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Are you grouping? Are you using the header and footer for groups? If so I don't imagine you could do this very easily.

    Otherwise you might be able (haven't tried it) to pass in an Order By string using the OpenReport openargs. And set the Order By property to the string. You will also probably want to display the sort on the report somehow.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DCKunkle
    Are you grouping? Are you using the header and footer for groups? If so I don't imagine you could do this very easily.

    Otherwise you might be able (haven't tried it) to pass in an Order By string using the OpenReport openargs. And set the Order By property to the string. You will also probably want to display the sort on the report somehow.
    Only problem is that the "hard coded" re: sort selections override any ordering of the data by the query ... Passing in the same thru OpenArgs will not work just the same ...

    sbaxter, try searching this forum for constructing reports thru code ... There might be something there for assigning/reassigning group headers/footers.
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    Quote Originally Posted by DCKunkle
    Are you grouping? Are you using the header and footer for groups? If so I don't imagine you could do this very easily.
    No, just for sorting purposes


    Quote Originally Posted by M Owen
    sbaxter, try searching this forum for constructing reports thru code ... There might be something there for assigning/reassigning group headers/footers.
    Will see what I can find. I have found something in MSDN, but can't determine how to get it to work

    http://msdn.microsoft.com/library/de...andreports.asp

    Section titled "Changing the Filter or Sort Order of a Form or Report"

    S-

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can simple sort your report by modifying Order by property in open event of your report. something like:

    to sort by "Names" field in on click event of your button that fires the report;
    Code:
    	DoCmd.OpenReport "YourReportName", acViewPreview, , , , "Names"
    then in Open event of your Report;
    Code:
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
    ghozy.

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Try taking a look at Allen Browne's tip

    http://members.iinet.net.au/~allenbrowne/ser-33.html

    Sort:
    Me.Grouplevel(1).SortOrder = False

    False - ascending, True - descending
    Roy-Vidar

  9. #9
    Join Date
    Nov 2003
    Posts
    267
    Quote Originally Posted by ghozy
    you can simple sort your report by modifying Order by property in open event of your report. something like:

    to sort by "Names" field in on click event of your button that fires the report;
    Code:
    	DoCmd.OpenReport "YourReportName", acViewPreview, , , , "Names"
    then in Open event of your Report;
    Code:
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True

    Using Access 2000, Thanks Though

  10. #10
    Join Date
    Nov 2003
    Posts
    267
    Quote Originally Posted by RoyVidar
    Try taking a look at Allen Browne's tip

    http://members.iinet.net.au/~allenbrowne/ser-33.html

    Sort:
    Me.Grouplevel(1).SortOrder = False

    False - ascending, True - descending

    Thanks, Tried using this but coulding get it to work right, will keep trying Believe it would be a better solution, but...

    Current Solution
    Remove All Grouping from report

    Code:
    Dim rpt As Report
    Dim strReportName As String
    strReportName = "ReportName"
    
        DoCmd.OpenReport strReportName, acViewDesign
        Set rpt = Reports(strReportName)
        rpt.OrderByOn = True
        rpt.OrderBy = "Variable1, Variable2"
        DoCmd.Close acReport, rpt.name, acSaveYes
    
        DoCmd.OpenReport strReportName, acViewPreview

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •