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

I doubt possible. If you notice, the name of the section in the report designer is based on the actual field you grouped on. This is not a property that can change. When you are in runtime, Access is looking at this as group 1, 2, etc... Doesn't give you access to what field it is holding in that position. It makes sense to me why you can't.

Now, you can use the format event of the section to create a new calculation using recordsets... will slow down the report considerably. But the subtotals (calculated items) will be flawed anyway because they won't be based on where the data really breaks, according to your field of interest in the rowsource.

I'd be interested to find out I am wrong...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Gajewski
Sent: Thursday, December 29, 2011 10:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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