AllForms Collection

[Ghost Dog]

I inherited a legacy app and I need to find specific verbage which may exist
in code, queries, forms, or reports. Searching the code is trivial, and
searching the SQL property in the querydefs collection is similarly
straightforward. The problem is searching forms and reports. The original
developers used a number of IIF statements in the data properties of various
controls (text boxes, etc) where the specific verbage I'm looking for might
appear. I tried using the AllForms collection as follows:

Public Function DebugFindStuff()
Dim dbs As Object
Dim f As Form
Dim ctl As Control
Set dbs = Application.CurrentProject
Dim i%, j%, k%
For i = 0 To dbs.AllForms.Count
Set f = dbs.AllForms(i)
For j = 0 To f.Controls.Count
Set ctl = f.Controls(j)
Debug.Print ctl.Name
Next j
Next i
End Function

, but that doesn't work, probably because the form isn't loaded.
Anybody have a code snippet to loop through the properties
of all forms and reports?

 

[Allen Browne]

This shows how to loop through all forms in Access 2000 or later:

Function FixAllForms()
Dim accObj As AccessObject
Dim strDoc As String

For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
'Do something here
DoCmd.Close acForm, strDoc
Next
End Function

 

[Dirk Goldgar]

I inherited a legacy app and I need to find specific verbage which
may exist in code, queries, forms, or reports. Searching the code is
trivial, and searching the SQL property in the querydefs collection
is similarly straightforward. The problem is searching forms and
reports. The original developers used a number of IIF statements in
the data properties of various controls (text boxes, etc) where the
specific verbage I'm looking for might appear. I tried using the
AllForms collection as follows:

Public Function DebugFindStuff()
Dim dbs As Object
Dim f As Form
Dim ctl As Control
Set dbs = Application.CurrentProject
Dim i%, j%, k%
For i = 0 To dbs.AllForms.Count
Set f = dbs.AllForms(i)
For j = 0 To f.Controls.Count
Set ctl = f.Controls(j)
Debug.Print ctl.Name
Next j
Next i
End Function

, but that doesn't work, probably because the form isn't loaded.
Anybody have a code snippet to loop through the properties
of all forms and reports?

You do need to open the form (or report) in design view. Something like
this (for forms):

Dim ao As AccessObject
Dim f As Access.Form
Dim c As Access.Control
Dim strControlSource As String
Dim strRowSource As String

On Error Resume Next

For Each ao In CurrentProject.AllForms

DoCmd.OpenForm ao.Name, acDesign, WindowMode:=acHidden
Set f = Forms(ao.Name)

For each c in f.Controls

strControlSource = vbNullString
strRowSource = vbNullString
strControlSource = f.ControlSource
strRowSource = f.RowSource
' Note -- these will remain = vbNullString if the
' properties don't exist.

If InStr(strControlSource, WhatImLookingFor) > 0 Then
Debug.Print f.Name, c.Name, "ControlSource",
strControlSource
End If

If InStr(strRowSource, WhatImLookingFor) > 0 Then
Debug.Print f.Name, c.Name, "RowSource", strRowSource
End If
Next c

DoCmd.Close acForm, f.Name, acSaveNo
Set f = Nothing

Next ao

 

[Ghost Dog]

I appreciate your reply, but I think I'm just going to use the database
documentor tool and save the result as a PDF. That way I can just search the
PDF for occurrences of the verbage of interest. I need to make the changes
individually anyway. I do thank you for your response.

 

[Brendan Reynolds]

You might want to check out Rick Fisher's 'Find and Replace' utility ...
www.rickworld.com