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

Charlotte Foust charlotte.foust at gmail.com
Fri Dec 30 21:16:28 CST 2011


Another alternative might be to create groups based on different fields or
combinations.  Then populate each group with the controls you want to show
and total for that group.  That should allow you to show or hide the groups
on the fly.  Essentially, you don't use detail at all, you use the groups
and you could embedd subreports in each group that contained the fields you
wanted to display for that condition and the totals you wanted to show.
This is entirely possible in .Net, but it's been a long time since I made
Access behave this way.  I know it works, I'm just fuzzy on the details!

Charlotte Foust

On Fri, Dec 30, 2011 at 12:16 PM, David McAfee <davidmcafee at gmail.com>wrote:

> 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
>
>
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>


More information about the AccessD mailing list