Count records when RecordSource is a Stored Procedure

I have a stored procedure named mySP that looks basically like this:
Select Field1, Field2
From tblMyTable
Where Field 3 = 'xyz'

What I do is to populate an Access form:
DoCmd.Openform "frmMyFormName"
Forms!myFormName.RecordSource = "mySP"

What I want to do in VBA is to open frmContinuous(a datasheet form) if
mySP returns more than one record or open frmDetail if mySP returns
only one record.

I'm stumped as to how to accomplish this, without running mySP twice:
once to count it and once to use it as a recordsource.
Thanks,
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404260611.33a36012@.posting.google.c om...
> I have a stored procedure named mySP that looks basically like this:
> Select Field1, Field2
> From tblMyTable
> Where Field 3 = 'xyz'
> What I do is to populate an Access form:
> DoCmd.Openform "frmMyFormName"
> Forms!myFormName.RecordSource = "mySP"
> What I want to do in VBA is to open frmContinuous(a datasheet form) if
> mySP returns more than one record or open frmDetail if mySP returns
> only one record.
> I'm stumped as to how to accomplish this, without running mySP twice:
> once to count it and once to use it as a recordsource.
> Thanks,
> lq

I don't know much about VBA, but if you return the results of the procedure
in an ADO RecordSet object, then you should be able to count the rows on the
client side, and format the data accordingly. You might get a better answer
in an Access newsgroup, though.

Simon

Count of Counts in SQL Server

Hi folks,

I have the following query to run:

select count(
select count(*)
from student
group by firstName
having count(*) > 1)

Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

Thanks so much!

-Parulthat doesn't make any sense

a count of a count will always be 1|||why is that so? how do we get a count of all those rows that appear in the inner query?|||okay, let's break this problem down into steps

could you please run the inner query all by itself and show me what you get|||Since the inner select query has a group by field, the number of rows it returned may be more than one also.|||it shows you different counts by student first name; the result looks like:
count
--
5
24
2
23
2|||ah yes, okay, then i think what you want is this --select count(*)
from (
select count(*)
from student
group by firstName
having count(*) > 1
) as counts|||Exactly, but this is giving me the following error:

No column was specified for column 1 of 'counts'.|||I got it! The inner select column needs an alias.
select count(*)
from (
select count(*) as 'cnt'
from student
group by firstName
having count(*) > 1
) as counts

Thanks so much for your help!

Count Invisible ReportItems in Page Footer?

I have a textbox in my page footer with an expression like this: Sum(iif(
(ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
many times "Complete" comes up in a page. I also have a parameter which can
filter out any "Complete" values using the visibility of the detail row. My
problem is that I still want a count of the Complete values, even if they are
filtered out on the page. When I run the report with no filters, it shows me
exactly how many Completes there are per page. However, it will not count
the Completes when they are filtered out by page. How can I get the page
footer to count them even when they are hidden?Sorry, this is not supported in RS 2000. The good news is that this will
change in RS 2005 and conditionally (expression-based) hidden items won't be
taken into account in page header/footer calculations.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:D32037E2-4E92-4BB0-B622-09794DEAF21F@.microsoft.com...
>I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which
> can
> filter out any "Complete" values using the visibility of the detail row.
> My
> problem is that I still want a count of the Complete values, even if they
> are
> filtered out on the page. When I run the report with no filters, it shows
> me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?|||Though in simple terms its not possible. But there is work around-
Make custom dll which has a method which takes filter expression as input
and connects to same datasource using ado.net and fetches the same records.
Then calculate no of "complete"s in table and return the value to report.
Please see using custom assemblies in Reporting Services. Its very powerful!
Suneet Moha
-----
"dachrist" wrote:
> I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which can
> filter out any "Complete" values using the visibility of the detail row. My
> problem is that I still want a count of the Complete values, even if they are
> filtered out on the page. When I run the report with no filters, it shows me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?