Problem with returning a recordset from a VBA function

  • Thread starter Thread starter Dave
  • Start date Start date
D

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.
 
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...
 
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
 
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?
 
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
 
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)
 
Back
Top