Finding which queries use specific control from the main form

[isladogs]

A point worth making IMO in respect to working with control references in queries:
• Don't put control references in queries.

Agreed.
However I thought the OP wanted to analyze an old database to identify existing references to a specific form in queries in order to then modify the query SQL

 

[MarkK]

I thought the OP wanted to analyze an old database

Agreed too, but I thought the thread might lead some to think it's a good idea to do in the first place. It works, it can get you across the finish-line, but its fragile.

 

[gemma-the-husky]

You can iterate the queries (querydefs) and search for particular text.
I use variations of this. This produces a report of all queries including the findtext text

You can easily modify this to include multiple findtext values (such as your "from" and "to" requirements)

Sub search_queries_file()

Const findtext = "sometext"
Const searchfor = 255 'use only certain types of query 255 checks all

Dim log As String
Dim fnum As Long
Dim db As database
Dim qdef As QueryDef
Dim qt As String

log = CurrentProject.path
If Right(log, 1) <> "\" Then log = log & "\"

log = log & findtext & ".txt"

fnum = FreeFile

Open log For Output As #fnum

Set db = CurrentDb

For Each qdef In db.QueryDefs
  
     
    If InStr(1, qdef.SQL, findtext) > 0 Then
        qt = "~~~~~"
        Select Case qdef.Type
            Case 0: qt = "Select"
            Case 16: qt = "Crosstab"
            Case 32: qt = "Delete"
            Case 48: qt = "Update"
            Case 64: qt = "Append"
            Case 128: qt = "Union"
            Case Else
                    qt = "??? " & qdef.Type
        End Select
      
        If (qdef.Type And searchfor) = qdef.Type Then
            Print #fnum, "Type: " & qt & "  " & qdef.name
        End If
    End If
Next

Close #fnum

On Error GoTo fail
Application.FollowHyperlink log

Exit Sub

fail:
    MsgBox ("Error opening log file")

End Sub

 

[gemma-the-husky]

If you look at one of the links @isladogs posted, he included a chart that explains the Attribute and Flag codes. If you make this into a parent and child table, you can replace the code that Dave suggested with a query with a left join and use that query as the RecordSource for a report so you can clean up the display and make it more understandable.

You mean to store and display the results, rather than extract the data?
For my purposes, I am happy to just to save and display a text file. Anyway, the code was really to show how easy it was to use the querydef object.

 

[ryetee]

Thanks to everyone's input. I went down the MSysQueries and Objects route and got what I was after

 

[isladogs]

That’s great. Glad you have a solution that works for you.
The thread triggered some interesting viewpoints and suggested approaches

 

[isladogs]

@Pat Hartman
Hmmm...at the moment I've can only find that table in a Word doc.
For now, attached is a PDF of the web article which includes that table

I'll extract the data and (hopefully) get it into Access in the next few days so it can be directly queried.

 

[isladogs]

It was a Word table.
I've saved it as a separate Word doc and also exported it to Excel

For some reason neither .docx or .xlsx are allowed filetypes so I've included both in the attached zip file