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.

On Tue, 26 Jan 2010 17:49:01 -0800, GLT
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.

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
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.
.