Which query is being used by which form/report?

D

Dean Slindee

Is there any way to find out whether a query is being used, and by which
reports or forms?

Thanks,
Dean S
 
A

Allen Browne

This will list the RecordSource property of your forms and reports:

Public Function ShowSources()
Dim accObj As AccessObject
Dim strDoc As String

For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
Debug.Print strDoc, Forms(strDoc).RecordSource
DoCmd.Close acForm, strDoc
Next

For Each accObj In CurrentProject.AllReports
strDoc = accObj.Name
DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
Debug.Print strDoc, Reports(strDoc).RecordSource
DoCmd.Close acReport, strDoc
Next
End Function

If you are interested in tracking down where a particular field is used,
this code will do it:
http://allenbrowne.com/ser-73.html

There's also a utility by Chas Dillon to rename fields and trace
dependencies here:
http://allenbrowne.com/ser-41.html

Access 2003 and 2007 have the ability to trace dependencies, but that relies
on Name AutoCorrect. My experience is that where there are some invalid
sources (e.g. referring to fields/tables that no longer exist in the
database), it just crashes Access so is not usable.
 
Top