Change the sort order in a report group from Visual Basic

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:

Expand|Select|Wrap|Line Numbers

1. RecordSource = "SELECT Left([Item],1) AS FirstLetter, " & _
2.         "[Year], " & _
3.         "[Item] & "" ("" & [Year] & "")"" AS PrintName" & _
4.         "FROM tInventory;"
5.  

The "Sorting and Grouping Dialog is:
___Field/Expression___Sort 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 chkShowNewestFirst, 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.

Anybody know how to modify to include group level in a dynamic report? There is no report saved in database so you can't go in on the report open sub.

Anybody know how to modify to include group level in a dynamic report? There is no report saved in database so you can't go in on the report open sub.

1. How to create a Dynamic Report in VBA
   http://bytes.com/forum/thread696050.html
2. The following code will create a Group Level on a [PayrollNumber] Field on a Report named rptEmployees. The Report on which the Group Level is to be created must initially be open in Design view.
   
   
   Expand|Select|Wrap|Line Numbers

   1. Dim varGroupLevel As Variant
   2. Dim blnCreateHeaderAlso As Boolean
   3. Dim blnCreateFooterAlso As Boolean
   4.  
   5. 'Let's create a Group Header but no Group Footer on [PayrollNumber]
   6. blnCreateHeaderAlso = True
   7. blnCreateFooterAlso = False
   8.  
   9. 'Critical that the Report is Open in Design View
   10. DoCmd.OpenReport "rptEmployees", acViewDesign, , , acHidden
   11.  
   12. 'Create new group level on the [PayrollNumber] Field.
   13. varGroupLevel = CreateGroupLevel("rptEmployees", "PayrollNumber", _
   14.                            blnCreateHeaderAlso, blnCreateFooterAlso)
   15.  
   16. 'Set the Height of the Group Header to 400
   17. Reports!rptEmployees.Section(acGroupLevel0Header).Height = 400
   18.  
   19. 'Save our lovely work
   20. DoCmd.Close acReport, "rptEmployees", acSaveYes

Great, that works (my code looks slightly different). Now I'm trying to put labels in the newly created group headers. The labels currently go in the page header and the text boxes are in the acDetail section.

I'm getting an error - either the grouping level doesn't exist or the report creates properly but the labels are nowhere to be found.

Below is my code. Any thoughts? Also, can anyone recommend a good guide to formatting reports? That is basically the last part of my project, I just need the data to look good and have no idea how to resize/move/get everything looking proper.

Expand|Select|Wrap|Line Numbers

1.   Case 16
2.     group1 = CreateGroupLevel(rpt.Name, strCbo1, 0, 0)
3.     group2 = CreateGroupLevel(rpt.Name, strCbo2, 0, 0)
4.     group3 = CreateGroupLevel(rpt.Name, strCbo3, 1, 1)
5.     group4 = CreateGroupLevel(rpt.Name, "Measure", 0, 0)
6.     With rpt
7.         .GroupLevel(group3).KeepTogether = 1
8.         .GroupLevel(group4).SortOrder = False
9.     End With
10. End Select
11.  
12.      'create label on pageheader
13.     For Each fld In rs.Fields
14.         CreateReportControl rpt.Name, acLabel, acPageHeader, , fld.Name, 0, 0
15.         CreateReportControl rpt.Name, acTextBox, acDetail, , fld.Name, 0, 0
16.  
17.     Next fld 

I've been trying acGroupLevel2Header and acGroupLevelgroup3header, nothing seems to work