When a button is clicked on a Access Form, I want to run a query and
display its result. I am able to do that, but my form has 12 buttons and
I am creating 12 different query objects(containing the 12 queries) for
them. So, the left had side of my window(which displays all Access
objects) which shows 12 query objects.

Now, I have to create another form which has 6 buttons which on clicked
will run 6 different queries.

1. Is there a way in Access 2007 where instead of creating another 6
query objects I can create a single query object which can contain the 6
different queries and run query 1 when button 1 is clicked, run query 2
when button 2 is clicked and so on.

2. If there is no such way, can I alleviate the issue of having multiple
query objects by running a macro when the button is clicked which runs a
query based on which button is clicked and produces the results. How
difficult would this be to do? Currently, all I have to do is create a
query object and add some SQL to it so that it produces the desired result.

Any suggestions would be appreciated.

g wrote:

Here's an example of two command buttons running different SQL
statements using the same query.

Option Compare Database
Option Explicit
Function IsQueryOpen(strname As String) As Boolean
'see if query is already open
If SysCmd(acSysCmdGetObjectState, acQuery, strname) <> 0 Then
IsQueryOpen = True
End If
End Function
Private Sub CloseQuery()
'close TempQ query if open and delete it if it exists.
On Error Resume Next
If IsQueryOpen("TempQ") Then
DoCmd.Close acQuery, "TempQ"
End If
DoCmd.DeleteObject acQuery, "TempQ"
End Sub
Private Sub CommandCustomers_Click()
'customers command button
CloseQuery
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("TempQ", "Select * From Customers")
DoCmd.OpenQuery "TempQ"
DoCmd.SelectObject acQuery, "TempQ"
End Sub
Private Sub CommandContacts_Click()
'contacts command button
CloseQuery
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("TempQ", "Select * From Contacts")
DoCmd.OpenQuery "TempQ"
DoCmd.SelectObject acQuery, "TempQ"
End Sub

It sounds like you are saying you are using up screen space to place one
button for each query. If so, plan on getting a bigger screen! As soon as
you add more queries, you'd need more buttons, right?!

Here's an alternate approach, in the way of an analogy...

I have an application with several dozen reports. Rather than put each
report behind a button on a "reports" form, I use a combobox to list the
reports. After the user selects a report, s/he clicks the <Print Preview>
button (only one button).

Behind that button, I have code that runs the OpenReport command, using the
selected report in the combobox as the parameter in the command that calls
for the name of the report to open.

I wonder if you could do something similar?

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.

"g" <g_1@g.com> wrote

On 6/30/2010 1:27 PM, Salad wrote:
Thanks for the code snippet and your time. My design is flawed. I should
have a combobox/list box where the user selects a value and using those
values a query should be created.

My current design is user chooses a value from combobox which is used in
the where clause of the query, clicks Min button,max button or Avg
button and using that selected value(from combobox) the query is run.
The query which i am using at present if a max button is chosen

SELECT ROUND(MAX(Table.[Column1]),2) AS Column1_Average
FROM Table
WHERE (((Table.[Column2])=[Forms]![Formname]![Combo2]));

as i am just using that value(selected value from combobox) in the where
clause.

I would need something like

SELECT
ROUND([Forms]![Formname],2)
AS Column1_Average
FROM Table
WHERE (((Table.[Column2])=[Forms]![Formname]![Combo2]));

where [Forms]![Formname]![Combo3] would be Min, Max, Avg
[Forms]![Formname]![Combo4] would be Name of Column in Table


It did not work when i tried. Is that possible to do what i want as a
SQL query alone?

Or, would I need to use VBA code as you have explained above with an
example?

Can you please clarify?

Thanks for your advice.

On 6/30/2010 1:51 PM, Jeff Boyce wrote:
I get the idea, my design is flawed. I should have a combobox/list box
where the user selects a value and using those values a query should be
created.

I could, i need something like

SELECT
ROUND([Forms]![Formname],2)
AS Column1_Average FROM Table
WHERE (((Table.[Column2])=[Forms]![Formname]![Combo2]));

where [Forms]![Formname]![Combo3] would be Min, Max, Avg
and [Forms]![Formname]![Combo4] would be Name of Column in Table

Thanks for pointing out the design glitch.