List available queries in combo box? (Access 2007)

Topic

New Reply

I have a client who likes to create their own queries. They use these queries as a basis for a variety of mailings they send out to their clients. I have created a routine that will take the email address from a query, combine it with a selected PDF file and perform a blitz emailing by passing the information to Outlook. Right now, it works for one query. They would like to create a number of queries, on-the-fly, and have the ability to select the query from a combo box. Is there some easier solution or is there any way of listing all queries in a combo box and allowing them to select one? I’m thinking there must be a way of populating a table with the query names and then referencing this table in the combo box. How would I do that?

Reply | Quote

Replies

The following works in all previous versions of Access; I hope it works in Access 2007 too, but I can’t test that.

– Set the Row Source Type property of the combo box to Table/Query.
– Set the Row Source property to the following SQL string:

SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Flags]=0 ORDER BY [Name]

Reply | Quote

It’s almost perfect, Hans (I’ve learned to expect nothing less from you!)

I found a query in the list that started with a tilde (~). I presume this is some kind of system file? I excluded it by modifying your statement to the following:

SELECT [Name] FROM MSysObjects WHERE [Type]=5 And [Flags]=0 AND (Left$([Name],1) “~”) ORDER BY [Name];

Otherwise, this worked perfectly in Access 2007.
Thanks!

Is there any way to exclude hidden queries from this list?

Reply | Quote

Apparently Access 2007 doesn’t set the Flags property any more. Here is another method:

Set the Row Source Type propery of the combo box to Value List.
Clear the Row Source property.
Create a function in a module:

Function ListQueries() As String
Dim strList As String
Dim obj As AccessObject
For Each obj In CurrentData.AllQueries
If Not GetHiddenAttribute(acQuery, obj.Name) Then
strList = strList & “;” & obj.Name
End If
Next obj
If Not strList = “” Then
strList = Mid(strList, 2)
End If
ListQueries = strList
End Function

Set the row source of the combo box in the On Load event of your form:

Private Sub Form_Load()
Me.cboQueries.RowSource = ListQueries
End Sub

where cboQueries is the name of the combo box.

Reply | Quote

Hans, I received an error message when trying to implement your latest solution (“The recordsource specified on this form or report does not exist”), even though your list contained valid query names. I also found that the new list still contained ALL the queries, so I did some digging. Interestingly, all the queries that were set to hidden (and do not show if the options are set to hide them) do not actually have the “hidden” flag set anymore. I checked every hidden query and found the same problem. So I “re-hid” every hidden query, and your original solution works perfectly. I’m not sure if this is going to happen regularly. I’ve found a number of interesting new “features” in Access 2007 that I don’t like, but this customer insists on using the new product.

Thanks for your help. I’m sticking with the original solution of assigning the recordsource directly from within the properties of the combo box. I’ll have to monitor how/when/why Access now seems to remove this flag from the properties.

Reply | Quote

I tried this with 3 different dbs using 2007, and it worked OK. But the databases are in 2000 format.

I have also just tried with one little accdb file, and the query only returned 1 query out of 3. The other two queries had a flag of 262144

Reply | Quote

It looks like 262144 (=2^18) is a new flag value in Access 2007. A Google search doesn’t turn up anything relevant, so I have no idea what it means.

Reply | Quote