AC said:
I have tons of reports and queries in my db, how can I quickly check
if a query is the record source for any report in the db? I'm
thinking there may be something like 'trace dependents' in Excel but
I haven't found it.
Here's some quickie code that will do something very like what you ask:
'----- start of code -----
Sub SearchReportRecordSources(strSought As String)
' Search the recordsources of all reports
' for the specified string.
On Error GoTo Err_SearchReportRecordSources
Dim db As DAO.Database
Dim doc As DAO.Document
Dim rpt As Report
Dim varTest As Variant
Dim lngReportCount As Long
Dim lngFoundCount As Long
Debug.Print "*** Beginning search ..."
Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
DoCmd.OpenReport doc.Name, acDesign, _
WindowMode:=acHidden
Set rpt = Reports(doc.Name)
With rpt
lngReportCount = lngReportCount + 1
If InStr(.RecordSource, strSought) Then
Debug.Print "Report " & .Name & _
" RecordSource: " & .RecordSource
lngFoundCount = lngFoundCount + 1
End If
DoCmd.Close acReport, .Name
End With
Set rpt = Nothing
Next doc
Exit_SearchReportRecordSources:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngReportCount & _
" reports, found " & _
lngFoundCount & " occurrences."
Exit Sub
Err_SearchReportRecordSources:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchReportRecordSources
End Sub
'----- end of code -----
Note that, as written, it searches for a particular string *within* the
recordsource, so if you search for "qryA", it will turn up reports that
use "qryA", "qryAB", "qryABC", and so on. You could modify it easily to
only find exact matches.
