Search for Text Strings in Access Modules, Forms and Reports
The code below searches for strings in modules, forms and reports. This is a very simplified version of Rick Fisher's "Find and Replace" application.
It is usefule to find references to variables that may populate multiple modules, forms and reports.
LIst All Module and Form Names
Option Compare Database
Option Explicit
' *****************************************
' Search And Print All Module Names
' *****************************************
Sub ListAllModules()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
For Each obj In dbs.AllModules
Debug.Print obj.Name
Next obj
End Sub
Sub ListAllForms()
' *****************************************
' Search And Print All Form Names
' *****************************************
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
Debug.Print obj.Name
Next obj
End Sub
Courtesy of Han Vegelaar:
Partially Working: Needs Additional Code
Sub TestSearch()
Dim s As String
s = InputBox("Enter the text to search for")
If s <> "" Then
SearchText s
End If
End Sub
Sub SearchText(s As String)
Dim obj As AccessObject
Dim sl As Long, sc As Long, el As Long, ec As Long
For Each obj In CurrentProject.AllForms
DoCmd.OpenForm FormName:=obj.Name, View:=acDesign, WindowMode:=acHidden
With Forms(obj.Name)
If .HasModule Then
If .Module.Find(s, sl, sc, el, ec) Then
Debug.Print "Form: " & obj.Name & " line " & sl
End If
End If
End With
DoCmd.Close ObjectType:=acForm, ObjectName:=obj.Name, Save:=acSavePrompt
Next obj
For Each obj In CurrentProject.AllReports
DoCmd.OpenReport ReportName:=obj.Name, View:=acDesign, WindowMode:=acHidden
With Reports(obj.Name)
If .HasModule Then
If .Module.Find(s, sl, sc, el, ec) Then
Debug.Print "Report: " & obj.Name & " line " & sl
End If
End If
End With
DoCmd.Close ObjectType:=acReport, ObjectName:=obj.Name, Save:=acSavePrompt
Next obj
For Each obj In CurrentProject.AllModules
DoCmd.OpenModule ModuleName:=obj.Name
With Modules(obj.Name)
If .Find(s, sl, sc, el, ec) Then
Debug.Print "Module: " & obj.Name & " line " & sl
End If
End With
On Error Resume Next
DoCmd.Close ObjectType:=acModule, ObjectName:=obj.Name, Save:=acSavePrompt
On Error GoTo 0
Next obj
End Sub