[AccessD] Access 2007 Report - Sort Puzzler - The Plot Thickens (Sub-Report)

There are a couple of tricks that might help.  You can change the querydef
in VBA code if you do it from the form that launches the report.  SQL is a
property of the querydef, and it can be modified and saved from code.  But
that would mean you were hard coding the changes into the querydef each
time you ran the report, which isn't very efficient.  One thing to watch
out for is that subreports load first, so their OrderBy  is already in
place before the parent fully loads.  Also make sure you turn the OrderBy
on and off in code when needed.  What we did at one shop was to open the
reports or subreports in design view from code with visibility set to false
and alter the properties before saving the changes and then calling
OpenReport from the same routine. OrderBy has to be off in order to change
the string value, so you turn it back on and save the change.

Charlotte

On Wed, Nov 14, 2012 at 9:47 AM, Brad Marks <BradM at blackforestltd.com>wrote:

> Charlotte,
>
> Thanks again for your explanation on this issue.
>
> I have a small test application (with a simple report) working nicely with
> this code -
>
> DoCmd.OpenReport "Report1", acViewReport, "", "", acNormal
> Reports.Report1.OrderBy = "Field1"
>
>
> Now I am trying to control the order of data on a sub-report.
> This sub-report's data is not at all tied to the main report's data.
> (Different database, different table, different query).
>
> I had planned to simply change the underlying query def with VBA code, but
> as you pointed out, this will not work.
>
> I can't figure out where to place the statement to control the "Orderby"
> for the sub-report.
>
> I have tried several tests but nothing works.
>
> Thanks,
> Brad
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>
>
>