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

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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 J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
Thank you Douglas,
Can I use this to allow users to run the queries in the list from a
drop-down menu?
regards
Sverre

"Douglas J. Steele" wrote:

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"sverre" wrote in message
...
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