Hi there.
I just figured it out.
Thanks.

But still would love to see if you will give me a better solution.

Dr. Sami Shamma

Hi Again,
Spoke too soon. My way did not work after all

What is vPrintView declared as. I believe it should be a Boolean (True/False) - you're comparing it to a string ("Yes")
Also, you say your way didn't work - what did you try?

From the code given above, try this:
Dim vPrintView As Boolean

If vPrintView Then
    DoCmd.OpenReport "RosterR", acViewPreview, , Filter
Else
    DoCmd.OpenReport "RosterR", acViewNormal, , Filter
End If

Hi Kevin.
Can I manipulate the sort element in the Group section of a report? if I can, I can fix my problem.

It requires some tricky VBA to change the grouping(s) inside a report.  You need to put VBA code inside the report to do that.

Inside the report's VBA module (in the Open event preferably, because you want the change to take effect as soon as the report opens), you need to change the report's GroupLevel property.  For instance, if my report's recordsource contains the fields country_name, capital, and continent, I specify the "continent" field to be made a group with this code:

   Me.GroupLevel(0).ControlSource = "continent"

The ordinal number (0) indicates that this is the first (top-level) grouping in the report.  Subsequent grouping levels can be referenced by GroupLevel(1), GroupLevel(2), and so on.

For groupings to work, the records need to be sorted the right way as well (obviously).  A grouping for "continent" would be useless if the records were sorted by country names.  So you need to use VBA to change the report's sort order as well, by changing the report's OrderBy property as shown below.  You also to need to set OrderByOn to True for the sort order to take effect.  (I believe Richard has touched on this topic in his videos, but I don't remember which.)

The OrderBy property is a string containing the field names separated by commas, in the order needed for the sort:

    Me.OrderBy = "continent,country_name"
    Me.OrderByOn = True

Lastly, you likely need your footer (or header) to show some of kind of description.  For "continent" groups, you want to show "Africa," "Asia," "Europe," in the footer.  To do that, you need a textbox in your footer that is bound to the field that is used in the grouping.  To do that in VBA:

    Me!footerfield.ControlSource = "continent"

See the pictures below for a sample report I made.  When the report opens, I have the user pick a sort order, and the report changes the grouping and sorting accordingly.

Thank you so much.
It worked like a charm.
this is the code I used:


Private Sub Report_Open(Cancel As Integer)
Dim vSortAlphabetically As String
Me.OrderByOn = True
vSortAlphabetically = DLookup("SortAlphabetically", "DefaultT", "DefaultID = 1")
If vSortAlphabetically Then
    Me.GroupLevel(1).ControlSource = "Full Name"
Else
    Me.GroupLevel(1).ControlSource = "Housing Code"
End If

End Sub

Good morning
Another related question:
is there a property that 1 can set to change the number of copies from the default "1" to another number? either from the calling form or from the report itself.

many Thanks
Dr. Sami Shamma

Hi Sami, you can specify the number of copies to be printed with the "DoCmd.PrintOut" method. But this only works if you have a report already opened in preview mode:

    Dim ncopies As Integer
    ncopies = 3
    DoCmd.OpenReport, "MyReportName", acViewPreview
    DoCmd.PrintOut , , , , ncopies

DoCmd.PrintOut has several settings including starting page, ending page, collated or not, etc.  The full documentation from Microsoft is here: https://learn.microsoft.com/en-us/office/vba/api/access.docmd.printout

Kevin you are the best.
I actually only need to use the #of copies when in acViewNormal so I used this code in the calling Form and it works well:

vShowPreview = DLookup("ShowPreview", "DefaultT", "DefaultID =1")
vCopy = DLookup("Copies", "DefaultT", "DefaultID =1")

  If vShowPreview Then
      DoCmd.OpenReport "RosterR", acViewPreview, , Filter
      DoCmd.OpenReport "RosterMasterR", acViewPreview, , Filter
  Else
      DoCmd.OpenReport "RosterR", acViewNormal, , Filter
          For vCounter = 1 To vCopy
              DoCmd.OpenReport "RosterMasterR", acViewNormal, , Filter
          Next
End If

I thank you very much, My DB is looking and behaving beautifully

Have a great Holiday