none
How To Search For A String in all Form VBA and Modules RRS feed

  • Question

  • Hello:

    When I have to find a reference to a variable within an Access application that has many forms and modules, I have been opening each form and module one at a time and searching using the VBE Find command.  This gets very tedious when there are many forms and modules.

    Is there a way to just do one search and get a listing of each form and module that contains that string?

    Thanks,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, February 13, 2020 9:56 PM

Answers

  • I use Rick Fisher's add-in Find and Replace.

    Although it is not being actively developed any more, it still works in Access 2019.

    It is not free, but you can try it out for free during 30 days. If you do a lot of searching, it is well worth the price.

    It searches all Access objects that you specify, not just code modules.

    For a simpler version:

    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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RichLocus Thursday, February 13, 2020 11:20 PM
    Thursday, February 13, 2020 10:31 PM

All replies

  • I use Rick Fisher's add-in Find and Replace.

    Although it is not being actively developed any more, it still works in Access 2019.

    It is not free, but you can try it out for free during 30 days. If you do a lot of searching, it is well worth the price.

    It searches all Access objects that you specify, not just code modules.

    For a simpler version:

    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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RichLocus Thursday, February 13, 2020 11:20 PM
    Thursday, February 13, 2020 10:31 PM
  • There is a free utility V-Tools that may be helpful.
    Thursday, February 13, 2020 11:06 PM
  • Hans:

    As always, thanks!

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, February 13, 2020 11:21 PM