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

Brad,

  The trick with either OnFormat or OnPrint events in regards to multiple
firings is to check the FormatCount and PrintCount variables.

  The report engine lays out a page from top to bottom and sometimes based
on settings (like keep together), needs to retreat up the page and reformat
it.  When it does this, you will see the OnFormat and OnPrint fire multiple
times.

 So anytime you need to do anything with totaling, you need to do:

 If FormatCount = 1 then
    ' Do the totals or whatever else that needs to happen only once.
 End If

 Or as an alternative, you can add in OnFormat, then subtract in the
OnRetreat event.

 The other "gotcha" with the report engine is placing code in the OnPrint
event.  When you invoke two pass printing with .Pages, keep in mind that
OnPrint does not get fired on the first pass.   Many get caught by this in
trying to alter the layout or creating a table of contents or index in that
event, then not getting the results they expect.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, January 09, 2015 09:20 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] How to Grab Report Subtotals and Push them to Excel


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