Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Question Unanswered: Query list in combo box (ms access 2007)

    Hi,
    I recently found out (thanks to Christyxo) how to display all of the queries in the db in a combo box on the form.
    There are so many queries that are not needed for selection that I would like to know if there is a way of manually entering the desired query names or possibley excluding the queries I do not wish to appear.
    Thanks in advance,
    Gav

    Christyxo's solution for my original thread was

    Put this SQL behind the Row Source of your Drop Down List;


    Code:
    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3))
    ORDER BY MSysObjects.Name;Object Type refers to the following

    -32764 = Reports
    -32768 = Forms
    5 = Queries
    6 = Tables

    You would then have this code behind your button


    Code:
    If IsNull(Me.List1.Value) = True Then
    MsgBox "Please Select a Query from the table!"
    Else
    DoCmd.OpenQuery Me.List1.Value, , acReadOnly
    End If

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The only way I can see to do that would be to build a table to hold the names of the queries that you want to see, and then use the table as the row source for the combo box.

    If the queries that you want to see in the list all have something in common in their name, however, you can modify Christyxo's code to refer to this.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2011
    Posts
    8

    Re:

    Thanks for the quick reply weejas,
    If I could amend some of the query names so that they have something in common, how would I alter the code given to me by christyxo?
    Thanks again,
    Gav

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Well, suppose that you append "_dsp" to the names of the queries that you want to pick (please note that if any of these are used in reports, forms or other queries, you'll need to amend them, too, to reflect the new name). Then the SQL statement would become:
    Code:
    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3) AND (MSysObjects.Name LIKE "*_dsp"))
    ORDER BY MSysObjects.Name;
    HTH!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jun 2011
    Posts
    8
    Perfect. Many thanks.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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