On a Report, can I change any of the fields in the "Sorting and Grouping Dialog" from within Visual Basic??? Changing the OrderBy property of the Report doesn't change anything, nor does adding an "ORDER BY" clause to the RecordSource SQL Statement.
I have a table, named tInventory. It contains a text field called Item. There may be more than one row where Item is duplicated. I use a field called Year to distinguish them on the printed report. Of course there is a Primary Key field which is AutoNum, and therefore each row is unique.
I print a list of Item, with the Year added to the end. I want to list the Items alphabetically, grouped by the first letter of the Item. Originally, I listed the year as decending, as the newer of duplicate Items was the most important. I now want to give the user control of this, as the users are split on which order is best.
My report, would therefore have:
The "Sorting and Grouping Dialog is:
___Field/Expression___So rt Order
[{= FirstLetter____ _____Ascending
___Year________ _____Descending
___PrintName___ _____Ascending
The report has the appropriate PageHeader, GroupHeader, & Detail Sections. This all works very well.
Now I want to add an Option chkShowNewestFi rst, giving users a choice. How do I change the field in the "Sorting and Grouping Dialog" from 'Decending' to 'Ascending' if so checked?
The above code is a simplification of the actual problem. There are actually several GroupHeader levels, each with user choices. For this simple case, I could add a new field in my SELECT statement as either "[Year] AS SortYear" or "9999-[Year] AS SortYear" before assigning to RecordSource, but that gets very messy very fast. It also slows down the program.
I have a table, named tInventory. It contains a text field called Item. There may be more than one row where Item is duplicated. I use a field called Year to distinguish them on the printed report. Of course there is a Primary Key field which is AutoNum, and therefore each row is unique.
I print a list of Item, with the Year added to the end. I want to list the Items alphabetically, grouped by the first letter of the Item. Originally, I listed the year as decending, as the newer of duplicate Items was the most important. I now want to give the user control of this, as the users are split on which order is best.
My report, would therefore have:
Code:
RecordSource = "SELECT Left([Item],1) AS FirstLetter, " & _
"[Year], " & _
"[Item] & "" ("" & [Year] & "")"" AS PrintName" & _
"FROM tInventory;"
___Field/Expression___So rt Order
[{= FirstLetter____ _____Ascending
___Year________ _____Descending
___PrintName___ _____Ascending
The report has the appropriate PageHeader, GroupHeader, & Detail Sections. This all works very well.
Now I want to add an Option chkShowNewestFi rst, giving users a choice. How do I change the field in the "Sorting and Grouping Dialog" from 'Decending' to 'Ascending' if so checked?
The above code is a simplification of the actual problem. There are actually several GroupHeader levels, each with user choices. For this simple case, I could add a new field in my SELECT statement as either "[Year] AS SortYear" or "9999-[Year] AS SortYear" before assigning to RecordSource, but that gets very messy very fast. It also slows down the program.
Comment