Search QueryDefs, Reports and Form controls for a string

[TheSmileyCoder]

Introduction
This article will explain how you can search your current application for a specified string.

Not often, but once in a while I need to change a field name, in order to for it to make more sense, often because I need to add an extra field, and with the new field, there might be ambiguity as to the meaning of each field.

Therefore I made a simple bit of code that will check (not auto-correct) your QueryDefs, the control source of controls in your your forms and reports.

Note that stored queries (such as a forms Recordsource, or a combobox Rowsource) are stored in the QueryDefs collection, and as such are included in this search.



Implementation:
I have created it as three separate functions, one for queries,one for forms, and one for reports, as well as a function combining the use of all three. The functions are shown below:

For Queries

Public Sub SearchQueryDefs(strSearchWord As String)
   Dim qdf As DAO.QueryDef
   Dim strSQL As String
   For Each qdf In CurrentDb.QueryDefs
      strSQL = qdf.SQL
      If InStr(1, strSQL, strSearchWord, vbTextCompare) > 0 Then
         Debug.Print "Query: " & qdf.Name
      End If
 
   Next
   Set qdf = Nothing
End Sub

Example output looks like:

Query: ~sq_ffrm_List_My_Reviews
Query: ~sq_ffrm_ListReviewsByDate
Query: qry_ExportReviewOverviewToExcel
Query: qry_ReviewDoc

You may notice the ~sq which means this is a stored query, in this first case its the underlying recordsource for my frm_List_My_Reviews.

On occasion while using this you might find that a query which has been deleted is returned. This is due to the way access stores/caches the queries. You can usually remove these by a Compact And Repair operation. If not this could be a sign of database corruption.

For Forms
For forms, I open each form in design view, and loop over the controls collection. I only check Textbox, combobox, listbox and checkbox, but if needed it could be expanded to check such items as bound pictures. Finally the form is closed again, ensuring that no changes are saved. BEFORE RUNNING you should close all forms.

Public Sub searchForms(strSearchWord As String)
   Dim oAO As Object
   Dim frm As Form
   Dim ctrl As Object
   For Each oAO In CurrentProject.AllForms
      DoCmd.OpenForm oAO.Name, acDesign
      Set frm = Forms(oAO.Name)
      For Each ctrl In frm.Controls
         Select Case ctrl.ControlType
 
            Case acTextBox, acComboBox, acListBox, acCheckBox
               If InStr(1, ctrl.ControlSource & "", strSearchWord) Then
                  Debug.Print "Form: " & frm.Name & ": " & ctrl.Name
               End If
 
         End Select
      Next
      DoCmd.Close acForm, oAO.Name, acSaveNo
   Next
 
 
   Set oAO = Nothing
   Set frm= Nothing
   Set ctrl = Nothing
End Sub

Example output looks like:

Form: frm_ReviewDetails: tb_DateSubmitted
Form: frm_Obs: tb_DateSubmitted

For Reports
This implementation is quite similar to the how the forms are searched exept I loop over the AllReports collection.

Public Sub searchReports(strSearchWord As String)
   Dim oAO As Object
   Dim rpt As Report
   Dim ctrl As Object
   For Each oAO In CurrentProject.AllReports
      DoCmd.OpenReport oAO.Name, acDesign
      Set rpt = Reports(oAO.Name)
      For Each ctrl In rpt.Controls
         Select Case ctrl.ControlType
 
            Case acTextBox, acComboBox, acCheckBox
               If InStr(1, ctrl.ControlSource & "", strSearchWord) Then
                  Debug.Print "Report:" & rpt.Name & ": " & ctrl.Name
               End If
 
         End Select
      Next
      DoCmd.Close acReport, oAO.Name, acSaveNo
   Next
 
 
   Set oAO = Nothing
   Set rpt = Nothing
   Set ctrl = Nothing

Example Output:

Report:rep_Main: tb_DateSubmitted
Report:supRep_Obs: tb_DateSubmitted

Modules
While it is possible to loop over the modules and check them in a similar way, its easy enough to use standard Find built into the VB-Environment so I have not made code for this.

Combine the 3 into a single call
For ease of use, I choose to combine the 3 functions into a single call:

Public Sub SearchDBObjects(strSearchWord As String)
    SearchQueryDefs strSearchWord
    searchForms strSearchWord
    searchReports strSearchWord
End Sub

I hope this can be usefull to someone. If you found this article usefull please post here, link to it, share it. You are welcome to use any and all parts of the code shown.

[twinnyfo]

Wow, Smiley! You read my mind. I was just thinking about posting this as a question, as I have had to do manual searches in the past. What a time saver! Thanks for your article!

[TheSmileyCoder]

Thanks for the feedback. Its nice to know that the time spent on writing the article was not wasted. :)

[twinnyfo]

And........... Thanks to this handy dandy little code, I just had to use it this morning. Rather than searching 400 queries by hand, I could now identify the 60 queries affected ....

Endless time save, Smiley! Thanks again!

[twinnyfo]

Smiley,

Your code above works for the QueryDefs, but when I look in the Reports and Forms, I get an error when I get to this line:

For Each oAO In CurrentProject.AllForms
For Each oAO In CurrentProject.AllReports
 

When I checked it at home, it worked fine. Office has 2007, Home has 2010.

Any ideas why these would behave differently?

[TheSmileyCoder]

I am guessing that AccessObject has only been added in version 2010. Replace the lines

Dim oAO As AccessObject

with

Dim oAO As Object

and the code should run fine in Access 2007.

I will update the original post to be compatible with Access 2007. I do believe that this new version is Ac2000+ compatible.

[twinnyfo]

Many thanks as usual, Smiley!

[twinnyfo]

Still getting the same error on the same lines:

Invalid qualifier

[twinnyfo]

Here is what I have found to get rid of any errors:

Public Sub SearchForms(strSearchWord As String)
    Dim dbs As Object
    Dim oAO As AccessObject
    Dim frm As Form
    Dim ctrl As Object
    Set dbs = Application.CurrentProject
    For Each oAO In dbs.AllForms
        DoCmd.OpenForm oAO.Name, acDesign
        Set frm = Forms(oAO.Name)
        For Each ctrl In frm.Controls
            Select Case ctrl.ControlType
                Case acTextBox, acComboBox, acListBox, acCheckBox
                    If InStr(1, ctrl.ControlSource & "", strSearchWord) Then
                        Debug.Print "Form: " & frm.Name & ": " & ctrl.Name
                    End If
            End Select
        Next
        DoCmd.Close acForm, oAO.Name, acSaveNo
    Next
    Set oAO = Nothing
    Set frm = Nothing
    Set ctrl = Nothing
End Sub
 

The report version of this code works and works on most of my forms, but one one particular form, I get a strange error:

You entered an expression that has an invlaid reference to the property ControlSource.

Any ideas? I'm trying to figure out which control and where this error is occuring.

[twinnyfo]

It appears that checkboxes within option groups do not have a control source. The option group itself has a control source, but the individual checkboxes do not. How do we check to see if a control has a particular property available?

[twinnyfo]

Also, just thinking about this, Smiley.... You mentioned in the original post that it is easy enough to search modules for a string. however, if one has several modules, and many forms and reports with modules, it would entail searching each module individually. I tried playing with the code for the modules, but admittedly, I have no idea where to start....