SOLVED: Query Button

[banavas]

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.

[Cosmos75]

The VBA command to open a query is
DoCmd.OpenQuery qryName
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
DoCmd.OpenQuery me.cboChooseQuery.Column(1)
.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 (http://www.access-programmers.co.uk/forums/showthread.php?t=5387) 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!
:)

[Cosmos75]

Access 97 Sample Database

[Cosmos75]

Access 2000 Sample

[Zack Barresse]

So banavas, did Cosmos get you a working solution here for you? Let us know if you can. Thanks!

[SJ McAbney]

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.

[banavas]

Yes, Thank you! Please consider the thread closed.

Yours,
G.