Finding which queries use specific control from the main form

[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

 

[Ken Sheridan]

So I've created a query using the following criteria
>=[forms]![mainform![Fr_date] And <=([forms]![mainform]![To_date]+1)

Shouldn't that be:

>=[forms]![mainform![Fr_date] And <([forms]![mainform]![To_date]+1)

i.e. on or later than the start date and on or before the end date?

 

[ryetee]

Shouldn't that be:

>=[forms]![mainform![Fr_date] And <([forms]![mainform]![To_date]+1)

i.e. on or later than the start date and on or before the end date?

you may well be right. I actually didn't have the + 1 and it wasn't working if the date was equal to the to_date. After some investigation i noticed the date had the time in it as well so I had to add the + 1 in

 

[ryetee]

for everyone else that's suggested things I will take a look at your advice but at the minute I have to finish the current project before I start investigating whether or not the other queries are using the to and from dates correctly

 

[Ken Sheridan]

you may well be right. I actually didn't have the + 1 and it wasn't working if the date was equal to the to_date. After some investigation i noticed the date had the time in it as well so I had to add the + 1 in

As it stands the query would return not only rows on the final day of the range, but also any rows on the next day where the date/time column's time of day element zero.

 

[sonic8]

Try this function
Code:

[...]
SaveAsText acForm, f.FullName, tempFile
[...]

Good idea in theory.
In practice it is insufficiently reliable. Long property values, particularly SQL statements, will be broken up into multiple lines. The line breaks may happen in mid-word and thus defeat your search approach.

 

[MajP]

Using @isladogs code you can find more than in the code. Especially row sources of controls. The code would need to loop the controls too, which is not currently included.

The query provides a lot of info
Determine if it is a form, report, or control that uses it in a query.
Knowing where the control is.
Determine if it is used as a link between a main form and subform. Knowing both the main form and subform names

 

[Pat Hartman]

there an easy way to see which queries uses these controls as their criteria.

Use the MSysQueries table. Search the Expression column for "Form"

Then use the ObjectID to find the name in MSysObjects - OR create a query that joins queries to objects and search the query.

 

[isladogs]

Use the MSysQueries table. Search the Expression column for "Form"
View attachment 121110
Then use the ObjectID to find the name in MSysObjects - OR create a query that joins queries to objects and search the query.

Already explained in posts 4/5/6

 

[Edgar_]

The line breaks may happen in mid-word and thus defeat your search approach.

Good catch, I didn't think about that scenario.

The SaveAsText output does not split the lines on its own, though, according to a few tests I just did. So, I believe the scenario you're talking about would be entirely on the user.

I mean, I would not write like this if I wanted to read it myself later:

sqlStatement = _
"SELECT c.customer_id, c.first_name & " " & c.last_name AS full_name, c.email, o" & _
".order_id, o.order_date, SUM(oi.quantity * oi.unit_price) AS order_total, (SELE" & _
"CT COUNT(*) FROM support_tickets AS st WHERE st.customer_id = c.customer_id AND" & _
" st.status = 'Open') AS open_tickets, IIf(SUM(oi.quantity * oi.unit_price) > 10" & _
"00, 'High Value', IIf(SUM(oi.quantity * oi.unit_price) >= 500, 'Medium Value', " & _
"'Low Value')) AS customer_value_tier, p.payment_method, p.payment_date FROM (cu" & _
"stomers AS c INNER JOIN orders AS o ON c.customer_id = o.customer_id) INNER JOI" & _
"N order_items AS oi ON o.order_id = oi.order_id LEFT JOIN payments AS p ON o.or" & _
"der_id = p.order_id WHERE o.order_status = 'Completed' AND o.order_date >= #1/1" & _
"/2024# GROUP BY c.customer_id, c.first_name, c.last_name, c.email, o.order_id, " & _
"o.order_date, p.payment_method, p.payment_date HAVING SUM(oi.quantity * oi.unit" & _
"_price) > 100 ORDER BY SUM(oi.quantity * oi.unit_price) DESC, o.order_date DESC" & _
";
"

And if that's how someone does code, they deserve not being able to find the thing. You are correct, though. I know I should not be judging, but I'm just a man with opinions.