[AccessD] What is the best way to grab fields from a report detail line so that they can be accessed in VBA?

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.





More information about the AccessD mailing list