Change an underlying query paramaters by button on a form

Hi,

I have a form that is bound to a query.  The form displays a list of servers 
and a count of errors that have occured on each server.

I also have two buttons on my form, one which is labelled 'All' and the 
other is labelled 'Errors Only'.

I have modified the query (via the QBE grid) to allow for both situations, 
and copied the SQL as follows:

When I click the 'All' button, I would like the following SQL to run:

SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, 
IIf([Ping]=-1,"OK","FAIL") AS PingRslt
FROM Full_Compare_Q LEFT JOIN Services ON Full_Compare_Q.RecID = 
Services.RecID
GROUP BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
Full_Compare_Q.RecID, IIf([Ping]=-1,"OK","FAIL")
ORDER BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server;

When I click the 'Errors Only' button, I would like the following SQL to run:

SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, 
IIf([Ping]=-1,"OK","FAIL") AS PingRslt
FROM Full_Compare_Q LEFT JOIN Services ON Full_Compare_Q.RecID = 
Services.RecID
GROUP BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
Full_Compare_Q.RecID, IIf([Ping]=-1,"OK","FAIL")
HAVING (((Abs(Sum([Services].[Valid])))>1)) OR 
(((IIf([Ping]=-1,"OK","FAIL")) Like "FAIL"))
ORDER BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server;

These two lots of SQL work fine, I just want to be able to change the Query 
parameters when either button is clicked.  Can anyone advise how to do this?

Any help is always greatly appreciated...

Cheers,
GLT.
0
Utf
1/27/2010 1:49:01 AM
access.forms 6864 articles. 2 followers. Follow

2 Replies
429 Views

Similar Articles

[PageSpeed] 25

On Tue, 26 Jan 2010 17:49:01 -0800, GLT
<GLT@discussions.microsoft.com> wrote:

I have my doubts about your use of the Having clause vs the Where
clause, but that aside:
Create two queries, each with their sql statement. Note that you can
create a new query, switch to sql view, and paste in your statement.
Say you named them q1 and q2.
Then in the All-button's click event write:
Me.RecordSource = "q1"
I'm sure you can figure out what to write in the other button's Click
event :-)

-Tom.
Microsoft Access MVP


>Hi,
>
>I have a form that is bound to a query.  The form displays a list of servers 
>and a count of errors that have occured on each server.
>
>I also have two buttons on my form, one which is labelled 'All' and the 
>other is labelled 'Errors Only'.
>
>I have modified the query (via the QBE grid) to allow for both situations, 
>and copied the SQL as follows:
>
>When I click the 'All' button, I would like the following SQL to run:
>
>SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
>Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, 
>IIf([Ping]=-1,"OK","FAIL") AS PingRslt
>FROM Full_Compare_Q LEFT JOIN Services ON Full_Compare_Q.RecID = 
>Services.RecID
>GROUP BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
>Full_Compare_Q.RecID, IIf([Ping]=-1,"OK","FAIL")
>ORDER BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server;
>
>When I click the 'Errors Only' button, I would like the following SQL to run:
>
>SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
>Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, 
>IIf([Ping]=-1,"OK","FAIL") AS PingRslt
>FROM Full_Compare_Q LEFT JOIN Services ON Full_Compare_Q.RecID = 
>Services.RecID
>GROUP BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
>Full_Compare_Q.RecID, IIf([Ping]=-1,"OK","FAIL")
>HAVING (((Abs(Sum([Services].[Valid])))>1)) OR 
>(((IIf([Ping]=-1,"OK","FAIL")) Like "FAIL"))
>ORDER BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server;
>
>These two lots of SQL work fine, I just want to be able to change the Query 
>parameters when either button is clicked.  Can anyone advise how to do this?
>
>Any help is always greatly appreciated...
>
>Cheers,
>GLT.
0
Tom
1/27/2010 5:39:19 AM
Hi Tom,

Thanks for your reply... thats what I wound up doing, is creating two 
seperate queries and when each button is clicked, it chagnes the recordset 
source to the corresponding query.  Works great...

Cheers,
GLT

"Tom van Stiphout" wrote:

> On Tue, 26 Jan 2010 17:49:01 -0800, GLT
> <GLT@discussions.microsoft.com> wrote:
> 
> I have my doubts about your use of the Having clause vs the Where
> clause, but that aside:
> Create two queries, each with their sql statement. Note that you can
> create a new query, switch to sql view, and paste in your statement.
> Say you named them q1 and q2.
> Then in the All-button's click event write:
> Me.RecordSource = "q1"
> I'm sure you can figure out what to write in the other button's Click
> event :-)
> 
> -Tom.
> Microsoft Access MVP
> 
> 
> >Hi,
> >
> >I have a form that is bound to a query.  The form displays a list of servers 
> >and a count of errors that have occured on each server.
> >
> >I also have two buttons on my form, one which is labelled 'All' and the 
> >other is labelled 'Errors Only'.
> >
> >I have modified the query (via the QBE grid) to allow for both situations, 
> >and copied the SQL as follows:
> >
> >When I click the 'All' button, I would like the following SQL to run:
> >
> >SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
> >Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, 
> >IIf([Ping]=-1,"OK","FAIL") AS PingRslt
> >FROM Full_Compare_Q LEFT JOIN Services ON Full_Compare_Q.RecID = 
> >Services.RecID
> >GROUP BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
> >Full_Compare_Q.RecID, IIf([Ping]=-1,"OK","FAIL")
> >ORDER BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server;
> >
> >When I click the 'Errors Only' button, I would like the following SQL to run:
> >
> >SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
> >Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, 
> >IIf([Ping]=-1,"OK","FAIL") AS PingRslt
> >FROM Full_Compare_Q LEFT JOIN Services ON Full_Compare_Q.RecID = 
> >Services.RecID
> >GROUP BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, 
> >Full_Compare_Q.RecID, IIf([Ping]=-1,"OK","FAIL")
> >HAVING (((Abs(Sum([Services].[Valid])))>1)) OR 
> >(((IIf([Ping]=-1,"OK","FAIL")) Like "FAIL"))
> >ORDER BY Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server;
> >
> >These two lots of SQL work fine, I just want to be able to change the Query 
> >parameters when either button is clicked.  Can anyone advise how to do this?
> >
> >Any help is always greatly appreciated...
> >
> >Cheers,
> >GLT.
> .
> 
0
Utf
1/27/2010 2:37:01 PM
Reply:

Similar Artilces: