Brad Marks
BradM at blackforestltd.com
Tue Jan 3 13:36:28 CST 2012
All,
Thanks for the help and insights into this question.
Because of other work that needs to be done, I think that I will just
take the easy approach and create a second report rather than trying to
change sub-totals on the fly.
I appreciate all of the assistance.
Brad
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Friday, December 30, 2011 9:16 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Is it possible to change Sub-Totals on Access
2007Reports with VBA?
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.ColorInde
x =
> > 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
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.