Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List available queries in combo box? (Access 2007)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List available queries in combo box? (Access 2007)

    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]

  3. #3
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List available queries in combo box? (Access 2007)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List available queries in combo box? (Access 2007)

    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.

  5. #5
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List available queries in combo box? (Access 2007)

    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.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: List available queries in combo box? (Access 2007)

    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
    Regards
    John



  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List available queries in combo box? (Access 2007)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •