Finding which queries use specific control from the main form (4 Viewers)

[ryetee]

I am looking at an access database that uses to and from controls on the main form to control what is displayed in other forms and reports etc. I there an easy way to see which queries uses these controls as their criteria.
So I've created a query using the following criteria
>=[forms]![mainform![Fr_date] And <=([forms]![mainform]![To_date]+1)
I want to find any other query that has used Fr_date and To_date
Thanks.

 

[isladogs]

The Object Dependencies feature under Database Tools will allow you to see which queries depend on your MainForm.
You can then view the SQL for each of those queries.

Or there are various database analyzer tools available (including my own) that can do that for you

 

[ryetee]

thanks I'll take a look

 

[isladogs]

You could also query the MSysQueries system table once you understand its structure.

 

[MarkK]

In support of Colin's post #4, if you run this query...

SELECT
    MSysObjects.Name,
    MSysQueries.*
FROM
    MSysObjects
    INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId;

...you can then run a Text Filter on the MSysQueries.Expression field, and the MSysObjects.Name column will identify the query it belongs to.

 

[isladogs]

Thanks @MarkK
I was on my phone for my previous answer
Now I'm back at my computer, I can provide a bit more detail

All you need from the MSysQueries table is the Expression field for Attributes 8 & 10 (WHERE & HAVING clauses respectively)
So I suggest the following SQL

SELECT
    O.Name,
    Q.Expression
FROM
    MSysObjects AS O
    INNER JOIN MSysQueries AS Q ON O.Id = Q.ObjectId
WHERE
    (
        ((Q.Attribute) IN (8, 10))
        AND ((Q.Expression) LIKE "*Forms!MainForm*")
    );

I also have a lengthy 2-part article explaining the structure of the MSysQueries system table in detail:

How Access Stores Queries 1

This article explains how Access uses the MSysQueries system table in conjunction with other system tables to display the query structure in the query design window.

www.isladogs.co.uk

 

[Edgar_]

Try this function

Function FindString(searchTerm As String) As String
    Dim folderPath As String, line As String, tempFile As String
    Dim f As AccessObject, r As AccessObject
    Dim output As String, counter As Long, fileNum As Integer
    Dim found As Boolean
    
    folderPath = CurrentProject.Path
    output = "Results found:"
    
    For Each f In CurrentProject.AllForms
        counter = counter + 1
        tempFile = folderPath & "\" & f.FullName & ".tmp"
        found = False
        
        On Error GoTo CleanupForm
        SaveAsText acForm, f.FullName, tempFile
        fileNum = FreeFile()
        Open tempFile For Input As #fileNum
        
        Do While Not EOF(fileNum) And Not found
            Line Input #fileNum, line
            If InStr(1, line, searchTerm, vbTextCompare) > 0 Then
                output = output & vbCrLf & f.Name
                found = True
            End If
        Loop
        
CleanupForm:
        If fileNum > 0 Then Close #fileNum: fileNum = 0
        If Dir(tempFile) <> "" Then Kill tempFile
        
        ' DoEvents @ every 5th object
        If counter Mod 5 = 0 Then DoEvents
    Next f
    
    For Each r In CurrentProject.AllReports
        counter = counter + 1
        tempFile = folderPath & "\" & r.FullName & ".tmp"
        found = False
        
        On Error GoTo CleanupReport
        SaveAsText acReport, r.FullName, tempFile
        fileNum = FreeFile()
        Open tempFile For Input As #fileNum
        
        Do While Not EOF(fileNum) And Not found
        Line Input #fileNum, line
            If InStr(1, line, searchTerm, vbTextCompare) > 0 Then
                output = output & vbCrLf & r.Name
                found = True
            End If
        Loop
        
CleanupReport:
        If fileNum > 0 Then Close #fileNum: fileNum = 0
        If Dir(tempFile) <> "" Then Kill tempFile
        
        ' DoEvents every 5th object
        If counter Mod 5 = 0 Then DoEvents
    Next r
    
    MsgBox output & vbCrLf & "(" & counter & " objects read)" ' Optional
    FindString = output
End Function

That you can try like this:

Sub testing()
    Debug.Print FindString("Fr_date")
End Sub