[AccessD] How to Grab Report Subtotals and Push them to Excel

Paul and Arthur,

Thanks for your advice.

You are right, setting up a new query to obtain the sub-total amounts is a better solution for the report that I am currently working on.

I wanted to experiment with grabbing the data directly from the report somewhat for curiosity reasons. 

Also, I sometimes go by the old adage “Why make things simple when you can make them complex and wonderful”  : - )


I think that in the future there may be cases where there is a need to grab select data from a report from multiple sections thus making it difficult to simply use a query to obtain the data.



After some digging, I found the following info on the internet regarding the need for checking the value of formatCount.  I experimented with this approach and it seems to work in my initial tests.  

“In some section, be it detail, a group footer, page header/footer or whatever (whenever the value you want is available), grab the value and add it to the total.  You *must* do it as:

   If formatCount = 1 then
      ' Add to totals
   End If

as the report engine often retreats up the page to re-format it and will call the OnFormat event multiple times for a given section.”



Thanks again for your help.  I plan to use the “query” approach for the current report but possibly use the other approach in the future if needed.

Brad


________________________________________
From: accessd-bounces at databaseadvisors.com <accessd-bounces at databaseadvisors.com> on behalf of Arthur Fuller <fuller.artful at gmail.com>
Sent: Saturday, January 10, 2015 5:42 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to Grab Report Subtotals and Push them to Excel

Brad,

I agree with Paul. To look at the report itself is the wrong way to go
about it. Look instead at the data source of the report and query that
query to obtain the subtotals you want.

Arthur

On Sat, Jan 10, 2015 at 1:51 AM, Paul Hartland <paul.hartland at googlemail.com
> wrote:

> Can you not write another query based on the report dataset to just produce
> the sub totals then just export that query out to Excel ?
>
> On 10 January 2015 at 02:20, Brad Marks <bradm at blackforestltd.com> wrote:
>
> >
> > All,
> >
> > We have a rather complicated Access 2007 report that has a “Group” in
> > order to show sub-totals on about 10 fields.
> >
> > Recently, the manager of our Accounting Department has asked to have the
> > sub-total amounts placed in an Excel file as this is what an outside
> > accounting firm is asking for.
> >
> > I have other Access applications that populate Excel via Windows
> > Automation, so I understand this part.
> >
> > However, I don’t understand how to correctly grab the sub-totals.
> >
> > I have experimented with the “On Format” and “On Paint” events in the
> > Footer that contains the sub-total fields.
> >
> > When I look at these fields via a Msgbox, the Msgbox is shown multiple
> > times, therefore I don’t think that I can grab the sub-totals on either
> the
> > “On Format” or “On Paint” events.
> >
> > Is there a good way to capture sub-total fields? (make them available in
> > VBA code)
> >
> > Thanks,
> > Brad
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



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