Arthur Fuller
fuller.artful at gmail.com
Sat Jan 10 10:31:09 CST 2015
Brad, In the gentlest possible way, I mean to suggest that any reliance on the report itself is misguided. Think of a report as a presentation of the underlying dataset. In itself, the report is little more than that -- kind of like building a Word document from a pre-designed template: the action is in the template not the specific Word document you create. Similarly for reports created not only in Access but all report generators/designers: anything the report tool can do, you can do better. Admittedly, the report designer simplifies things somewhat, as with groups and subtotals; but it is IMO misguided to rely on the presentation tool as opposed to the data source. Let us assume that your report's data source is query XYZ. Then forget about the report for a moment and instead load query XYZ and play with it. Add Sum(someColumn) and GroupBy (someColiumn(s)) as needed until you arrive at a list of the group subtotals you're after. It might require several attempts to arrive at your desired results, and that's ok. Adopt some naming convention such as MyQuerySubtotals01,..09, providing you a method of backtracking your experiments. This way you end up with a bunch of failed experiments and one final solution, and then you can nuke the previous experiments without damaging any existing code that works. The answers lie in the data source, not the presentation of said data. A. On Sat, Jan 10, 2015 at 8:35 AM, Brad Marks <bradm at blackforestltd.com> wrote: > > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Arthur