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 and TOP

I want to do something like this:

SELECT COUNT (SELECT TOP(10) * FROM MyTable order by Date Desc) FROM MyTable where User = 'Scott'

What I want is to return the number of affected rows where the column 'User' equals 'Scott'...But is should only check in the 10 latest inserted rows....

Hope you understand what I mean...

when I want to do things like that especially if performance isnt completly critical, I just take the easier to read approach and do a sub query.

Select Count(*)
FROM ( Select Top(10) * From... ) As MyAlias

That way you know for sure things will be working out as you are thinking them.|||

Hi Tigers21,

Use the following: -

SELECT COUNT(*) FROM MyTable WHERE MyTableUniqueFieldID IN
(
SELECT TOP 10 MyTableUniqueFieldID FROM MyTable
ORDER BY [DATE] DESC
)
AND [User] = 'scott'

Substitute MyTableUniqueFieldID with the primary key field of the MyTable table.

Kind regards

Scotty

|||

Thanks for your answers!...

Both ways seems to work correct for me, but which one is best for the performance?

|||

Shados has the better solution.