A.D. Tejpal
adtp at airtelmail.in
Mon Jan 2 06:14:50 CST 2012
Brad,
My sample db named Report_CustomGroupSort might be of interest to you.
It handles a book list with fields like topic, publisher, author, title,
year etc.
In a simulated sorting & grouping subform, the user can click various
check boxes so as to specify the fields to be earmarked as group headers,
apart from desired sort orders for all available fields.
Based upon user's choice, the report gets displayed with specified group
headers, depicting appropriate subtotals in corresponding group footers. For
example, if the user has selected topic and author as group headers,
subtotals get displayed author and topic wise. If the selection is now
changed to publisher and author the report gets rearranged so as to display
new subtotals, author and publisher wise.
The program ensures that fields earmarked for group headers do not get
displayed in detail section. Moreover, the position of displayed controls in
detail section is adjusted suitably so as to match the order specified in
sorting & grouping subform. At the same time, width of control named Title
is manipulated suitably so as to make full use of available space (e.g.
space released by fields diverted from detail section to group headers).
Note: Controls in detail section have intentionally been provided with a
light border so as to afford convenient visual indication regarding
manipulation of their position and width.
The sample db is in access 2000 file format and is available at Rogers
Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: Charlotte Foust
To: Access Developers discussion and problem solving
Sent: Saturday, December 31, 2011 08:46
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.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