Finding which queries use specific control from the main form (1 Viewer)

[MarkK]

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

Control names and anything along the path that references them are ...
• subject to change without notice, and
• not subject to compiler scrutiny.
You might as well put a sharp stick somewhere well travelled, somewhere dark, and right at eye level. Or sprinkle glass on the bathroom floor just before bed.

A control is your UI. A query is your database. If you have zero distance between them and anything goes wrong, you are immediately out of runway.

 

[Gasman]

I must admit I never liked hardcoding form references in queries.
I would prefer to use TempVars() and populate them with whatever was needed.

 

[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.

 

[Pat Hartman]

• subject to change without notice, and

The developer knows when they change which in my world would be exceptionally rare. Who just randomly changes the names of controls or forms?
The developer also knows which controls are used in queries. For any given form, it will be the RecordSource and any cascading combos. Same for reports. It's not like the user can change them.
If someone new takes over the app, he is very unlikely to just willy-nilly start changing control or form names. That would be downright dumb even for a novice.
I have used this technique for as long as I have been developing with Access (30 + years) and do not find it to be the least bit 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

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

The table that Colin posted explains the coding used in the MSysQueries table so you know what each field/row represents.