Running a query from a selection

[Bob H]

I have this code in a form's load event and it lists or loads the
queries in a database into a combo box.

What I would like to do from this is when a query is selected from the
list, is for that said query to run.
Is there any way I can get this to happen?

Code:
Private Sub Form_Load()
Dim TblNames As String
TblNames = ""

Dim QM As String
QM = Chr(34)

Dim qry As AccessObject
For Each qry In CurrentData.AllQueries
TblNames = TblNames & QM & "Query: " & qry.Name & QM & ";"
Next qry

Me!tblqryCombo.RowSourceType = "Value List"
Me!tblqryCombo.RowSource = TblNames

Me!tblqryCombo.Value = Me!tblqryCombo.ItemData(0)

Me!tblqryCombo.LimitToList = True
End Sub


Thanks

 

[Bob Quintal]

I have this code in a form's load event and it lists or loads the
queries in a database into a combo box.

What I would like to do from this is when a query is selected from
the list, is for that said query to run.
Is there any way I can get this to happen?

Code:
Private Sub Form_Load()
Dim TblNames As String
TblNames = ""

Dim QM As String
QM = Chr(34)

Dim qry As AccessObject
For Each qry In CurrentData.AllQueries
TblNames = TblNames & QM & "Query: " & qry.Name & QM &
";"
Next qry

Me!tblqryCombo.RowSourceType = "Value List"
Me!tblqryCombo.RowSource = TblNames

Me!tblqryCombo.Value = Me!tblqryCombo.ItemData(0)

Me!tblqryCombo.LimitToList = True
End Sub


Thanks

Code to do that is going to be much more complex than that above.

If your Combobox is also going to contain "Tables: " you are going to
have some fun.

You need to determine whether the query is an action type, e.g. an
append, update, delete or make-table query as opposed to a select
query, e.g. select union, crosstab or totals type.

You can get that data from the CurrentDb.QueryDefs collection, but
not, AFAIK, from the CurrentData.AllQueries collection.

But the CurrentDb.QueryDefs also suppplies a name for a bunch of
queries that start with ~sq_, you'll need to test before adding to
the TblNames.

You then would need to put the query type into a hidden second
combobox column.

In the combobox's after_update you would need to determine the query
type and then either issue a DoCmd.OpenQuery for select queries and
DoCmd.RunSQL for action queries.

 

[Jeff Boyce]

You've described "how" you want to approach solving a business need.

If you'll provide a specific description of that underlying business need,
folks here may be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob H]

The reason I would like to be able to run a selected query from the
combo box, is so that I can have this on a form, and so hide the queries
etc by not displaying the navigation pane.

The queries are all select queries, and I would like the user to be able
to just make a selection of a query from the combo box , then run it,
and make any required adjustments.

Thanks

 

[Bob H]

Code to do that is going to be much more complex than that above.

If your Combobox is also going to contain "Tables: " you are going to
have some fun.

You need to determine whether the query is an action type, e.g. an
append, update, delete or make-table query as opposed to a select
query, e.g. select union, crosstab or totals type.

You can get that data from the CurrentDb.QueryDefs collection, but
not, AFAIK, from the CurrentData.AllQueries collection.

But the CurrentDb.QueryDefs also suppplies a name for a bunch of
queries that start with ~sq_, you'll need to test before adding to
the TblNames.

You then would need to put the query type into a hidden second
combobox column.

In the combobox's after_update you would need to determine the query
type and then either issue a DoCmd.OpenQuery for select queries and
DoCmd.RunSQL for action queries.

All the queries are select queries, and I won't be using or selecting
table s from the list. This is an afterthought.

What I was thinking was if a user selected a query from the combo box
list, then some sort of action would be required to get the said query
to run, but I'm not sure what or how at the moment.

But, as you say, insert the Docmd.Openquery in the AfterUpdate.
I have just tried that but as it gives a runtime error I guess I need
something else there as well.

Thanks

 

[Bob H]

All the queries are select queries, and I won't be using or selecting
table s from the list. This is an afterthought.

What I was thinking was if a user selected a query from the combo box
list, then some sort of action would be required to get the said query
to run, but I'm not sure what or how at the moment.

But, as you say, insert the Docmd.Openquery in the AfterUpdate.
I have just tried that but as it gives a runtime error I guess I need
something else there as well.

Thanks

Sorry, it is a compile error not a runtime error.

 

[Bob Quintal]

Sorry, it is a compile error not a runtime error.

Your code compiled for me.
Or is it the DoCmd.OpenQuery that will not compile?

Also, you will need to extract the query's name from the
"Query: qry001" type text you are putting into the textbox.

Something like DoCmd.OpenQuery mid(TblQueryCombo,5)

 

[Jeff Boyce]

Bob

Thanks for the clarification.

I set up something similar to help users select a report they wish to run.
I added a table that holds the report name (Access) and a user-friendly
report name, plus categorization and other 'set up' values.

I use a form with a combobox to have the user select a report by
user-friendly name, then have a command button for Access to open the
selected report (by its Access report name).

Would a similar approach work for you?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob H]

Bob

Thanks for the clarification.

I set up something similar to help users select a report they wish to run.
I added a table that holds the report name (Access) and a user-friendly
report name, plus categorization and other 'set up' values.

I use a form with a combobox to have the user select a report by
user-friendly name, then have a command button for Access to open the
selected report (by its Access report name).

Would a similar approach work for you?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Hi Jeff,
well yes I think that approach would work for me as I was kind of
thinking that I may need a cmd button also on the form to run the
selected query.
I'm not sure how I could do this though, as the cmd button would have to
know which query has been selected.

Thanks

 

[Bob H]

Your code compiled for me.
Or is it the DoCmd.OpenQuery that will not compile?

Also, you will need to extract the query's name from the
"Query: qry001" type text you are putting into the textbox.

Something like DoCmd.OpenQuery mid(TblQueryCombo,5)

Yes it is the DoCmd.OpenQuery which will not compile, as the error
message is 'Argument not optional'

I'm not sure what you say here as the query name goes into the combo box

'"Query: qry001" type text you are putting into the textbox.'

The name in the combo box is 'Query: qryLSC 01' for example.

Thanks

 

[David W. Fenton]

You've described "how" you want to approach solving a business
need.

If you'll provide a specific description of that underlying
business need, folks here may be able to offer more specific
suggestions.

I've not read the actual question, but Jeff's comment is one that I
very commonly make in regard to questions -- too often, someone
asking a question has settled on a method for solving a problem, and
can't get that method to work, so they ask about it, but don't
explain what the underlying problem is so that it's really
impossible to offer a good answer to the question.

The hard part is telling the difference between what is a problem
and what is a solution. A failed solution looks like a problem, but
really, it's not, and it's most helpful to the reader of the
question for the poster to back up and describe the goal of the
failed solution, i.e., what problem it's intended to solve.

 

[Jeff Boyce]

Bob

How I do it is to add code behind the button click that uses something like
(untested):

DoCmd.OpenReport, .... blah, blah, blah, Me!MyCombobox.Column(x)

That way, when the button is clicked, the code looks to the combobox to get
the correct reportname. By the way, the .Column(x) is zero-based, and is
used to tell Access to look at the x-1st column in the query feeding the
combobox. Thus, if the Access reportname is in the 2nd physical column,
this portion would read ".Column(1)"

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob Quintal]

Yes it is the DoCmd.OpenQuery which will not compile, as the error
message is 'Argument not optional'

I'm not sure what you say here as the query name goes into the
combo box

'"Query: qry001" type text you are putting into the textbox.'

The name in the combo box is 'Query: qryLSC 01' for example.

Thanks

Argument Not Optional indicates that DoCmd.OpenQuery cannot figure
out what the real name of the query is.

First, you need to pass the name of the query, and only the name of
the query, not "Query: some query name", and if you have spaces in
the name, you have given yourself another headache.

So taking your example, you need to skip over the first 7 characters
of your combobox, and since you have spaces, surroung the name with
square brackets.

if your Combobox is TblQryCombo then

Docmd.OpenQuery "[" & trim(mid(TblQryCombo,7) &"]"

should open your query.

 

[Bob H]

Docmd.OpenQuery "["& trim(mid(TblQryCombo,7)&"]"

I'm afraid this doesn't work, as I am getting a compile error message

 

[Bob H]

Docmd.OpenQuery "["& trim(mid(TblQryCombo,7)&"]"

I'm afraid this doesn't work, as I am getting a compile error message

Ok, I have solved the compile error; it was a missing 'close bracket'

Docmd.OpenQuery "["& trim(mid(TblQryCombo,7))&"]"

But now when I select the query, I get a Runtime 7874 message sayin it
can't find query qryLSC01, which is what it is called.

Thanks

 

[Bob Quintal]

Docmd.OpenQuery "["& trim(mid(TblQryCombo,7)&"]"

I'm afraid this doesn't work, as I am getting a compile error
message

Ok, I have solved the compile error; it was a missing 'close
bracket'

Docmd.OpenQuery "["& trim(mid(TblQryCombo,7))&"]"

But now when I select the query, I get a Runtime 7874 message
sayin it can't find query qryLSC01, which is what it is called.

Thanks

If you type Docmd.OpenQuery "qryLSC01" into the immediate window and
press enter, what happens?

If you put a debug.print "["& trim(mid(TblQryCombo,7))&"]" into the
code just above your Docmd.Openquery statement, what do you see in the
immediate window when you run the code?

 

[Bob H]

On 16/06/2010 22:15, Bob Quintal wrote:
Docmd.OpenQuery "["& trim(mid(TblQryCombo,7)&"]"


I'm afraid this doesn't work, as I am getting a compile error
message

Ok, I have solved the compile error; it was a missing 'close
bracket'

Docmd.OpenQuery "["& trim(mid(TblQryCombo,7))&"]"

But now when I select the query, I get a Runtime 7874 message
sayin it can't find query qryLSC01, which is what it is called.

Thanks

If you type Docmd.OpenQuery "qryLSC01" into the immediate window and
press enter, what happens?

If you put a debug.print "["& trim(mid(TblQryCombo,7))&"]" into the
code just above your Docmd.Openquery statement, what do you see in the
immediate window when you run the code?

When I type Debug.Print "[" & Trim(Mid(tblqryCombo, 7)) & "]" into the
code then select a query, nothing happens, but when I type the same into
the immediate window, it returns a set of square brackets : []

Then when I type into the code window DoCmd.OpenQuery ("qryLSC01"), then
select a query from the combo box , it runs the query.
(But obviously it is the same query regardless of selection)

Thanks