Consulting

Results 1 to 7 of 7

Thread: SOLVED: Query Button

  1. #1
    VBAX Regular
    Joined
    Jun 2004
    Posts
    28
    Location

    SOLVED: Query Button

    Is there a way to run queries by pressing buttons?
    I have created a DB with many queries. What I need to do now is to create a buttoned interface to call the queries. What is the best way of doing it?

    Thanks,
    G.

  2. #2
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    The VBA command to open a query is
    [VBA]DoCmd.OpenQuery qryName[/VBA]
    You can put that in the Click event of a button.

    If you want to be able to choose which query to use, create a combobox is linked to a table (tblQueries) with three fields
    - ID (Autonumber, Primary Key)
    - QueryName (Name of your query)
    - QueryDesc (Description of your query)

    Set the combobox (cboChooseQuery) to pull all the fields from tblQueries. Now that you've done that, you can select from a query that you've entered into tblQueries. Your code behind the button to open the query will now be
    [VBA]DoCmd.OpenQuery me.cboChooseQuery.Column(1)[/VBA]
    .Column(0) is ID
    .Column(1) is QueryName
    .Column(2) is QueryDesc

    Another way to go is to set the SQL behind the combobox to use the hidden system tables to find all the queries in your current database.

    Write a query that selects from MSysObjects (a hidden system table).
    See here to see where the original source where I learned about this information about MSysObjexcts came from (not gonna take credit for it).


    SELECT DISTINCTROW MSysObjects.Type, T.Description AS ObjectType, MSysObjects.Name AS ObjectName, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Connect, MSysObjects.Database, MSysObjects.ForeignName, MSysObjects.Id
    FROM MSysObjects LEFT JOIN PatSystemObjectTypes AS T ON MSysObjects.Type = T.Type
    ORDER BY MSysObjects.DateUpdate DESC , MSysObjects.Type, MSysObjects.Name;


    Type Description
    -32768 Forms
    -32766 Macros
    -32764 Reports
    -32761 Code Modules
    1 Local Access Table
    3 System stuff
    4 Attached ODBC Tables
    5 Queries
    6 Attached Access Tables


    The MSys tables are used internally by Access and they are not documented. Microsoft does not guarentee to keep the same format or code structure for them from version to version so be careful how you use this query. Microsoft's recommended method for obtaining this information is to use the various collections."

    You can also use a listbox to accomplish the same thing.

    Are you trying to just open one query or multiple ones at the same time?

    Hope this helps!

  3. #3
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    Access 97 Sample Database

  4. #4
    Banned VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    Access 2000 Sample

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    So banavas, did Cosmos get you a working solution here for you? Let us know if you can. Thanks!

  6. #6
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Quote Originally Posted by firefytr
    So banavas, did Cosmos get you a working solution here for you? Let us know if you can. Thanks!
    I would take it as a yes. I had to message banavas about another two threads to determine if they were concluded.

    The question is such a simple one that DoCmd.OpenQuery "QueryName" is all that's needed to answer this.

  7. #7
    VBAX Regular
    Joined
    Jun 2004
    Posts
    28
    Location
    Yes, Thank you! Please consider the thread closed.

    Yours,
    G.

Posting Permissions

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