Find any report referncing a specific data item...

[Andre L via AccessMonster.com]

I thought this would be easy but have come up empty. We are cleaning up our
warehouse. I have been given a list of data items to be eliminated from the
warehouse. I have been asked to check our Access reports, and queries to see
if any of these fields are referenced. I wrote some code to look through
each query and return affect tables: I input a field on a form and click GO,
it returns a list of queries.

********************************************************************
Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim db As Database
Set db = CurrentDb()
With db
'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm

'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable

'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
End With

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True
**************************************************************************

I wish to do somethong similar with Reports, ie look at every report and
every item printed and determine which reports print the fields they are
deleteing from the warehouse. I am stumped as to how to procede. There is
no ReportDefs variable.

Any ideas....

 

[Brendan Reynolds]

You could use something like the example code below. Alternatively, check
out some of the third party tools that exist to do this sort of thing - Find
and Replace (www.rickworld.com) SpeedFerret (www.moshannon.com) or Total
Access Analyzer (www.fmsinc.com)

Public Sub TestRepProp()

Dim aob As AccessObject
Dim rpt As Report
Dim ctl As Control
Dim prp As DAO.Property
Dim strPropVal As String

For Each aob In CurrentProject.AllReports
DoCmd.OpenReport aob.Name, acViewDesign
Set rpt = Reports(aob.Name)
For Each prp In rpt.Properties

'ignore non-string properties
strPropVal = vbNullString
On Error Resume Next
strPropVal = CStr(prp.Value)
On Error GoTo 0

If InStr(1, strPropVal, "Customers") > 0 Then
Debug.Print rpt.Name, prp.Name, strPropVal
End If
Next prp
For Each ctl In rpt.Controls
For Each prp In ctl.Properties

'ignore non-string properties
strPropVal = vbNullString
On Error Resume Next
strPropVal = CStr(prp.Value)
On Error GoTo 0

If InStr(1, strPropVal, "Customers") > 0 Then
Debug.Print rpt.Name, ctl.Name, prp.Name, strPropVal
End If
Next prp
Next ctl
DoCmd.Close acReport, aob.Name
Next aob

End Sub

--
Brendan Reynolds

I thought this would be easy but have come up empty. We are cleaning up
our
warehouse. I have been given a list of data items to be eliminated from
the
warehouse. I have been asked to check our Access reports, and queries to
see
if any of these fields are referenced. I wrote some code to look through
each query and return affect tables: I input a field on a form and click
GO,
it returns a list of queries.

********************************************************************
Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim db As Database
Set db = CurrentDb()
With db
'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm

'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable

'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
End With

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True
**************************************************************************

I wish to do somethong similar with Reports, ie look at every report and
every item printed and determine which reports print the fields they are
deleteing from the warehouse. I am stumped as to how to procede. There
is
no ReportDefs variable.

Any ideas....

 

[Guest]

Hi Andre,

I wrote the following code nearly two years ago. It has *not* been tested
extensively, but you might find something useful here. For example, it will
not determine form or report recordsources that are set in code for initially
unbound forms and reports.

In addition, you might find MVP Jeff Conrad's CSD Documentor Utility handy:

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

'*******************BEGIN CODE********************
Function EnumerateAllFormsAndReports(Optional objName As Variant)
On Error GoTo ProcError

' This code enumerates all forms and reports, and their recordsources.
' It requires the user to have design permissions. You must set a reference
' to the "Microsoft DAO 3.x Object Library" (x=.51 for A97, x=.6 for A2000+).
' Written by Tom Wickerath, 2/4/2004.
'
' Example usage from debug window:
' All forms and reports:
' ? EnumerateAllFormsAndReports()
'
' All forms and reports with no recordsource.
' ? EnumerateAllFormsAndReports("")
'
' All forms and reports with tblCustomers as recordsource:
' ? EnumerateAllFormsAndReports("tblCustomers")
'
Dim db As DAO.Database
Dim ctr As DAO.Container
Dim doc As DAO.Document
Dim i As Integer

Set db = CurrentDb()
i = 0

' Enumerate all forms
Debug.Print "Forms:" & vbTab & "Recordsource"
Set ctr = db.Containers!Forms

For Each doc In ctr.Documents
DoCmd.openForm doc.name, acDesign
If Not IsMissing(objName) Then
If Forms(doc.name).RecordSource = objName Then
Debug.Print doc.name & ": " & Forms(doc.name).RecordSource
i = i + 1
End If
Else
Debug.Print doc.name & ": " & Forms(doc.name).RecordSource
i = i + 1
End If
DoCmd.Close acForm, doc.name, acSaveNo
Next doc

If i = 0 Then
Debug.Print "No Forms"
End If

' Reset counter and enumerate all reports
i = 0
Set ctr = db.Containers!Reports
Debug.Print
Debug.Print "Reports:" & vbTab & "Recordsource"

For Each doc In ctr.Documents
DoCmd.OpenReport doc.name, acDesign
If Not IsMissing(objName) Then
If Reports(doc.name).RecordSource = objName Then
Debug.Print doc.name & ": " & Reports(doc.name).RecordSource
i = i + 1
End If
Else
Debug.Print doc.name & ": " & Reports(doc.name).RecordSource
i = i + 1
End If
DoCmd.Close acReport, doc.name, acSaveNo
Next doc

If i = 0 Then
Debug.Print "No Reports"
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in EnumerateAllFormsAndReports event procedure..."
Resume ExitProc
End Function

'********************END CODE********************

____________________________________________________________________

:

I thought this would be easy but have come up empty. We are cleaning up our
warehouse. I have been given a list of data items to be eliminated from the
warehouse. I have been asked to check our Access reports, and queries to see
if any of these fields are referenced. I wrote some code to look through
each query and return affect tables: I input a field on a form and click GO,
it returns a list of queries.

********************************************************************
Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim db As Database
Set db = CurrentDb()
With db
'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm

'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable

'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
End With

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True
**************************************************************************

I wish to do something similar with Reports, ie look at every report and
every item printed and determine which reports print the fields they are
deleteing from the warehouse. I am stumped as to how to procede. There is
no ReportDefs variable.

Any ideas....

 

[Andre L via AccessMonster.com]

THANK YOU ALL!

Hi Andre,

I wrote the following code nearly two years ago. It has *not* been tested
extensively, but you might find something useful here. For example, it will
not determine form or report recordsources that are set in code for initially
unbound forms and reports.

In addition, you might find MVP Jeff Conrad's CSD Documentor Utility handy:

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

'*******************BEGIN CODE********************
Function EnumerateAllFormsAndReports(Optional objName As Variant)
On Error GoTo ProcError

' This code enumerates all forms and reports, and their recordsources.
' It requires the user to have design permissions. You must set a reference
' to the "Microsoft DAO 3.x Object Library" (x=.51 for A97, x=.6 for A2000+).
' Written by Tom Wickerath, 2/4/2004.
'
' Example usage from debug window:
' All forms and reports:
' ? EnumerateAllFormsAndReports()
'
' All forms and reports with no recordsource.
' ? EnumerateAllFormsAndReports("")
'
' All forms and reports with tblCustomers as recordsource:
' ? EnumerateAllFormsAndReports("tblCustomers")
'
Dim db As DAO.Database
Dim ctr As DAO.Container
Dim doc As DAO.Document
Dim i As Integer

Set db = CurrentDb()
i = 0

' Enumerate all forms
Debug.Print "Forms:" & vbTab & "Recordsource"
Set ctr = db.Containers!Forms

For Each doc In ctr.Documents
DoCmd.openForm doc.name, acDesign
If Not IsMissing(objName) Then
If Forms(doc.name).RecordSource = objName Then
Debug.Print doc.name & ": " & Forms(doc.name).RecordSource
i = i + 1
End If
Else
Debug.Print doc.name & ": " & Forms(doc.name).RecordSource
i = i + 1
End If
DoCmd.Close acForm, doc.name, acSaveNo
Next doc

If i = 0 Then
Debug.Print "No Forms"
End If

' Reset counter and enumerate all reports
i = 0
Set ctr = db.Containers!Reports
Debug.Print
Debug.Print "Reports:" & vbTab & "Recordsource"

For Each doc In ctr.Documents
DoCmd.OpenReport doc.name, acDesign
If Not IsMissing(objName) Then
If Reports(doc.name).RecordSource = objName Then
Debug.Print doc.name & ": " & Reports(doc.name).RecordSource
i = i + 1
End If
Else
Debug.Print doc.name & ": " & Reports(doc.name).RecordSource
i = i + 1
End If
DoCmd.Close acReport, doc.name, acSaveNo
Next doc

If i = 0 Then
Debug.Print "No Reports"
End If

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in EnumerateAllFormsAndReports event procedure..."
Resume ExitProc
End Function

'********************END CODE********************

____________________________________________________________________

I thought this would be easy but have come up empty. We are cleaning up our
warehouse. I have been given a list of data items to be eliminated from the
warehouse. I have been asked to check our Access reports, and queries to see
if any of these fields are referenced. I wrote some code to look through
each query and return affect tables: I input a field on a form and click GO,
it returns a list of queries.

********************************************************************
Dim QueryNm As QueryDef
Dim TableNm As TableDef
Dim db As Database
Set db = CurrentDb()
With db
'Delete Table: tbl_TempList
For Each TableNm In .TableDefs
If TableNm.Name = "tbl_TempList" Then
DoCmd.DeleteObject acTable, "tbl_TempList"
End If
Next TableNm

'Create Table: tbl_TempList
Set TempTable = db.CreateTableDef("tbl_TempList")
With TempTable
.Fields.Append .CreateField("Name1", dbText)
End With
db.TableDefs.Append TempTable

'Start looking thru each Query's Sql to see if it contains the table name
desired,
'if so add it to the table: tbl_TempList
For Each QueryNm In .QueryDefs
If InStr(QueryNm.SQL, [Forms]![frmSearchQueriesForTableName]![TableName])
<> 0 Then
Indicator = "Found"
sqlstr = "INSERT INTO tbl_TempList(Name1) VALUES ('"
sqlstr = sqlstr & QueryNm.Name & "');"
DoCmd.RunSQL sqlstr
End If
Next QueryNm
End With

If Indicator = "Found" Then
MsgBox "Done!---Will Now Open the List for you."
DoCmd.OpenTable "tbl_TempList", acViewNormal
Else
MsgBox "Done!---No matches found."
End If

DoCmd.SetWarnings True
**************************************************************************

I wish to do something similar with Reports, ie look at every report and
every item printed and determine which reports print the fields they are
deleteing from the warehouse. I am stumped as to how to procede. There is
no ReportDefs variable.

Any ideas....