Create a list of queries to include in a drop-down menu

  • Thread starter Thread starter sverre
  • Start date Start date
S

sverre

Hi

I would like to create a list of queries (not all ) to include this list in
a drop-down menu. But I cannot find where I start creating the list.

Could someone help me!

Best regards

Sverre
 
The following SQL will list all queries in your database:

SELECT Name
FROM MSysObjects
WHERE Type = 5
ORDER BY Name

Can you put an appropriate WHERE clause on there to limit it to only those
of interest to you?
 
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre
 
Depends what you mean by "run the queries".

If they're Action queries (INSERT INTO, UPDATE, DELETE), then you can use
code like:

Private Sub cboQueries_AfterUpdate

CurrentDb.QueryDefs(Me.cboQueries).Execute

End Sub

If they're Select queries, I don't really understand why you'd want your
users to run them, but you can use code like:

Private Sub cboQueries_AfterUpdate

DoCmd.OpenQuery Me.cboQueries

End Sub

Using the OpenQuery method, you can make the query Read-Only if you want.
 
Hi Douglas

Sorry, I have expressed myself unclear. The art of explaining is sometimes
difficult. The queries are select queries and the reason to create the drop
down list is that I want the users to have the possibility to run some of all
select queries in a menu - there are a lot of queries out of there interest
not to be included. So my thought was to allow users to run any of the
queries in the list by activating it in the list. I have added the SQL you
gave me in your first response in a combo box as a "row source", but I would
like to "connect" each query-name in the list to make Access run the query
itself and display the result when the user select the name of select query.

I am not very familiar with forms and buttons so maybe I am moving in the
wrong direction to create the above solution? With this information above -
can I use your vba code in previous answer and if yes! where in the
properties do I put it?

Hope I have explained myself more clear now!

Regards
Sverre
 
I gave you the code you need to put in the AfterUpdate event of the combo
box for which I previously gave your the RowSource. The AfterUpdate event
fires once the user has made a selection in the combo box.

I still think it's unusual to have users run Select queries.
 
Hi Douglas,
Ok, I will try to build this. I am curious about your doubts on select
queries. What would be the alternative - build a report which includes all
select queries output ?
Regards
Sverre
 
Douglas

I have created the combo box, created your select query to list all queries
and added it as row source. Then I added the vba code to the after update row
in properties. The list of queries is visible but nothing happens when I
select any of the queries in the list.

Can it be related to the fact that I run MS ACESS 2002? When I run the code
for after update within the module a screen with the name macro pops up. (I
have added the query for listing all queries to a macro - can that be causing
this error)
regards
Sverre
 
I think the error can be "cboQueries". Keyword not found says the help
function when I press F1. Do I need to add something like an add-in?
Sverre
 
In my sample code, "cboQueries" is assumed to be the name of the combo box
containing the names of the queries.

If your combo box is called something different, use your name.

As to alternatives to running queries, yes, running a report instead would
be better in my opinion.
 
Great works fine! Many thanks Douglas!

The advantage with using select queries was that users easily can copy paste
to Excel, but maybe you can do that in reports aswell?

For my curiousity - are you sql and vba-programmer by profession?
You write code that for me is very advanced and very exact. If I would like
to learn
the basics of access (except for writing select queries) - could you
recommend training courses or a book? I have a pdf-book called Access 2002
Bible.

Many thanks again,
Sverre
 
sverre said:
Great works fine! Many thanks Douglas!

The advantage with using select queries was that users easily can copy
paste
to Excel, but maybe you can do that in reports aswell?
Click to expand...

No, you can't. Reports aren't interactive. If the intent is to export to
Excel, why not do the "heavy lifting" for them, and use TransferSpreadsheet
to put the data into Excel?
For my curiousity - are you sql and vba-programmer by profession?
You write code that for me is very advanced and very exact. If I would
like
to learn
the basics of access (except for writing select queries) - could you
recommend training courses or a book? I have a pdf-book called Access 2002
Bible.
Click to expand...

To be honest, I hardly program anymore (and I'm working mostly with
mainframe computers at the moment!). However, I've long had a interest in
educating people and helping to improve their programming style. I wrote the
"Access Answers" column for more than two and a half years (see
http://www.accessmvp.com/DJSteele/SmartAccess.html for a free archive of my
columns)

For a great list of resources to help you learn Access, see what Jeff Conrad
has at http://www.accessmvp.com/JConrad/accessjunkie/resources.html
 
Back
Top