505.369.1650 [email protected]

There are very sophisticated search programs to help you find occurrences of an object name or a piece of code within a MS Access database.  I usually find that this VBA code is sufficient to do the job.  It looks in tables, queries, code modules, form and report modules, record sources for forms and reports, and control sources for controls.  These procedures are also good examples of how to enumerate through collections of Access objects.

Code:

' basFindIn.bas Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected]
' Use (at your own risk) and modify freely as long as proper credit is given.

' Example
' If you want to find where "Contacts" is used in the database,
' put FindIn "Contacts" in the Immediate Window and press Enter.
' The screen will flash as objects (except macros) are opened and closed.
' The results will appear in the Immediate Window, each section starting
' with **, something like this:
'
' **FIND Table: Contact
' Contacts Name                                 [in the table Name]
' Contacts ContactID name                       [in the ContactID field Name]
' Contacts Contact name                         [in the Contact field Name]
' **FIND Query: Contact
' qryContactsUnique name                        [in the query Name]
' qryContactsUnique                             [in the query SQL]
' ~sq_cfrmContactResults~sq_ccboContactTaskID   [in a combo box Row Source]
' ~sq_cfrmContacts_Edit~sq_csfrContactTasks     [in a subform Record Source]
' **FIND Module: Contact
' basNetworkingDB                               [in the code module]
' Form_frmContacts_Edit                         [in the form code module]
' **FIND Form: Contact
' frmContacts_Edit RecordSource                 [in the form Record Source]
' frmContacts_Edit txtContactID controlsource   [in the control Control Source]
' **FIND Report: Contact
' rptContacts name                              [in the report Name]
' rptContacts RecordSource                      [in the report Record Source]
' rptContacts ContactID controlsource           [in the control Control Source]

Public Sub FindIn(WhatToFind As String)
' This procedure calls each "find" function.  Each function will
' print results in the Immediate Window.
On Error GoTo Err_FindIn
   
    FindInTable WhatToFind
    FindInQuery WhatToFind
    FindInModule WhatToFind
    FindInRSOrCS WhatToFind

Exit_FindIn:
    On Error Resume Next
    Exit Sub

Err_FindIn:
     Err.Number & " " & Err.Number
    Resume Exit_FindIn
End Sub

Public Sub FindInTable(WhatToFind As String)
' This procedure looks in a table name and field names for WhatToFind.
On Error GoTo Err_FindInTable
   
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
   
    ' Print the header.
     "**FIND Table: " & WhatToFind
    ' Loop through all the table definitions.
    For Each tbl In .
        ' If WhatToFind is in the table name, print.
        If InStr(1, tbl.Name, WhatToFind) > 0 Then
            Debug.Print tbl.Name & " name"
        End If
        ' Loop through all the  in the table.
        ' If WhatToFind is in a field name, print.
        For Each fld In tbl.
            If InStr(1, fld.Name, WhatToFind) > 0 Then
                Debug.Print tbl.Name & " " & fld.Name & " name"
            End If
        Next fld
    Next tbl

Exit Sub
Err_FindInTable:
    Debug.Print "Error " & Err.Number & " on " & tbl.Name & " " & Err.Description
    Resume Next
End Sub

Public Sub FindInQuery(WhatToFind As String)
' This procedure looks in a query name and SQL for WhatToFind.
On Error GoTo Err_FindInQuery
   
    Dim qdf As DAO.QueryDef
   
    ' Print the header.
    Debug.Print "**FIND Query: " & WhatToFind
    ' Loop through all the query definitions.
    For Each qdf In CurrentDb.
        ' If WhatToFind is in the query name, print.
        If InStr(1, qdf.Name, WhatToFind) > 0 Then
            Debug.Print qdf.Name & " name"
        End If
        ' If WhatToFind is in the query SQL, print.
        If InStr(1, qdf.SQL, WhatToFind) > 0 Then
            Debug.Print qdf.Name
        End If
    Next qdf

Exit Sub
Err_FindInQuery:
    Debug.Print "Error " & Err.Number & " on " & qdf.Name & " " & Err.Description
    Resume Next
End Sub

Public Sub FindInModule(WhatToFind As String)
' This procedure looks in code module and in form and report modules
' for WhatToFind.
On Error GoTo Err_FindInModule
   
    Dim mdl As Module
    Dim aob As 
   
    ' Print the header.
    Debug.Print "**FIND Module: " & WhatToFind
    ' Loop through all the code modules.
    For Each aob In CurrentProject.AllModules
        ' Open the module and get an object.
        DoCmd.OpenModule aob.Name
        Set mdl = Modules(aob.Name)
        ' If WhatToFind is in the code module, print.
        If mdl.Find(WhatToFind, 1, 1, 10000, 10000) Then
            Debug.Print mdl.Name
        End If
        ' Close the module, unless it is this one.
        If mdl.Name  "basFindIn" Then DoCmd.Close acModule, mdl.Name
    Next aob
   
    ' Loop through all the forms.
    For Each aob In CurrentProject.AllForms
        ' Open the form in design view.
        DoCmd.OpenForm aob.Name, acDesign
        ' Continue if it has a module.
        If Forms(aob.Name).HasModule Then
            ' Open the form module and get an object.
            DoCmd.OpenModule "Form_" & aob.Name
            Set mdl = Modules("Form_" & aob.Name)
            ' If WhatToFind is in the form module, print.
            If mdl.Find(WhatToFind, 1, 1, 10000, 10000) Then
                Debug.Print mdl.Name
            End If
        End If
        ' Close the form module.
        DoCmd.Close acModule, mdl.Name
        ' Close the form.
        DoCmd.Close acForm, aob.Name
    Next aob
   
    ' Loop through all the reports.
    For Each aob In CurrentProject.AllReports
        ' Open the report in design view.
        DoCmd.OpenReport aob.Name, acDesign
        ' Continue if it has a module.
        If Reports(aob.Name).HasModule Then
            ' Open the report module and get an object.
            DoCmd.OpenModule "Report_" & aob.Name
            Set mdl = Modules("Report_" & aob.Name)
            ' If WhatToFind is in the report module, print.
            If mdl.Find(WhatToFind, 1, 1, 10000, 10000) Then
                Debug.Print mdl.Name
            End If
        End If
        ' Close the report module.
        DoCmd.Close acModule, mdl.Name
        ' Close the report.
        DoCmd.Close acReport, aob.Name
    Next aob

Exit Sub
Err_FindInModule:
    Debug.Print "Error " & Err.Number & " on " & aob.Name & " " & Err.Description
    Resume Next
End Sub

Public Sub FindInRSOrCS(WhatToFind As String)
' This procedure looks in form and report record sources and in
' the control sources for the controls on those forms and reports
' for WhatToFind.
On Error GoTo Err_FindInRSOrCS

    Dim aob As 
    Dim ctl As Control
    Dim frm As Form
    Dim rpt As Report
   
    ' Print the header.
    Debug.Print "**FIND Form: " & WhatToFind
   
    ' Loop through all the forms.
    For Each aob In CurrentProject.AllForms
        ' Open the form in design view and get an object.
        DoCmd.OpenForm aob.Name, acDesign
        Set frm = Forms(aob.Name)
        ' If WhatToFind is in the form record source, print.
        If InStr(1, frm.RecordSource, WhatToFind) > 0 Then
            Debug.Print aob.Name & " recordsource"
        End If
       
        ' Loop through all the controls on the form.
        For Each ctl In frm.Controls
            Select Case ctl.
            ' If WhatToFind is in the control source of the control, print.
            ' Some control types are excluded.
            Case acBoundObjectFrame, acCheckBox, acComboBox, acListBox, acOptionGroup, _
                acTextBox, acToggleButton
                If InStr(1, ctl.ControlSource, WhatToFind) > 0 Then
                    Debug.Print aob.Name & " " & ctl.Name & " controlsource"
                End If
            Case Else
                ' Do nothing
            End Select
        Next ctl
       
        ' Close the form.
        DoCmd.Close acForm, aob.Name
    Next aob
   
    ' Print the header.
    Debug.Print "**FIND Report: " & WhatToFind
   
    ' Loop through all the reports.
    For Each aob In CurrentProject.AllReports
        ' Open the report in design view and get an object.
        DoCmd.OpenReport aob.Name, acDesign
        Set rpt = Reports(aob.Name)
        ' If WhatToFind is in the report record source, print.
        If InStr(1, rpt.RecordSource, WhatToFind) > 0 Then
            Debug.Print aob.Name
        End If
       
        ' Loop through all the controls on the report.
        For Each ctl In rpt.Controls
            Select Case ctl.
            ' If WhatToFind is in the control source of the control, print.
            ' Some control types are excluded.
            Case acBoundObjectFrame, acCheckBox, acComboBox, acListBox, acOptionGroup, _
                acTextBox, acToggleButton
                If InStr(1, ctl.ControlSource, WhatToFind) > 0 Then
                    Debug.Print aob.Name & " " & ctl.Name & " controlsource"
                End If
            Case Else
                ' Do nothing
            End Select
        Next ctl
       
        ' Close the report.
        DoCmd.Close acReport, aob.Name
    Next aob

Exit Sub
Err_FindInRSOrCS:
    Debug.Print "Error " & Err.Number & " on " & aob.Name & " " & Err.Description
    Resume Next
End Sub

Download Code:

basMinimizeRibbon.zip

Making Microsoft Access Work

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.