[AccessD] Need to Find Access Report that is using a Specific Query

[Dan Waters]

Hi Bill,

In order to find the recordsource of a query using some kind of search
mechanism, the report needs a named query.  In the example below, there is a
report named "REPORTNAME" which has a recordsource of a query named
"QUERYNAME".  The name of the query does not change - but you can change the
SQL in the query as needed.

If you can't do this, then use Rick Fisher's Find and Replace to look for
all the queries (using partial match) that use a readily identifiable part
of the recordsource string - like "tblFYOfRevData".  You'll get a report of
all those different reports.

If you are dealing with any large Access application, Rick Fisher's F&R is
really excellent!

HTH!
Dan

----------------------------------------------------------------------------
---------------
        Select Case "A"

            Case "B"
                CurDB.QueryDefs("QUERYNAME").SQL = strSQL &
strSQLFiscalYrConditional & " AND (" & strSQLFiscalYrOfRevConditional & ")"

            Case "C"
                CurDB.QueryDefs("QUERYNAME").SQL = strSQL &
strSQLFiscalYrConditional

            Case "A"
                CurDB.QueryDefs("QUERYNAME").SQL = strSQL &
strSQLFiscalYrOfRevConditional

            Case Else
                CurDB.QueryDefs("QUERYNAME").SQL = "SELECT * FROM
tblFYOfRevData"

        End Select

        CurDB.QueryDefs.Refresh

        DoCmd.OpenReport "REPORTNAME", acViewPreview
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Friday, February 05, 2016 11:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need to Find Access Report that is using a Specific
Query

I am afraid I do have to pass Go, because for example, I might have a base
query named qry_Base. Then in the Report_Open event, code which gets the
querydef with that name (possibly dynamically derived); take the SQL
property, and add some criteria to the WHERE clause, then

    With Me
        Select Case True

            Case bFiscalYearSelected And bFiscalYrOfRevSelected
                .RecordSource = strSQL & strSQLFiscalYrConditional & " AND
(" & strSQLFiscalYrOfRevConditional & ")"

            Case bFiscalYearSelected
                .RecordSource = strSQL & strSQLFiscalYrConditional

            Case bFiscalYrOfRevSelected
                .RecordSource = strSQL & strSQLFiscalYrOfRevConditional

            Case Else
                .RecordSource = "tblFYOfRevData"

        End Select
    End With


You get the idea. No way Rick Fisher's tool gonna keep up with this kind of
automation.

And no, this does not help the  OP much either, just adds more anxiety more
than anything.

On Fri, Feb 5, 2016 at 8:42 AM, Jim Dettman <jimdettman at verizon.net> wrote:

>
>  Stop here, do not pass go:
>
>  Rick Fisher's Find and Replace:
>
>  www.rickworld.com
>
>
> Jim.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Brad Marks
> Sent: Thursday, February 04, 2016 05:51 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Need to Find Access Report that is using a Specific 
> Query
>
> All,
>
> I am working with a very large Access application that has many 
> reports and many queries.
>
> I would like to find the name of a report that is using a specific query.
>
> Is there an easy way to do this?
>
> Thanks,
> 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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com