Get List of Queries in Project - MS Access

Question

I've got a very cookie-cutter database (very similar, very repetitive queries) which go into one modular report (ie they all return the same things with different criteria).

There will be ~100 of these queries so I'm using a combo box to display the queries, which get sent to the report (via OpenArgs).

I need to generate a list of queries (just the names) that I have in my project. I'd like to have the control source of the combo box be this list of queries.

It doesn't matter if I have to do a string concatenated Value List source or a Query/Table source type, the only thing that matters is that the bound column contains the "qryName"

What I have so far:

For Each qry In CurrentDb.QueryDefs
    list = list & ";" & """" & qry.Name & """" 
    'String in the form "qryName";"qryAnotherQuery";"qryNextQuery"
Next

but apparently there's a ~2000 character limit on Value Lists, so if I have a lot of queries I can't use a value list? Note also: qry.Name will return something like "~sq_cTableName" as well, not just my queries.. which is a problem. I'd like just queries.

Any ideas? I'm open to other ways of showing this information without a combo box as well, as long as I can send the query name to the OpenArgs of my report.

Answer 1

If you have read permission on MSysObjects, you can use this query as the row source for your combo box.

SELECT m.Name
FROM MSysObjects AS m
WHERE m.Type=5 AND m.Name Not ALike "~%"  
ORDER BY m.Name;

The criterion, m.Name Not ALike "~%", excludes temporary and hidden queries from the result set.

If you do not have read permission on MSysObjects, you will have to create a callback function which you then use as the row source for the combo box. If you have to go that route, see this example from Allen Browne which lists the report objects and change it to list queries instead: List Box of Available Reports.

Answer 2

A listbox allows you to use Me.lstBox.AddItem qryName as you loop through your queries.
You can then use a combination of ItemsSelected and ItemData to find the name of the query

For Each varItm In lstBox.ItemsSelected
    Debug.Print lstBox.ItemData(varItm)
Next varItm

or if you don't allow a multi select,

lstBox.ItemData(lstbox.ItemsSelected(0))

will give you the single item selected