David McAfee
davidmcafee at gmail.com
Fri Dec 30 14:16:07 CST 2011
You could do things old school with hidden fields in the details section
for all of the fields you want to subtotal.
Then depending on some other form or input parameter, hide/show the
appropriate field(s) in the report footer.
HTH
David
On Thu, Dec 29, 2011 at 8:07 PM, Benson, William (GE Global Research,
consultant) <Benson at ge.com> wrote:
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>