Modified Query SQL not being used by Report 1

[scriverb]

Strange situation. I have a form where the "USER" selects from a combobox the appropriate sort for a report to be previewed. The VBA code then updates the query with a new ORDER BY . . . line. I have verified that the query has changed to the new sort order. The Record Source for the report is in fact the correct query. I am not using the sorting and grouping feature in the report. When the preview runs the correct records are being selected but the NEW sort order is not reflected. While in preview I can click the design button and then look at the Record Source for the report. The query is correct and the SQL within the query has been modified by the code earlier noted. The query when run seperately does in fact sort propertly.

Very confusing. I am running Windows XP and A97 for this application. I have run this type of form, VBA code, query, and report combination many times before without a problem.

Any ideas experts??? Strange!!!

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dhookom]

I never rely on the query's sort order to be honored by a report. Allen Browne has code at

http://members.iinet.net.au/~allenbrowne/tips.html

to set the grouping level dynamically.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Thanks dhookup, I will try this. BUT, you knew this was coming. WHAT I DESCRIBED SHOULD WORK!!!. RECORD SOURCE query with an ORDER BY. This should display a simple list of records in whatever the ORDER BY designates.

I have never run into this before and I have used this technique in the past. But, I do know that at times ACCESS takes on a mind of its own.

I will let you know shortly how this works. Thanks again.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

dhookup: Yes, I got your suggestion to work. Here is what I did to document for others.

1. Created a single Sorting Group designed as (0) in the report sort numbering scheme.
2. Modified my query/Record Source to include four(4) new columns(i.e. Sort1, Sort2, Sort3, Sort4) which cancatenated fields together in the proper required sort orders.
3. Put this code in the OnOpen event procedure of the report.

Select Case Forms![frmSelectionForm]![cboSortOrder]
Case 1 'Insured Name
Me.GroupLevel(0).ControlSource = "Sort1"
Case 2 'Insurance Company
Me.GroupLevel(0).ControlSource = "Sort2"
Case 3 'Employer
Me.GroupLevel(0).ControlSource = "Sort3"
Case 4 'Invoice Number
Me.GroupLevel(0).ControlSource = "Sort4"
End Select
4. Removed the ORDER BY clause of the query and relied upon the above code to update the field to be sorted in the report.

It stills seems like a true bug in ACCESS for the original method NOT working.

Thanks for the help. Have a star on me. I appreciate the link to the Allen Browne page as I had not seen this one before. Some good code and information there.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dhookom]

I have seemed to notice that as a report opens, if it has a "where clause" or filter, the report will re-build its record source to optimize the query. In this process, the order by may get lost. This is just my WAG.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

I am not using the OpenReport parameters to preview these reports. The query does have a WHERE clause and an ORDER BY. The ORDER BY was being modified through code based upon the selection from the calling forms combobox. The query does in fact get updated correctly. The query runs correctly in the right sort order. But, when the report uses the query as the Record Source it just simply displays the records in the tables original default order.

Now the query does have 5 tables that are linked using multiple left joins to a single main table. The default sort of the main table is the order being displayed in the report. The ORDER BY modifications are using fields from the secondary tables but I don't this should be an issue.

Who's to know. I do have the problem solved with your help so we will just take on a new technique in the future.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???