Peepsalot
asked on
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(FileSt
Else
Application.Reports(FileSt
Application.Reports(FileSt
'MsgBox Application.Reports(FileSt
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.
Here is a snippet of the code I am using.
DoCmd.OpenReport FileString, acViewPreview, , wClause
If OrderByStr = "" Then
Application.Reports(FileSt
Else
Application.Reports(FileSt
Application.Reports(FileSt
'MsgBox Application.Reports(FileSt
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Peepsalot
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 ;)
(°v°)
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 ;)
(°v°)
ASKER
For i = 0 To 30000
For j = 0 To 30000
Next
Next
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.