Problem with returning a recordset from a VBA function

[Dave]

I define a VBA funcion with the following signature
Public Function GetPhysicianData(Byval PhysicianId as String) as
ADOB.RecordSet

Within this function I decalre an internal adoRs object that calls the
stored procedure and then do Set GetPhysicianData = adoRS.

I then call this function from my Access form as:

Set adoRS = GetPhysicianData(strPhysicianId)

I am getting a Run time error 3704 - Operation is not allowed when the
recordset is closed.

When I examine the recordset value in the function (e.g. GetPhysicianData) I
can see there is data there. Does this have something do with using an active
connection to get the data.

 

[Dave]

NVM - I found the answer to my problem. I need to open the recordset after
returning from the function call as follows:

Set adoRS = GetPhysicianData(strPhysicianId)
adoRS.Open

duhh...

 

[Dave]

Ahh but now a new problem arises. I use the recordset as a data soruce for an
Access report by doing the following: Set Me.Recordset = adoRs.

The first record displays fine, but when I advance to the second record I
get an error message that the object is closed. Has anyone seen this before?


Dave

 

[David H]

What exactly does the function do? If you need to set the report's recordset,
why won't setting it to the SQL statement work?

 

[Dave]

Because I am calling a stored procedure that has mutiple input parameters,
some optional some not. Can that be done setting the report property
recordsource?

Dave

 

[David H]

Ah - you didn't mention that SQLServer was involved. I have *very* minimal
experience with Stored Procedures let alone calling them from Access. You may
to resort to using a temp table - probably on the SQL server side. Also, you
may want to ask the question of a SQLServer group. (www.sqlservercentral.com)