Quick, Easy method to find which Reports, Forms and Queries use a field!

Question

Good Afternoon,

I am looking for a way to see all objects using a particular Yes/No checkbox field.

Anyone know a good method?

I have code for finding embedded macros Dirk Goldgar gave me maybe it can be modified to do this I just don't know where I would changes.

Option Compare Database
Option Explicit
Sub ListAllEmbeddedMacros()
    Dim ao As AccessObject
    Dim strObjectType As String
    Dim strMacros As String
    
    strObjectType = "Form"
    For Each ao In CurrentProject.AllForms
        DoCmd.OpenForm ao.Name, acDesign, WindowMode:=acHidden
        strMacros = ListEmbeddedMacros(Forms(ao.Name))
        If Len(strMacros) > 0 Then
            Debug.Print "* Embedded macros in "; strObjectType; " "; ao.Name
            Debug.Print strMacros
        End If
        DoCmd.Close acForm, ao.Name, acSaveNo
    Next ao
    
    strObjectType = "Report"
    For Each ao In CurrentProject.AllReports
        DoCmd.OpenReport ao.Name, acDesign, WindowMode:=acHidden
        strMacros = ListEmbeddedMacros(Reports(ao.Name))
        If Len(strMacros) > 0 Then
            Debug.Print "* Embedded macros in "; strObjectType; " "; ao.Name
            Debug.Print strMacros
        End If
        DoCmd.Close acReport, ao.Name, acSaveNo
    Next ao
End Sub
Function ListEmbeddedMacros(FormOrReport As Object) As String
    Dim ctl As Access.Control
    Dim prp As Property
    Dim strPropertyValue As String
    Dim strResult As String
    
    On Error Resume Next  ' In case not all properties have values
    
    For Each ctl In FormOrReport.Controls
        For Each prp In ctl.Properties
            strPropertyValue = vbNullString
            strPropertyValue = prp.Value & vbNullString
            If strPropertyValue = "[Embedded Macro]" Then
                strResult = strResult & vbCrLf & _
                    ctl.Name & " : " & prp.Name
            End If
        Next prp
    Next ctl
    
    ListEmbeddedMacros = Mid(strResult, 3)
End Function

---

Chris Ward

Answer 1

Are you looking to see which check box controls are bound to a particular boolean field, or are you simply looking for which check box controls have a specific name?

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 2

Thanks Doug,

I am looking for a way to see where a Yes/No field from a Table is used in any object.

Products Table has a Checkbox "No-Action Taken"

I want to know every Query, Report, or other object is using this field for information.

This field is being removed from the Table and before it is I want to make sure there are no dependancies I have overlooked.

Thank you.

---

Chris Ward

Answer 3

Hi Chris,

I use the following code to locate any instance of a specified value in any form, report, macro or query;

Private Sub FindStringAll()
    Dim sValue      As String
    Dim sErr        As String
    
    Const PROC      As String = "FindStringAll"
    
    On Error GoTo ErrHandler
    
    Application.Echo False
    
    sValue = "q_RPT_Detail3"
    
    Debug.Print "Searching for '" & sValue & "'..."
    
    Debug.Print "Processing queries..."
    DoEvents
    Call FindStringQueries(sValue)
    
    Debug.Print "Processing forms..."
    DoEvents
    Call FindValueForms(sValue)

    Debug.Print "Processing reports..."
    DoEvents
    Call FindValueReports(sValue)
    
    Debug.Print "Processing modules..."
    DoEvents
    Call FindStringModules(sValue)
    
ErrHandler:
    Debug.Print
    
    Application.Echo True
    
    Select Case Err.Number
    Case 0, vbObjectError + 100
        'No error or error has already been reported...
        MsgBox "Completed search the database for '" & sValue & "'.", vbInformation + vbOKOnly, PROC
    Case Else
        sErr = "The following error occurred;" & vbCrLf & vbCrLf _
             & "Source: " & Err.Source & vbCrLf _
             & "Number: " & CStr(Err.Number) & vbCrLf _
             & "Description: " & Err.Description

        MsgBox sErr, vbInformation + vbOKOnly, PROC
    End Select
End Sub


Public Sub FindValueForms(ByVal value As String)
    Dim sValue  As String
    Dim sFrm    As String
    Dim sErr    As String
    Dim lErr    As Long
    Dim oFrm    As AccessObject
    Dim ctl     As Control
    Dim prp     As Property
    
    Const PROC  As String = "FindValueForms()"
    
    On Error GoTo ErrHandler
    
    For Each oFrm In CurrentProject.AllForms
        sFrm = vbNullString
        DoCmd.OpenForm oFrm.Name, acDesign
        
        'Check the form was load - corrupted forms won't load...
        If oFrm.IsLoaded Then
            For Each prp In Forms(oFrm.Name).Properties
                sValue = Nz(prp.value, vbNullString)
                
                If Len(sValue) > 0 Then
                    If sValue Like "*" & value & "*" Then
                        If sFrm = vbNullString Then
                            sFrm = oFrm.Name
                            Debug.Print "Form: " & oFrm.Name
                        End If
                        
                        Debug.Print Space(5) & "Property: " & prp.Name
                        Debug.Print Space(5) & "Value   : " & Replace(sValue, vbCrLf, " ")
                        Call DisplayQualifier(Forms(oFrm.Name).Properties, 5)
                        Debug.Print
                    End If
                End If
            Next prp
            
            For Each ctl In Forms(oFrm.Name).Controls
                For Each prp In ctl.Properties
                    sValue = Nz(prp.value, vbNullString)
                    
                    If Len(sValue) > 0 Then
                        If sValue Like "*" & value & "*" Then
                            If sFrm = vbNullString Then
                                sFrm = oFrm.Name
                                Debug.Print "Form: " & oFrm.Name
                            End If
                            
                            Debug.Print Space(5) & "Control: " & ctl.Name
                            Debug.Print Space(10) & "Property: " & prp.Name
                            Debug.Print Space(10) & "Value   : " & Replace(sValue, vbCrLf, " ")
                            Call DisplayQualifier(ctl.Properties, 10)
                            Debug.Print
                        End If
                    End If
                Next prp
            Next ctl
        End If
        
        DoCmd.close acForm, oFrm.Name, acSaveNo
        DoEvents
    Next oFrm

CleanUp:
    On Error Resume Next
    If lErr <> 0 Then MsgBox sErr, vbInformation + vbOKOnly, PROC
    Set oFrm = Nothing
    
ExitProc:
    On Error GoTo 0
    If lErr <> 0 Then Err.Raise vbObjectError + 100
    Exit Sub
    
ErrHandler:
    Select Case Err.Number
    Case 2004, 2186, 2196, 2683, 7751
        '2004   -   There isn't enough memory to perform this operation. Close unneeded programs and try the operation again
        '2186   -   This property isn't available in Design view
        '2196   -   Can't retrieve the value of this property
        '2683   -   There is no object in this control
        '7751   -   In Design view, you can't retrieve the value of the ObjectPalette property for an OLE object contained in a bound object frame
        If Err.Number = 2004 Then
            Debug.Print "Form: " & oFrm.Name
            Debug.Print Space(5) & "!!! Warning - Failed to load the form !!!"
            Debug.Print Space(5) & "This could indicate the form is corrupted"
            Debug.Print
        End If
        
        sValue = vbNullString
        Resume Next
    Case Else
        lErr = Err.Number
        sErr = Err.Description
    
        Debug.Print "Form: " & oFrm.Name
        Debug.Print Space(5) & Err.Description
        Debug.Print
    
        If Not oFrm.IsLoaded Then
            lErr = 0
            Resume Next
        End If
    End Select
    
    Debug.Assert lErr = 0
    
    Resume CleanUp
    Resume
End Sub


Public Sub FindValueReports(ByVal value As String)
    Dim sValue  As String
    Dim sRpt    As String
    Dim sErr    As String
    Dim lErr    As Long
    Dim oRpt    As AccessObject
    Dim ctl     As Control
    Dim prp     As Property
    
    Const PROC  As String = "FindValueReports()"
    
    On Error GoTo ErrHandler
    
    For Each oRpt In CurrentProject.AllReports
        sRpt = vbNullString
        DoCmd.OpenReport oRpt.Name, acDesign
        
        If oRpt.IsLoaded Then
            For Each prp In Reports(oRpt.Name).Properties
                sValue = Nz(prp.value, vbNullString)
                
                If Len(sValue) > 0 Then
                    If sValue Like "*" & value & "*" Then
                        If sRpt = vbNullString Then
                            sRpt = oRpt.Name
                            Debug.Print "Report: " & sRpt
                        End If
                        
                        Debug.Print Space(5) & "Property: " & prp.Name
                        Debug.Print Space(5) & "Value   : " & Replace(sValue, vbCrLf, " ")
                        Call DisplayQualifier(Reports(oRpt.Name).Properties, 5)
                        Debug.Print
                    End If
                End If
            Next prp
            
            For Each ctl In Reports(oRpt.Name).Controls
                For Each prp In ctl.Properties
                    sValue = Nz(prp.value, vbNullString)
                    
                    If Len(sValue) > 0 Then
                        If sValue Like "*" & value & "*" Then
                            If sRpt = vbNullString Then
                                sRpt = oRpt.Name
                                Debug.Print "Report: " & sRpt
                            End If
                            
                            Debug.Print Space(5) & "Control: " & ctl.Name
                            Debug.Print Space(10) & "Property: " & prp.Name
                            Debug.Print Space(10) & "Value   : " & Replace(sValue, vbCrLf, " ")
                            Call DisplayQualifier(ctl.Properties, 10)
                            Debug.Print
                        End If
                    End If
                Next prp
            Next ctl
        End If
        
        DoCmd.close acReport, oRpt.Name, acSaveNo
        DoEvents
    Next oRpt

CleanUp:
    On Error Resume Next
    If lErr <> 0 Then MsgBox sErr, vbInformation + vbOKOnly, PROC
    Set oRpt = Nothing
    
ExitProc:
    On Error GoTo 0
    If lErr <> 0 Then Err.Raise vbObjectError + 100
    Exit Sub
    
ErrHandler:
    Select Case Err.Number
    Case 2004, 2186, 2196, 2683, 7751
        '2004   -   There isn't enough memory to perform this operation. Close unneeded programs and try the operation again
        '2186   -   This property isn't available in Design view
        '2196   -   Can't retrieve the value of this property
        '2683   -   There is no object in this control
        '7751   -   In Design view, you can't retrieve the value of the ObjectPalette property for an OLE object contained in a bound object frame
        If Err.Number = 2004 Then
            Debug.Print "Form: " & oRpt.Name
            Debug.Print Space(5) & "!!! Warning - Failed to load the report !!!"
            Debug.Print Space(5) & "This could indicate the report is corrupted"
            Debug.Print
        End If
        
        sValue = vbNullString
        Resume Next
    Case Else
        lErr = Err.Number
        sErr = Err.Description
    End Select
    
    Debug.Assert lErr = 0
    
    Resume CleanUp
    Resume
End Sub


Private Sub FindStringQueries(ByVal value As String)
    Dim sValue      As String
    Dim bNamed      As Boolean
    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef
    Dim prp         As DAO.Property
    
    Set db = CurrentDb
    
    For Each qdf In db.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then
            bNamed = False
            On Error Resume Next
            
            For Each prp In qdf.Properties
                sValue = vbNullString
                sValue = prp.value
                
                If InStr(sValue, value) Then
                    If Not bNamed Then
                        Debug.Print "Query: " & qdf.Name
                        bNamed = True
                    End If
                    
                    Debug.Print Tab(5); "Property: " & prp.Name
                End If
            Next prp
            On Error GoTo 0
        End If
    Next qdf
End Sub


Public Sub FindStringModules(ByVal value As String)
    Dim sProj       As String
    Dim sMod        As String
    Dim sProc       As String
    Dim sErr        As String
    Dim iCount      As Integer
    Dim lErr        As Long
    
    Dim vbProj      As VBProject
    Dim vbComp      As VBComponent
    Dim vbMod       As CodeModule
    
    Const PROC      As String = "FindStringModules"
    
    On Error GoTo ErrHandler
    
    For Each vbProj In Application.VBE.VBProjects   'Loop through each project
        If vbProj.Protection = vbext_pp_none Then
           For Each vbComp In vbProj.VBComponents      'Loop through each module
               Set vbMod = vbComp.CodeModule
               
               iCount = 1
               
               Do While iCount < vbMod.CountOfLines
                   If Len(Trim(vbMod.Lines(iCount, 1))) > 0 Then
                   
                       If (InStr(LCase(vbMod.Lines(iCount, 1)), LCase(value)) > 0) Then
                           If sProc <> "FindStringAll_Test" Then
                               If sProj <> vbProj.Name Then
                                   sProj = vbProj.Name
                                   sMod = vbNullString
                                   sProc = vbNullString
                                   Debug.Print
                                   Debug.Print "Project  : " & sProj
                               End If
                               
                               If sMod <> vbComp.Name Then
                                   sMod = vbComp.Name
                                   sProc = vbNullString
                                   Debug.Print
                                   Debug.Print "Module   : " & sMod '& " :: " & vbMod.CountOfLines & " lines"
                               End If
                               
                               If sProc <> vbMod.ProcOfLine(iCount, vbext_pk_Proc) Then
                                   sProc = vbMod.ProcOfLine(iCount, vbext_pk_Proc)
                                   Debug.Print
                                   Debug.Print "Procedure: " & sProc
                               End If
                               
                               Debug.Print Space(5) & "Line " & CStr(iCount) & ": " & Trim(Replace(vbMod.Lines(iCount, 1), vbTab, vbNullString))
                           End If
                       End If
                   End If
                   
                   iCount = iCount + 1
               Loop
    
               Set vbMod = Nothing
           Next vbComp
        End If
    Next vbProj

CleanUp:
    On Error Resume Next
    If lErr <> 0 Then MsgBox sErr, vbExclamation + vbOKOnly, PROC
    
ExitProc:
    On Error GoTo 0
Exit Sub
    
ErrHandler:
    Debug.Assert Err.Number = 0
    Resume
End Sub

Answer 4

While you can try Ray's approach, I'd actually suggest getting a third-party product that can do deep searches for you, such as Rick Fisher's Find and Replace At $37 a copy, if it save you a half-hour, it's paid for itself!

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 5

You can also try a free utility I posted on UtterAccess (SearchForText).  That utility is a single form you import into your database.  You enter a string pattern to search, choose the object types you want to search, then execute the search.  The utility will tell you everyplace the string pattern is found.

If you want to try a "beta" version that is an update (that I use in A2010) of the one on UA, plus it offers "Replace" functionality, I would be glad to post a copy on SkyDrive.

---

Brent Spaulding | Access MVP

Answer 6

I use the following code to locate any instance of a specified value in any form, report, macro or query;

Good Morning Ray,

When compiling your code I get the following Error

Compile Error - Sub or function not defined - this error occurs on the line

Call DisplayQualifier(Forms(oFrm.Name).Properties, 5)

can you help me with this code please?

---

Chris Ward

Answer 7

Good Morning Brent,

Your post didn't show until 20 minutes after my last post...strange, it's not the first time this has happened. I am very interested in your beta version however I think it will be the cause of much misery if I import an A2010 into my A2007. Is it posible you have a version for 2007.

I was really hoping for something more along the lines of this little code but I don't really know how to make it correct.

Option Compare Database
Option Explicit
Dim ctl As Access.Control
Dim AcControlType As CheckBox
Dim CheckBox As AcControlType
Function AP_FindCtl(Cancel As Integer)
    AcFindField = CheckBox = "No-ActionTaken"
End Function

---

Chris Ward

Answer 8

Hi Brent,

I would like to try this because I can always try to open as is is and if I can then it will be compatible with A2007 as it is what I have to open with. Also I would only use it in a copy and once it identifies the places then I manually adjust the original. Also if you could place your earlier verson on Skydrive I could also try it. I can't seem to dowload from UA.

Thanks Much!

---

Chris Ward

Answer 9

Here is a SkyDrive link that contains a Zip folder named SearchForText.  What you do is extract the SearchForText.mdb from the Zip file, then import the Form object named "xdlgSearchForText_v40" into your database application.st open the Form object within your application.  It should be fairly self-explanatory/discoverable (but I wrote it --- so its easy for me :) ).  The usage instructions on the UA link may be useful, but do need updating.

Please let me know what you think, and if it helps you out, or if you have any issues/questions with the code.

---

Brent Spaulding | Access MVP

Answer 10

Very Cool Brent!!!

Can you tell me what the results mean?

_____________________________________

In Queries alone there are;

NameMap (32 instances)

Name (1)

DOL (6)

I get the parts where it says at this SQL Character 168 or some other number

_______________________________________

Forms - Makes Sense

Macros - Will not function Kills the db when you try hangs on Autoexec.mcr' Error 2220 on this line SaveAsText acMacro, doc.Name, strTempFile

Reports - Wow without this I would not have found why that Report wasn't working there was an eroneous filter on the Report that called for this field but the field wasn't even in the sql for the Report.

Wow, Brent you should charge money for things like this!

---

Chris Ward

Answer 11

Thanks for the feedback Chris!!! ... I will work on the Macro bug, I just changed that logic, so I will see what I can do and let you know when I fix it (or give up for the time being :) ).  One question for you though, can you tell me what the value of strTempFile is when it crashes?  The code is supposed to write a file to the users temporary folder, but if one is not defined in the environment, then

NameMap: That is a property that is related to the AutoCorrect and dependancy stuff. The property value is managed by Access.

Name: That is the name property of the Query object (what you see in the NavPane).

DOL: Used in AutoCorrect. The property value is managed by Access.

>> Wow, Brent you should charge money for things like this! <<

Thanks for the encouragement! ... Maybe one day .. maybe version 5.0!

---

Brent Spaulding | Access MVP

Answer 12

Found the error ...

In the SearchMacros procedure:

Change From:

strTempFile = "~" & Environ("TEMP") & "\" & doc.Name & ".mcr"

To:

strTempFile = Environ("TEMP") & "\~" & doc.Name & ".mcr"

I will likely re-post on my SkyDrive is soon, but in a rush right now :-s ... so if you are comfortable changing the code then give it a whirl and let me know if that fixed it for you.

---

Brent Spaulding | Access MVP

Answer 13

One question for you though, can you tell me what the value of strTempFile is when it crashes?  The code is supposed to write a file to the users temporary folder, but if one is not defined in the environment, then

Brent Spaulding | Access MVP

What. More of my ignorance showing here. Where would I look?
I looked for *.temp, *.tmp and *Temporary* but I haven't looked for these much in W7 so maybe I need to look elsewhere?

---

Chris Ward

Answer 14

Right-click on Computer in the start menu and select Properties. Click on Advanced System Settings on the left. Click on Environment Variables on the Advanced tab. Are there definitions for TMP and TEMP?

Another way to set the same settings is to open a command prompt and type Set T. That will display the value of any environment variables starting with T.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 15

Hello Chris,

When I asked ...

"One question for you though, can you tell me what the value of strTempFile is when it crashes?"

I was meaning the VBA variable strTempFile in the SearchMacros procedure :-s ... I figured that would be the key to fixing the error that was raised.  That did indeed provide the key, thus the subsequent post on which line to change in the SearchMacros procedure.

I have posted an updated zip file on SkyDrive here

---

Brent Spaulding | Access MVP

Answer 16

Sorry Brent somehow I missed your subsequent post.

Thanks for the update.

Hi Doug. Here at work I don't have access to the advanced tab we can't even set desktop settings (har har) using the command prompt for "Set T" or "Set T." I get a syntax error. /Set T says that /Set is not recognized as an internal or external command, operable program or batch file.

---

Chris Ward

Answer 17

Hi Doug. Here at work I don't have access to the advanced tab we can't even set desktop settings (har har) using the command prompt for "Set T" or "Set T." I get a syntax error. /Set T says that /Set is not recognized as an internal or external command, operable program or batch file.

I can identify with not having access to the advanced tab, but I didn't think it was possible to lock down the DOS Set command! (But then Group Policy is an area I tended to stay away from...)

What about if you go the Access Immediate window (Ctrl-G) and enter ?Environ("Temp") and ?Environ("Tmp")?

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 18

Both point to the same place

Some folders\AppData\Local\Temp

There are 414 Folders\files within

7 from yesterdays date

OD files

TMP Files

CVR Files

Text Docs

---

Chris Ward

Answer 19

Hi Brent,

Just to comment further this was a great tool to have to find and redesign with. I was able to find over two hundred instances I was able to make a change to where some things I knew but others I didn't.

Even though I was able to correct many itmes there is still just a little question...

NameMap: That is a property that is related to the AutoCorrect and dependancy stuff. The property value is managed by Access.

I have removed the call everywhere I can find but it still shows here. Where is this that I might remove it. I am only finding NameMap in the Queries. No other objects have it since I modified them.

Or am I just stuck with it. Or is there a chance that temporarily turning off Autocorrect will expunge it?

Thanks!

---

Chris Ward

Answer 20

Hello Chris!

I am glad to hear that you like the utility!  I won't design/maintain a database application with out it!

With respect to the NameMap and AutoCorrect, I don't worry about the NameMap property.  Are you recieving errors because of it?  As a point of note, I don't, repeat DON'T, ever keep Name AutoCorrect options enabled.  The reason for this practice of mine (and others) is that AutoCorrect has been the *suspected* root cause of corruption issues and speed issues.  Granted I can only assume that MS is improving the AutoCorrect feature as time goes on, but at this point I still don't trust it completely.  Speaking of that practice, I *should* add the feature to automatically turn OFF autocorrect with the SearchForText utility because changing the name of objects programmatically may effect the AutoCorrect adversely -- I have not experimented enough to provide absolute effects.  So ... it is my *best guess* that turning off/on Name AutoCorrect will be a benefit for you -- but I my advise to you is to leave it off after you toggle it in an attempt to clean up the NameMap property.  If that does not clean up the property as you wish, then I would suggest that you create a new BLANK database --- turn OFF Name AutoCorrect, then import all of your objects into the new 'clean' database --- that should work wonders, but may not be worth the effort if you are not experiencing issues regarding the property.

---

Brent Spaulding | Access MVP

Answer 21

Did not receive any errors I just don't like having something in the db that doesn't do anything useful. Looks like turning AutoNameCorrecting off and back on did the trick. I have no hits on the search now.

Thanks Brent!

---

Chris Ward

Answer 22

FYI ... A bug was found in v41 when searching macro's.  I have updated the utility to v42 and posted it on my SkyDrive.  Please download the update here.

Thanks!

---

Brent Spaulding | Access MVP