Retrieving Combobox RowSource

[Linton.McCracken]

Hi. I am trying to identify the RowSource for every combobox
throughout my database, whether it is a named query or a SQL
statement. I know that I can open each form with a combobox and get
the combobox's RowSource by assigning a string variable =
Me.<controlname>.RowSource and get the correct query or SQL statement
assigned to the variable. But my project is being designed in an
independent module and none of my efforts have succeeded so far.

I tried programmatically opening the form and then its module and
using "Forms!<formname>!<controlname>.RowSource" but that just sets
the variable to the string above but with the object names replaced,
not the evaluated value of the string.

Any help would be appreciated. Thank you.

 

[Stuart McCall]

Hi. I am trying to identify the RowSource for every combobox
throughout my database, whether it is a named query or a SQL
statement. I know that I can open each form with a combobox and get
the combobox's RowSource by assigning a string variable =
Me.<controlname>.RowSource and get the correct query or SQL statement
assigned to the variable. But my project is being designed in an
independent module and none of my efforts have succeeded so far.

I tried programmatically opening the form and then its module and
using "Forms!<formname>!<controlname>.RowSource" but that just sets
the variable to the string above but with the object names replaced,
not the evaluated value of the string.

Any help would be appreciated. Thank you.

Here's a much easier way. The names of all hidden queries generated by
controlsources containing SQL statements take the form:

~sq_cMyFormName~sq_cMyControlName

IOW they are named in two parts, the name of the form followed by the name
of the control. Both are preceded by ~sq_c.

How to list them:

Paste this procedure into a standard module:

Sub FindHiddenQueries()
Dim q As DAO.QueryDef
For Each q In CurrentDb.QueryDefs
If InStr(1, q.Name, "~") = 1 Then
Debug.Print q.Name
End If
Next
End Sub

Click inside the sub and press F5. The results will be displayed in the
immediate window.

If you don't want to keep the procedure in each database, just don't save
the module when prompted.

Hope that helps.

 

[Stuart McCall]

Addendum: for 'controlsources' read 'rowsources'.