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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2010, 01:50 AM   #1
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,491
Thanks: 44
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.

Enjoy!
Code:
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

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
ELF_VIGO
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)
Reply

Tags
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 09:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
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