Go Back   Access World Forums > Microsoft Access Reference > Code Repository

Thread Tools Rate Thread Display Modes
Old 08-12-2010, 01:50 AM   #1
Guus2005's Avatar
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,498
Thanks: 45
Thanked 80 Times in 75 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Search for a text trough all queries.

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.

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 Sub

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

End Sub

Guus2005 is offline   Reply With Quote
The Following 3 Users Say Thank You to Guus2005 For This Useful Post:
hassanogaibi (06-28-2016), isladogs (04-07-2017), Lightwave (05-14-2017)
Old 10-20-2015, 05:36 AM   #2
Newly Registered User
Join Date: Oct 2015
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
ELF_VIGO is on a distinguished road
Re: Search for a text trough all queries.

Thank you very much. I have to search in queries "Formulaires" in order to substitute by "Forms" (An Access aplication from French version of Access...).
ELF_VIGO is offline   Reply With Quote
The Following User Says Thank You to ELF_VIGO For This Useful Post:
hassanogaibi (06-28-2016)

queries , search

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search through multiple text boxes to retrieve records hardhitter06 Forms 4 11-03-2009 10:40 AM
need help with search textbox anonymous user Forms 0 11-02-2006 04:49 PM
Problems mixing numeric fields and text fields in Query by Form search method Sharky II Forms 4 01-17-2004 03:50 PM
Form - Free Text Search kevsim Forms 2 09-23-2002 01:27 PM
Union Queries Converting Fields to Text mstorer Queries 2 08-29-2002 12:16 PM

All times are GMT -8. The time now is 07:08 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World