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
Example output looks like:
You may notice the
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.
Example output looks like:
For Reports
This implementation is quite similar to the how the forms are searched exept I loop over the AllReports collection.
Example Output:
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:
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.
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
Code:
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
Code:
Query: ~sq_ffrm_List_My_Reviews Query: ~sq_ffrm_ListReviewsByDate Query: qry_ExportReviewOverviewToExcel Query: qry_ReviewDoc
~sq which means this is a stored query, in this first case its the underlying recordsource for my frm_List_My_Rev iews.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.
Code:
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
Code:
Form: frm_ReviewDetails: tb_DateSubmitted Form: frm_Obs: tb_DateSubmitted
This implementation is quite similar to the how the forms are searched exept I loop over the AllReports collection.
Code:
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
Code:
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:
Code:
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.
Comment