Brad Marks
BradM at blackforestltd.com
Tue Jul 5 07:18:24 CDT 2011
Doug, Thanks for the advice. I may need to go down this path. Because of the complexity of the reports and underlying queries, I was hesitant to change the underlying queries. The idea of creating a temporary query is something that I had not considered. Thanks again, Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele Sent: Monday, July 04, 2011 8:17 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] What is the best way to grab fields from a report detail line so that they can be accessed in VBA? Brad, what I do in similar circumstances is build and save the 'Where' string in the filtering form. In code, I take the original SQL for the report recordsource and add the Where string to it. I then save this new SQL into a temporary query and export it to Excel. Doug On Mon, Jul 4, 2011 at 11:53 AM, Brad Marks <BradM at blackforestltd.com> wrote: > All, > > I want to grab the value of fields from report detail lines. > > Here is the method that I finally got to work. > > I open the report with a line like this... > > DoCmd.OpenReport "Report1", acViewPreview, "", "", acNormal > > I then grab the fields in the "On Print" Event > > This works, but I would like to know if this is the best method to do > this. > > ~ ~ ~ ~ > > There rest of the story... > > Here is what I am trying to accomplish. > > I have a number of Access 2007 reports that employ "dynamic filters" via > the use of "Report View" and the "Where Condition" when the report is > opened. In other words, the filters are applied at the report level and > not at the underlying query level. (There are buttons on the reports > which open up a small form that is used to collect "filter info" and > then re-open the report with the appropriate "Where Condition" based on > the "filter info") > > Now there is a need to export the report data to Excel. Because of the > complex nature of the reports, they do not Export very nicely to Excel > (I have tried several approaches). > > I know that I could easily export from the underlying queries to Excel, > but this type of export would not take into account the filters that are > applied at the report level. > > Therefore, to export the exact data on the reports to Excel, I have > started to experiment with grabbing the data from the report as it is > being built. This is not ideal, but I can't seem to find a better > method. > > Maybe others have run into this issue and have a better approach. > > Thanks for your assistance. > > 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.