3

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.

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37

2 Answers2

7

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • This is exactly what I needed; displays the exact things I want and allows me to quickly and easily add exceptions (more `Not Alike ""`) statements if I have some queries of my own that the user need not see. Works well. Thanks! – StuckAtWork Jun 04 '12 at 14:44
1

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

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • I'll give this a try, but I still don't have a way to "loop through my queries". I'm not sure what any syntax is for finding all the query names for your database. My code above finds all tables, etc, which shouldn't be shown. – StuckAtWork Jun 04 '12 at 14:03
  • 2
    quick and dirty method - you will have to look at what is returned to see what to exclude: `Select Name, Type from MSysObjects where Type =5;` - the reason to not use it as the source to a combo box is it includes some TMP queries too – SeanC Jun 04 '12 at 14:20
  • The `AddItem` approach may not help in this case because `AddItem` requires "Value List" as row source type (see Access help topic). The OP already told us the combined length of his queries names exceeds the capacity of a Value List. The only way it could work is if he can get under that limit by excluding the names of hidden queries. – HansUp Jun 04 '12 at 14:39
  • Bah - I didn't realize that the listbox had the same limitation. I stand corrected. use @HansUp solution below – SeanC Jun 04 '12 at 14:44