Find string in ControlSource

[Guest]

On occasion, I have to rename a query after creating a reference to it in one
ormore ControlSource or DefaultValue of controls (generally text/combo boxes)
on forms. I found a way to look up such a string when it exists in a
RowSource by cycling through the QueryDefs collection.

How can I cycle through all controls on all forms, looking for a particular
string in the .ControlSource or .DefaultValue property?

 

[Dirk Goldgar]

On occasion, I have to rename a query after creating a reference to
it in one ormore ControlSource or DefaultValue of controls (generally
text/combo boxes) on forms. I found a way to look up such a string
when it exists in a RowSource by cycling through the QueryDefs
collection.

How can I cycle through all controls on all forms, looking for a
particular string in the .ControlSource or .DefaultValue property?

Here's a very unpolished and only lightly tested routine for the
purpose. You'll have to fix up any line breaks caused by the
newsreader.

'----- start of code -----
Sub SearchControlSourcesAndDefaultValues(strSought As String)

' Search the ControlSources and DefaultValue properties of all
' controls on all forms for the specified string.

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim doc As DAO.Document
Dim frm As Form
Dim ctl As Control

Dim lngFormCount As Long
Dim lngControlCount As Long
Dim lngFoundCount As Long
Dim lngControlFoundCount As Long
Dim strOpenForm As String
Dim strControlSource As String
Dim strDefaultValue As String

Debug.Print "*** Beginning search ..."

Set db = CurrentDb
For Each doc In db.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acHidden
strOpenForm = doc.Name
Set frm = Forms(strOpenForm)
With frm
lngFormCount = lngFormCount + 1
lngControlFoundCount = 0
For Each ctl In .Controls
On Error GoTo Err_NoSource
strControlSource = ctl.ControlSource
strDefaultValue = ctl.DefaultValue
On Error GoTo Err_Handler
lngControlCount = lngControlCount + 1
If InStr(strControlSource, strSought) Then
lngFoundCount = lngFoundCount + 1
lngControlFoundCount = lngControlFoundCount + 1
If lngControlFoundCount = 1 Then
Debug.Print "Form " & .Name & " -- string found
in control"
End If
Debug.Print , "Control " & ctl.Name & "
ControlSource: " & strControlSource
End If
If InStr(strDefaultValue, strSought) Then
lngFoundCount = lngFoundCount + 1
lngControlFoundCount = lngControlFoundCount + 1
If lngControlFoundCount = 1 Then
Debug.Print "Form " & .Name & " -- string found
in control"
End If
Debug.Print , "Control " & ctl.Name & "
DefaultValue: " & strDefaultValue
End If
Next_Control:
Next ctl
DoCmd.Close acForm, .Name
strOpenForm = vbNullString
End With
Set frm = Nothing
Next doc

Exit_Point:
On Error Resume Next
Set ctl = Nothing
Set doc = Nothing
Set db = Nothing
If Len(strOpenForm) > 0 Then
DoCmd.Close acForm, strOpenForm, acSaveNo
End If
Debug.Print "*** Searched " & lngFormCount & _
" forms and " & lngControlCount & " controls, found " &
_
lngFoundCount & " occurrences."
Exit Sub

Err_NoSource:
If Err.Number = 438 _
Or Err.Number = 2455 Then
Resume Next_Control
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

 

[Guest]

Thanks. That did it. Here is my foreshortened but fairly well tested version
that is built on your concept, for anyone later reading this post. I added a
bit of logic to make sure I do not open/close any forms that are already
open. I used MsgBox instead of Debug.Print because I am actually running the
code via a button on a form. It also searches through all queries for the
same string.

Dim db As Variant
Dim qdef As Variant
Dim srchStr As String
Dim cp As Variant
Dim frm As Variant
Dim frmOpen As Boolean
Dim frmCrnt As Variant
Dim frmName As String
Dim ctrl As Variant
Dim propCur As Variant
Dim defVal As Variant
Dim ctrlSrc As Variant

srchStr = InputBox("Enter search string.")
Set cp = CurrentProject
Set db = CurrentDb
'find string in .DefaultValue & .ControlSource
For Each frm In cp.AllForms
frmName = frm.Name
If frm.IsLoaded Then
frmOpen = True
Else
frmOpen = False
End If
If Not frmOpen Then DoCmd.OpenForm frmName, acDesign, , , , acHidden 'do
not open form if already open
Set frmCrnt = Forms(frmName)
With frmCrnt
For Each ctrl In frmCrnt.Controls
If ctrl.ControlType = acListBox Or ctrl.ControlType = acComboBox
Or ctrl.ControlType = acTextBox Then
Set defVal = ctrl.Properties("DefaultValue")
If defVal Like "*" & srchStr & "*" Then MsgBox frmName & "."
& ctrl.Name, vbInformation, "Default value"
Set ctrlSrc = ctrl.Properties("ControlSource")
If ctrlSrc Like "*" & srchStr & "*" Then MsgBox frmName &
"." & ctrl.Name, vbInformation, "Control source"
End If
Next
End With
If Not frmOpen Then DoCmd.Close acForm, frmName, acSaveNo 'do not close
form if it was already open
Next
MsgBox "Control search complete. Will now search queries.", , ""

'find string in queries
For Each qdef In db.QueryDefs
If qdef.SQL Like "*" & srchStr & "*" Then MsgBox qdef.Name
Next qdef
MsgBox "Finished searching queries.", , ""

 

[Dirk Goldgar]

Thanks. That did it. Here is my foreshortened but fairly well tested
version that is built on your concept, for anyone later reading this
post. I added a bit of logic to make sure I do not open/close any
forms that are already open. I used MsgBox instead of Debug.Print
because I am actually running the code via a button on a form. It
also searches through all queries for the same string.

Dim db As Variant
Dim qdef As Variant
Dim srchStr As String
Dim cp As Variant
Dim frm As Variant
Dim frmOpen As Boolean
Dim frmCrnt As Variant
Dim frmName As String
Dim ctrl As Variant
Dim propCur As Variant
Dim defVal As Variant
Dim ctrlSrc As Variant

srchStr = InputBox("Enter search string.")
Set cp = CurrentProject
Set db = CurrentDb
'find string in .DefaultValue & .ControlSource
For Each frm In cp.AllForms
frmName = frm.Name
If frm.IsLoaded Then
frmOpen = True
Else
frmOpen = False
End If
If Not frmOpen Then DoCmd.OpenForm frmName, acDesign, , , ,
acHidden 'do not open form if already open
Set frmCrnt = Forms(frmName)
With frmCrnt
For Each ctrl In frmCrnt.Controls
If ctrl.ControlType = acListBox Or ctrl.ControlType =
acComboBox Or ctrl.ControlType = acTextBox Then
Set defVal = ctrl.Properties("DefaultValue")
If defVal Like "*" & srchStr & "*" Then MsgBox
frmName & "." & ctrl.Name, vbInformation, "Default value"
Set ctrlSrc = ctrl.Properties("ControlSource")
If ctrlSrc Like "*" & srchStr & "*" Then MsgBox
frmName & "." & ctrl.Name, vbInformation, "Control source"
End If
Next
End With
If Not frmOpen Then DoCmd.Close acForm, frmName, acSaveNo 'do not
close form if it was already open
Next
MsgBox "Control search complete. Will now search queries.", , ""

'find string in queries
For Each qdef In db.QueryDefs
If qdef.SQL Like "*" & srchStr & "*" Then MsgBox qdef.Name
Next qdef
MsgBox "Finished searching queries.", , ""

Not bad! I particularly like the logic you inserted to avoid closing
any forms that are already open.

I note that you've decided to search only text boxes, combo boxes, and
list boxes. That seems rather restrictive to me, since there are
several other controls that could have controlsources and defaultvalues.
Be aware also that your use of the Like operator to check for your
search-string will preclude searching for any text that includes one of
the pattern-matching characters recognized by the Like operator.

 

[Guest]

Thanks for the tip on the Like operator. I think I will got to an InStr. I
included only those controls only because I was trying to avoid the error
that would occurif I referred to .ControlSource for a button or other control
for which that property does not exist. Since those three types are the only
ones I am using that have default values and/or control sources and refer to
queries, I could get away with it in this instance. Your code probably had a
way to get past that error, but I was in too much of a hurry to get back to
my program. I will look through it again today to see if I can integrate it.

I had just made some changes to my naming conventions that involved the name
of a query or two, and it is such a pain to find all the references to query
names in other queries, default values, and controls sources.

The closing logic was necessary because I run this from a hidden development
menu in each application, and it is nested two or three levels deep in the
application. I knew I would have a problem if I tried to re-open the form or
close it (or any of its precursory forms).

To finish it off, I just need a piece that will search through VBA for the
string automatically (any ideas?). Right now, I just do that part manually
(search/replace through VBA window).

One more (hopefully) little question. Any ideas on how I could send the
results to a report? I suppose I could use a temporary table and append each
entry line by line and then run a report from that table, but there must be
some more efficient way.

 

[Dirk Goldgar]

[...] I included only those controls only because I was trying to
avoid the error that would occurif I referred to .ControlSource for a
button or other control for which that property does not exist. Since
those three types are the only ones I am using that have default
values and/or control sources and refer to queries, I could get away
with it in this instance. Your code probably had a way to get past
that error, but I was in too much of a hurry to get back to my
program. I will look through it again today to see if I can integrate
it.

Yes, it did. In the control loop, it trapped for the errors that might
be raised if the properties didn't exist, and skipped that control if
those errors were raised.

I had just made some changes to my naming conventions that involved
the name of a query or two, and it is such a pain to find all the
references to query names in other queries, default values, and
controls sources.

There are third-party utilities that do this; among them these:

Find and Replace
Speed Ferret
Total Access Analyzer (FMS, Inc.)

I've never used any of them, though. For the most part, I don't
remember where you have to go to purchase them, but I'm sure you could
find them easily by googling, or checking various Access-related web
sites. Their prices vary widely.

The Name AutoCorrect feature of Access 2000 and later is intended to
deal with this problem, but it has so many other problems associated
with it that I generally tell people to turn it off.

The closing logic was necessary because I run this from a hidden
development menu in each application, and it is nested two or three
levels deep in the application. I knew I would have a problem if I
tried to re-open the form or close it (or any of its precursory
forms).

As I said, it's a nice touch.

To finish it off, I just need a piece that will search through VBA
for the string automatically (any ideas?). Right now, I just do that
part manually (search/replace through VBA window).

I haven't written any such code, but I guess you'd be working with the
Module object. You'll probably need to iterate through the
CurrentProject.AllModules collection as well as all the forms and
reports that have .HasModule = True.

One more (hopefully) little question. Any ideas on how I could send
the results to a report? I suppose I could use a temporary table and
append each entry line by line and then run a report from that table,
but there must be some more efficient way.

You could open a text file and write your results there. Or you could
insert each match into a table, and then open an Access report based on
that table. That's about all I can think of offhand.