[AccessD] Change Order within Access 2007 Report Group

I have an action item report that I can sort by various fields (due date, action by person, etc.) using an option group (that sets a global variable gReportType) and then by ascending or descending order using another option group (that sets another global variable gActionSortOrder).

I set the global variables in the command button Click event on the form:
        If Me!optSort = 1 Then
            gReportType = "ByActionNo"
        ElseIf Me!optSort = 2 Then
            gReportType = "ByActionBy"
        ElseIf Me!optSort = 3 Then
            gReportType = "ByDueDate"
        End If

        If Me!optSortOrder = 1 Then
            gActionSortOrder = "Ascending"
        Else
            gActionSortOrder = "Descending"
        End If

In the report's Open event, I have the following:

    Dim MySortOrder As String
    'Ascending = False, Descending = True
    If GetActionSortOrder() = "Ascending" Then
        MySortOrder = "False"
    Else
        MySortOrder = "True"
    End If

    If GetReportType() = "ByDueDate" Then
        Me.GroupLevel(1).ControlSource = "DateDue"
        Me.GroupLevel(1).SortOrder = MySortOrder
        Me.GroupLevel(2).ControlSource = "ActionItemNo"
        Me.GroupLevel(2).SortOrder = False
    ElseIf GetReportType() = "ByActionBy" Then
        Me.GroupLevel(1).ControlSource = "ActionBy"
        Me.GroupLevel(1).SortOrder = MySortOrder
        Me.GroupLevel(2).ControlSource = "DateDue"
        Me.GroupLevel(2).SortOrder = False
    Else                                                'SortByActionNumber
        Me.GroupLevel(1).ControlSource = "ActionItemNo"
        Me.GroupLevel(1).SortOrder = MySortOrder
    End If


Not the cleanest code, but it works.



Bruce Kovacs
Quality Assurance Director

The Steam Generating Team
7207 IBM Drive
Charlotte, NC  28262

Phone: 704-805-2131
Mobile: 704-200-8802
Fax: 704-805-2875
bruce.kovacs at urs.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Tuesday, December 09, 2014 04:48 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Change Order within Access 2007 Report Group

All,

Is it possible to change the order in an Access report Group from "with A on top" to "with Z on top" with VBA code?

We currently have an Access 2007 report that our Accounting Department uses daily.  This report has one report "Group" on Sales_Order_Number (Ascending).

Recently it was requested that a second report be created with the Sales_Order_Numbers in descending order.

Instead of creating a second report, I would like to have a button to change the Report's Group Order on the fly.  Is this possible via VBA code?

We have several other reports where the sort order is changed on the fly, but none of these have report Groups.

Thanks,
Brad

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


This e-mail and any attachments contain URS Corporation confidential information that may be proprietary or privileged. If you receive this message in error or are not the intended recipient, you should not retain, distribute, disclose or use any of this information and you should destroy the e-mail and any attachments or copies.