Show record count from a SQL Server Temp Table used in a form recordsource

[Lauren Quantrell]

I am using a stored procedure as the recordsource on an MS-Access2000
form:
Forms!frmName.R ecordSource = "dbo.myStoredPr ocedure"

The stored procedure creates a temp table #Contacts and then inserts
matching KeyID values contacts into it. Then I use a union query in
the same stored procedure to create the actual recordset.

What I want to know is how I can return the number of records in the
temp table #Contacts back to MS Access and display it?

I can't just do a VBA recordcount because that will count all the
records returned from the union queries.

What I am doing now feels sloppy, which is to count the records in the
temp table, then display the count (@myCount) in every row of the
recordset, which I display in the header of the form.

Any help is appreciated,
LQ

Example below:

Alter Procedure "myStoredProced ure"
AS
set nocount on

DECLARE @myCount as integer

CREATE TABLE #Contacts (id int identity, ContactID int)

INSERT INTO #Contacts(Conta ctID)
SELECT
Contacts.ID
FROM
Contacts
WHERE
Contacts.LastNa me = 'Jones')
SELECT @myCount = count(*) FROM #Contacts

SELECT
#Contacts.Conta ctID,
NULL as DATA,
@myCount AS CNT
FROM
#Contacts

UNION SELECT
#Contacts.Conta ctID,
Addresses.Addre ssInfo AS DATA,
@myCount AS CNT

FROM
#Contacts LEFT OUTER JOIN
Addresses ON
#Contacts.Conta ctID = Addresses.Conta ctID

[John Winterbottom]

"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...

I am using a stored procedure as the recordsource on an MS-Access2000
form:
Forms!frmName.R ecordSource = "dbo.myStoredPr ocedure"

The stored procedure creates a temp table #Contacts and then inserts
matching KeyID values contacts into it. Then I use a union query in
the same stored procedure to create the actual recordset.

What I want to know is how I can return the number of records in the
temp table #Contacts back to MS Access and display it?

Return the value as an output parameter. Instead of binding the form
directly to the procedure, (I guess this is an ADP?), create the recordset
in code with ADO - then you can read the returned parameter and bind the
recordset to the form.

[Alan Webb]

Laren,
=Count(*) in a text control on the form will count the rows in the form.
Doesn't matter what the source of the data is. Also, you can add an aliased
value ("CONTACTS" AS SELECT_SOURCE . . .) to your union query that
identifies which select statement contributed the result. You can then use
DCount to count only those from the select statement that has your contacts.

"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...

What I want to know is how I can return the number of records in the
temp table #Contacts back to MS Access and display it?