Access Report OrderBy not applying correctly

Posted on 2006-05-31
Last Modified: 2008-02-01
This is driving me insane.  I am working on a report where I need to programmatically set the OrderBy property.

Here is a snippet of the code I am using.

DoCmd.OpenReport FileString, acViewPreview, , wClause
If OrderByStr = "" Then
    Application.Reports(FileString).OrderByOn = False
    Application.Reports(FileString).OrderByOn = True
    Application.Reports(FileString).OrderBy = OrderByStr
    'MsgBox Application.Reports(FileString).OrderBy
End If
DoCmd.OutputTo acOutputReport, FileString, acFormatSNP, Outputdir + "\" + CStr(iRequestID) + "-" & FileString + ".snp", False

The orderby string that I am testing contains two fields, comma separated. ("wolevel_idx,wo_idx")

When I run this, it seems to only order by wo_idx.  However if I uncomment the msgbox line, and let this message popup, it magically orders them correctly.  What the hell is going on here.  I guess it needs some time to set the order by before running the report?  Is there some command I am missing to tell it wait for the previous command(setting orderby) to finish before running the next command(outputto).  Is VB in access not single threaded?  Why doesn't anything in access ever JUST WORK.

I'm giving this question all my remaing points, hopefully it's enough.
Question by:Peepsalot
    LVL 2

    Author Comment

    Well, i just tried replacing the msgbox with a large loop
    For i = 0 To 30000
        For j = 0 To 30000
    Takes about ten seconds or so, but this does not fix the problem like a msgbox does.

    Another strange thing I forgot to mention: without the msgbox, it seems to sort by wo_idx, but not wolevel_idx.  The results sorted by wo_idx are different from if I had no orderby set at all, so it seems to be only partially applying the orderby for whatever reason.  
    LVL 58

    Accepted Solution

    Hello Peepsalot

    In years of programming with Access, I have never used this particular technique to sort reports programatically. So I had to try it, and did observe the behavior you describe. However, it either sorted correctly (with MsgBox) or not at all. So I don't know that the problem is with that.

    The replacement of MsgBox is DoEvents.

    It doesn't need just time, it needs idle time, and DoEvents does precisely that: allow any pending operations to be run. So you are right, VB is single-threaded, but it's multi-tasking. When you set OrderByOn, the report sets a flag to reformat and redraw ASAP. That is, as soon as your code has finished running. Only, by that time, it will already have been overruled by the OutputTo command.

    At least the name of the property (and it's a property, not a method) is not misleading. It just turns a mode on or off. The method to actually redraw the report would be .Repaint, .Resort or the like.

    Anyway, although DoEvents solved your problem, here is the "official" method for your case.

    You can use the "FilterName" to specify a sort order, a where condition, or both. For that, you can create a "filter" by opening the relevant table or query, sorting it, filtering it, and going to "records / filters / advanced filters" to save it. And you can write it directly as SQL.

    A filter has this structure:

        SELECT * FROM <table or query>
        [ WHERE <where condition> ]
        [ ORDER BY <order fields> ]

    In effect, you can rewrite your code like this:

        strFilter = "SELECT * FROM <insert name of report's source>"
        If wClause <> "" Then _
            strFilter = strFilter & " WHERE " & wClause
        If OrderByStr <> "" Then _
            strFilter = strFilter & " ORDER BY " & OrderByStr
        DoCmd.OpenReport FileString, acViewPreview, strFilter
        DoCmd.OutputTo acOutputReport, FileString, acFormatSNP, _
            Outputdir + "\" + CStr(iRequestID) + "-" & FileString + ".snp", False

    If you have only two or three different sort orders, you could also save them and refer to them by name, leaving wClause for the last argument.

    In case you wonder, the WHERE clause of the Filter and that of the WhereCondition are combined, uning AND. Records will have to match both conditions.

    In any case, using FilterName, WhereCondition, or a combination of both allows you to open the report correctly the first time. No need to run it twice and no need for DoEvents...

    There would be many more things to say about reports, filtering and ordering, of course.

    Good luck!

    LVL 2

    Author Comment

    Great!  Using DoEvents works.  Thanks for taking the time to explain the logic behind it, best answer I've gotten on here.

    For the filterStr method, is there any reason this would be better?  I don't think I am able to do this one.  The code given is part of a function that is capable of opening ANY report, it is called from an ASP page for custom filtered reports, etc.  So the recordsource is dependant on which report is being called.  And I can't get the recordsource property of a report that is not yet open.  I guess I could open a report, get the recordsource, close it, then repoen with the correctly built query, but this seems very inefficient.  I'm going for the fastest method because some of these reports are monsters.
    LVL 58

    Expert Comment


    Thanks for  the compliment ;)

    > "some of these reports are monsters"

    So there would be some benefit in opening the report only once. When you let the report redraw, it's almost like reopening it. All in all, you format each report three times (opening, sorting, printing).

    I understand that you want to remain as generic as possible, however you do have a list of reports and you do have (or should have) some data structure indicating on which fields you can filter and/or sort for each report. In the same data structure, it makes sense to also have the name of the underlying table/query so that you can use the method above.

    There are two other advanced techniques that allow formatting the report only once:

    1) Each report is based on a query with a similar name (qrptSomething for rptSomething), and the query itself gets rewritten so that you can use OutputTo on the closed report.

    2) Each report manipulates in Report_Open a predefined list of grouping / sorting entries (they cannot be created at that time, only modified) according to some mechanism involving global variables.

    However, if the current solution works for you, don't fix it ;)


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now