View Full Version : Search for a text trough all queries.


Guus2005
08-12-2010, 01:50 AM
Here's a function which searches for a string or a number of strings in all queries.
Open the immediate window and enter:

?SearchInQueryDefs2 "INT", "OUT", "DO"

The results will be displayed in the messagebox.

Tell me what you think.

Enjoy!

Public Sub SearchInQueryDefs2(ParamArray arrSearch())
'Search for all strings in the paramarray in all queries.
'Run from intermediate window.
'
'Example: SearchInQueryDefs2 "INT", "OUT", "DO"
'
Dim qdf As QueryDef
Dim qdfS As QueryDefs
Dim blnFound As Boolean
Dim intSearch As Integer
Dim intCount As Integer
Dim strFound As String
Dim intCtl As Integer

On Error GoTo Err_SearchInQueryDefs2

Set qdfS = CurrentDb.QueryDefs

intSearch = UBound(arrSearch, 1)

For Each qdf In qdfS
For intCount = 0 To intSearch
blnFound = InStr(1, qdf.SQL, arrSearch(intCount)) > 0
If Not blnFound Then
Exit For 'Not found, exit loop, continue next query
End If
Next intCount
If blnFound Then
Select Case Left$(qdf.Name, 5)
Case "~sq_f" ' rowsource form
strFound = "Form: " & Mid$(qdf.Name, 6) & vbCrLf
Case "~sq_c" ' rowsource control on form
intCtl = InStr(1, Mid$(qdf.Name, 2), "~")
strFound = "Form : " & Mid$(qdf.Name, 6, intCtl - 5) & vbCrLf & "Control: " & Mid$(qdf.Name, intCtl + 6) & vbCrLf
Case Else 'query
strFound = "Query: " & qdf.Name & vbCrLf
End Select

Debug.Print "Found string(s) in: " & qdf.Name
If vbNo = MsgBox("String(s) found in " & vbCrLf & vbCrLf & strFound & vbCrLf & qdf.SQL & vbCrLf & vbCrLf & "Confirm to continue the search, 'No' to stop", vbExclamation + vbYesNo, "SearchInQueryDefs") Then
Exit Sub
End If
End If
Next qdf

MsgBox "Done searching.", vbInformation

Exit_SearchInQueryDefs2:
Exit Sub

Err_SearchInQueryDefs2:
MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure SearchInQueryDefs2 of Module modUtility"
Resume Exit_SearchInQueryDefs2
Resume 'For debugging purposes

End Sub