[AccessD] Is it possible to change Sub-Totals on Access 2007Reports with VBA?

Hi Brad

Here's some code that might work ... It's from an Excel VBA project, and
will need a little tweaking, but the sorting basics should be the same ...

Watch for line wrap on the 'UserChoice' line ...

Regards,
Bob Gajewski

==================================
END OF CODE
================================== 

Sub SortByColumnSelect()

Rem Take Screen Control From Application
    varFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
    varFilePath = ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.Cursor = xlWait
    Application.DisplayStatusBar = True
    Application.StatusBar = "Formatting 0% complete"

Rem Go to top left cell
    Application.Goto Worksheets(1).Range("A1")

Rem Freeze the top line
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

UserChoice = MsgBox("The data will be sorted and sub-totaled by months." &
vbCrLf & vbCrLf & "Do you want to sort by client instead?", vbYesNo +
vbQuestion + vbDefaultButton2, "Sort by client?")
	If UserChoice = vbNo Then
		Rem Sort the spreadsheet by column "A" (month)
		Cells.Sort Key1:=Range("A2"), Header:=xlYes
		Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(3, 4), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
	Else
		Rem Sort the spreadsheet by column "B" (client)
    		Cells.Sort Key1:=Range("B2"), Header:=xlYes
		Selection.Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3, 4), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
	End If

Rem Collapse the view to show only subtotals, color them grey, then expand
the view to show all detail
    ActiveSheet.Outline.ShowLevels 2
 
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Interior.ColorIndex =
15
    ActiveSheet.Outline.ShowLevels 3

Rem Fill in columns "A" (month), "B" (client)
        lastrow = ActiveSheet.UsedRange.Rows.Count
    For r = lastrow To 2 Step -1
        varPercentageComplete = Round((((lastrow - 1) - (r - 1)) / (lastrow
- 1)) * 100, 0)
        Application.StatusBar = "Formatting subtotal rows " &
varPercentageComplete & "% complete"
        If Right(Cells(r, 1).Value, 5) = "Total" Then
            Cells(r, 1).Value = Left(Cells(r, 1).Value, Len(Cells(r, 1)) -
6)
        End If
        If Cells(r, 2).Value = "" Then
            Cells(r, 2).Value = Cells(r - 1, 2).Value
        End If
    Next r

Rem Collapse the view to show only sutotals
    ActiveSheet.Outline.ShowLevels 2
    
Rem Resize all columns to show full width
    Columns.AutoFit
    
Rem Save output file
    Application.DisplayAlerts = False
    Application.StatusBar = "Saving formatted spreadsheet in XLS format ..."
        ActiveWorkbook.SaveAs Filename:=varFilePath & "\YourFileName " &
varFileName & " (created " & Format(Date, "yyyymmdd") & ")",
FileFormat:=xlNormal
    Application.DisplayAlerts = True
    
Rem Return screen control
    Application.Cursor = Default
    Application.StatusBar = ""
    Application.ScreenUpdating = True
'    ActiveWorkbook.Close
'    Application.Quit

End Sub

==================================
END OF CODE
================================== 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Thursday, December 29, 2011 16:05 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Is it possible to change Sub-Totals on Access 2007Reports
with VBA?

We have an Access 2007 report that currently has subtotals by month.

Recently there was a request to create a variation of this report with
subtotals by Customer.

It would be quite easy to create a second report.  But then I remembered a
slogan which said something like "Why make things simple when you can make
them complex and wonderful".  So I started to wonder if it is possible to
use VBA to change a report's sub-totals on the fly (controlled by buttons at
the top of the report).  

Is this possible?

Thanks,

Brad

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