Find all objects that use field name?

[lorirobn]

Hi,

I would like to search all forms, reports, and queries in one database for a particular field name. Is there a way to do this?

(I just downloaded an add-in tool called mdbdoc, but it didn't cross-reference to this level).

Thanks,
Lori

 

[lupins46]

Not in Access as it comes.
You can buy 3rd party tools - search for FMS or Speed Ferret as examples.

 

[FinalPrime]

Not in FMS. Just finished looking myself.

 

[Remou]

Hi
This code is not very satisfactory for a variety of reasons, but it might save time if you just wanted to see how many reports, forms and queries you need to update after changing a table.

Sub SearchReportsForField(fldname)
Dim rpt
Dim ctl
Dim strReportList
For Each rpt In CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign
    For Each ctl In Reports(rpt.Name).Controls
        If ctl.ControlType = acComboBox _
           Or ctl.ControlType = acListBox _
           Or ctl.ControlType = acTextBox Then
        If ctl.ControlSource <> "" Then
            If InStr(1, ctl.ControlSource, fldname) > 0 Then
            Debug.Print Reports(rpt.Name).Name; "  "; ctl.Name; "  "; ctl.ControlSource
        End If
        End If
        End If
    Next
    DoCmd.Close acReport, rpt.Name
Next
End Sub

Sub SearchFormsForField(fldname)
Dim frm
Dim ctl
For Each frm In CurrentProject.AllForms
    DoCmd.OpenForm frm.Name, acViewDesign
    For Each ctl In Forms(frm.Name).Controls
        If ctl.ControlType = acComboBox _
           Or ctl.ControlType = acListBox _
           Or ctl.ControlType = acTextBox Then
        If ctl.ControlSource <> "" Then
            If InStr(1, ctl.ControlSource, fldname) > 0 Then
            Debug.Print Forms(frm.Name).Name; "  "; ctl.Name; "  "; ctl.ControlSource
        End If
        End If
        End If
    Next
    DoCmd.Close acForm, frm.Name
Next
End Sub

Sub SearchQueriesForField(fldname)
Dim qdf
Dim ctl
For Each qdf In CurrentDb.QueryDefs
    If InStr(1, qdf.SQL, fldname) > 0 Then
        Debug.Print qdf.Name
    End If
Next
End Sub

 

[FinalPrime]

lorirobn.

This is the 'guts' of a function that I worked on this morning and has worked.
1) checking a "Test" table in an application.
2) Here, searching for the field name called "PIN".

SearchForFieldname = 0
Dim rstTmp As DAO.Recordset
Dim fldTmp As DAO.Field
Set rstTmp = dbsIn.OpenRecordset("Test", dbOpenDynaset)
rstTmp.MoveFirst

With rstTmp
For Each fldTmp In .Fields
' Does it match?
If fldTmp.Name = "PIN" Then
SearchForFieldname = 1
Exit Function

End If
Next fldTmp

End With