Dale Fye said:
As mentioned in earlier posts, I'm writing my own Search and Replace
application.
I've figured out how to do almost everything I need to search the various
objects, but have not figured out how to refer to the code modules
associated
with forms and reports. Anybody know the correct syntax for that?
Dale -
Here's code for one approach, not using the VBA Extensibility library.
Forgive the newsreader-introduced line breaks; this is rough code I threw
together a while ago for my own use:
'------ start of code ------
Sub SearchFormModules(strSought As String, Optional bWholeWord As Boolean)
' Search the class modules of all forms that have them,
' looking for the specified string.
' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.
On Error GoTo Err_SearchFormModules
Dim db As DAO.Database
Dim doc As DAO.Document
Dim frm As Access.Form
Dim mdl As Access.Module
Dim lngFormCount As Long
Dim lngFoundCount As Long
Dim bOpenedForm As Boolean
Debug.Print "*** Searching form modules for '" & strSought & "' ..."
Set db = CurrentDb
For Each doc In db.Containers("Forms").Documents
If CurrentProject.AllForms(doc.Name).IsLoaded = False Then
DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acHidden
bOpenedForm = True
Else
bOpenedForm = False
End If
Set frm = Forms(doc.Name)
With frm
If frm.HasModule Then
lngFormCount = lngFormCount + 1
lngFoundCount = lngFoundCount + SearchModule(frm.Module,
strSought, bWholeWord)
End If
If bOpenedForm Then
DoCmd.Close acForm, .Name, acSaveNo
End If
End With
Set frm = Nothing
Next doc
Exit_SearchFormModules:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngFormCount & _
" forms, found " & _
lngFoundCount & " occurrences."
Exit Sub
Err_SearchFormModules:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchFormModules
End Sub
Sub SearchReportModules(strSought As String, Optional bWholeWord As Boolean)
' Search the class modules of all reports that have them,
' looking for the specified string.
' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.
On Error GoTo Err_SearchReportModules
Dim db As DAO.Database
Dim doc As DAO.Document
Dim rpt As Access.Report
Dim mdl As Access.Module
Dim lngReportCount As Long
Dim lngFoundCount As Long
Dim bOpenedReport As Boolean
Debug.Print "*** Searching report modules for '" & strSought & "' ..."
Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
If CurrentProject.AllReports(doc.Name).IsLoaded = False Then
DoCmd.OpenReport doc.Name, acDesign, WindowMode:=acHidden
bOpenedReport = True
Else
bOpenedReport = False
End If
Set rpt = Reports(doc.Name)
With rpt
If rpt.HasModule Then
lngReportCount = lngReportCount + 1
lngFoundCount = lngFoundCount + SearchModule(rpt.Module,
strSought, bWholeWord)
End If
If bOpenedReport Then
DoCmd.Close acReport, .Name, acSaveNo
End If
End With
Set rpt = Nothing
Next doc
Exit_SearchReportModules:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngReportCount & _
" reports, found " & _
lngFoundCount & " occurrences."
Exit Sub
Err_SearchReportModules:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchReportModules
End Sub
Function SearchModule( _
mdl As Access.Module, _
strSought As String, _
Optional bWholeWord As Boolean) _
As Long
' Search module <mdl> for string <strSought>.
' Optional argument <bWholeWord> tells whether to report only
' occurrences of the string as a "whole word".
' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.
On Error GoTo Err_SearchModule
Dim bFound As Boolean
Dim lngFoundCount As Long
Dim lngStartLine As Long
Dim lngEndLine As Long
Dim lngStartCol As Long
Dim lngEndCol As Long
Dim lngProcType As Long
lngFoundCount = 0
lngStartLine = 0
lngEndLine = 0
With mdl
Do
lngEndLine = 0
lngStartCol = 0
lngEndCol = 0
bFound = .Find(strSought, lngStartLine, lngStartCol, lngEndLine,
lngEndCol, bWholeWord)
If bFound Then
lngFoundCount = lngFoundCount + 1
Debug.Print "Found in " & _
IIf(mdl.Type = acStandardModule, "standard", "class") &
_
" module " & .Name & ", line " & lngStartLine & ", proc
'" & .ProcOfLine(lngStartLine, lngProcType) & "'"
lngStartLine = lngStartLine + 1
End If
Loop While bFound
End With
Exit_SearchModule:
SearchModule = lngFoundCount
Exit Function
Err_SearchModule:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchModule
End Function
'------ end of code ------
