Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

Access Report OrderBy not applying correctly

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.
  • 2
  • 2
1 Solution
PeepsalotAuthor Commented:
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.  
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!

PeepsalotAuthor Commented:
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.

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now