Report Instantiation 1

[RealTKEddy]

I have a report that is fed by a query. The SQL for the query is created/modified by the choices made on a form by the user. Some of these choices are for sorting. My problem is that I cannot find a way to instantiate the report in order to access the Order By & Order By On properties of the report so I can dynamically have the sorting changed.

Is there a way to do this?

I have tried accessing the Application.CurrentProject.AllReports collection to gain access to the specific report, but you only get access to an AccessObject which is really only a pointer to a few attributes of a report, not the report itself.

Thanks for any help anyone can provide.

 

[dhookom]

I believe you would have to have the report open in order to change the group levels. There is sample code at

http://users.bigpond.net.au/abrowne1/ser-33.html.

I would not mess with the OrderBy properties since any grouping or sorting will always over-ride the OrderBy.

Another method is to use an option group or list box on a form to determine sorting. Assuming Form!frmA!optSortBy as an option group with values
1 Last Name
2 First Name
3 City
4 Zip Code
You can add a calculated column in your query:
SortBy: Choose(Form!frmA!optSortBy, [LastName], [FirstName], [City], [ZipCode])
This will dynamically fill the column with the fields selected on the form.

Duane
MS Access MVP

 

[RealTKEddy]

Thanks for the info and code. But, I am not having a problem dynamically changing the sorting on the query. For some reason (unknown to me), the sorting on the query does not carry over to the report. In the past, I have had to set the sort/group properties in report design mode to get the desired sorting/grouping. This time around, I need to have the sorting dynamically set on the report output.

I hope this makes sense?

Thanks, Jim

 

[dhookom]

Sorting has never reliably carried over from the query to the report. I should have continued with my original reply to suggest setting the primary sorting and grouping in the report to the calculated column. Then selecting on option/field on the form will automatically change the sorting field in your report.



Duane
MS Access MVP

 

[RealTKEddy]

Thanks. I won't be able to tryout until Tue next week. I'll let you know how it works out then. Have a good Labor Day weekend.

Thanks again, Jim

 

[RealTKEddy]

I finally got to work on this today, and I was able to take your suggestion and run with it. Since I already had the drop lists on the form and the code in place to dynamically alter the SQL created for the query, I just piggy-backed off of that to create the calculated fields. The report is now sorting correctly.

Thanks again, Jim