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.
10 13270
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!
Thanks for the feedback. Its nice to know that the time spent on writing the article was not wasted. :)
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!
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?
I am guessing that AccessObject has only been added in version 2010. Replace the lines
with
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.
Many thanks as usual, Smiley!
Still getting the same error on the same lines:
Invalid qualifier
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.
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?
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....
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Davey |
last post by:
Is it possible to add new form controls such as <select> and <input>
controls using JavaScript - all client-side and on-the fly?
|
by: Lauren Quantrell |
last post by:
In terms of resources, wondering if it makes a difference in adopting
one of the three options below for resizing controls on a form and
controls on a subform at the same time.
option a.) Put...
|
by: Dries De Rudder |
last post by:
Hi,
I am trying to bind an object to form controls. I've got an object
MyObject which has some properties e.g. ID.
I've got a form, Form1, that contains a textbox ,TextBox1, now I want to...
|
by: Adam Right |
last post by:
Hi,
I want to get the controls of the windows form. I know that
form.Controls give me the list of controls but i have a problem. For
example, the toolstripmenu control cannot be reached with...
|
by: Ryan |
last post by:
Ok.. I have a form with lots of stuff on it; a tool strip panel, menu strip,
data binding elements (dataset, binding source, table adapter), tab control
with 7 tab pages, each page contains a...
|
by: DFS |
last post by:
'Search all reports for a text box named something
Dim cMsg As String, db As Database, rs As Recordset, rpt As Report, ctl as
Control
Set db = CurrentDb()
cMsg = "BuildingID"
Set rs =...
|
by: Mike |
last post by:
Hi,
I have a form with some controls, and a different class that needs to
modify some control properties at run time.
Hoy can I reference the from so I have access to its controls and...
|
by: dhtml |
last post by:
(originally mis-posted on m.p.s.jscript...)
I've just closed all windows in Firefox and its using 244MB of memory.
I have no idea why. I had GMail open, a page from unicode, the CLJ FAQ.
...
|
by: dhtml |
last post by:
I have written an article "Unsafe Names for HTML Form Controls".
<URL: http://jibbering.com/faq/names/ >
I would appreciate any reviews, technical or otherwise.
Garrett
--...
|
by: =?Utf-8?B?UmljaA==?= |
last post by:
A lot of users at my workplace use different screen resolutions, and I build
apps to use 1680 x 1050 pixels res by default. But some users are using 800
x 600, and the apps are too large for their...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| | |