How do I run a query that generates a count then display the resu.

[Guest]

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?

 

[John Vinson]

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?

It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "<criteria>")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John but actually, I use the query to limit my record-set. The query
uses two tables and the query filter criteria is based on the related table.
So how should I proceed? Here's the query's SQL:

SELECT tblMain.fldDept, tblComputers.fldCPU, Count(tblComputers.fldCPU) AS
CountOffldCPU
FROM tblComputers INNER JOIN tblMain ON tblComputers.fldCompTag =
tblMain.fldCompTag
GROUP BY tblMain.fldDept, tblComputers.fldCPU
HAVING (((tblMain.fldDept)="CS - Storage") AND
((tblComputers.fldCPU)="C400"));

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?

It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "<criteria>")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John but actually, I use the query to limit my record-set. The query
uses two tables and the query filter criteria is based on the related table.
So how should I proceed? Here's the query's SQL:

SELECT tblMain.fldDept, tblComputers.fldCPU, Count(tblComputers.fldCPU) AS
CountOffldCPU
FROM tblComputers INNER JOIN tblMain ON tblComputers.fldCompTag =
tblMain.fldCompTag
GROUP BY tblMain.fldDept, tblComputers.fldCPU
HAVING (((tblMain.fldDept)="CS - Storage") AND
((tblComputers.fldCPU)="C400"));

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?

It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "<criteria>")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John but I used the query to create my record-set and the query has
two tables and a field in the related table is used in the query criteria. I
have tried pasting the SQL into this message but I keep getting and error
message when I try to send it.

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?

It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "<criteria>")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Trying message with pasted SQL but not asking for notification to see if the
message will through without the message nofification.
Thanks John but actually, I use the query to limit my record-set. The query
uses two tables and the query filter criteria is based on the related table.
So how should I proceed? Here's the query's SQL:

SELECT tblMain.fldDept, tblComputers.fldCPU, Count(tblComputers.fldCPU) AS
CountOffldCPU
FROM tblComputers INNER JOIN tblMain ON tblComputers.fldCompTag =
tblMain.fldCompTag
GROUP BY tblMain.fldDept, tblComputers.fldCPU
HAVING (((tblMain.fldDept)="CS - Storage") AND
((tblComputers.fldCPU)="C400"));

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?

It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "<criteria>")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Albert D. Kallal]

If this already a query saved in the query builder?

You can use:

Dim rstRecs As DAO.Recordset

Set rstRecs = CurrentDb.QueryDefs("YourQueryName").OpenRecordset

MsgBox rstRecs!CountOffldCPU

rstRecs.Close
set rstRecs = nothing