Calling a recordset from a function.

[Gerry Abbott]

Hi all.

I wish to call a recordset from a function.
Ive tried the following approach,

--------------------------------------------------------
Function PassRS() As Recordset
Dim db As Database
Dim rs As Recordset
Dim myQdf As QueryDef
Dim mySql As String


mySql = ".............m y sql............ ..."

Set db = CurrentDb
Set myQdf = db.CreateQueryD ef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecor dset()
End Function
--------------------------------------------------------

Then i call it like this,
--------------------------------------------------------
Private Sub Command1_Click( )
Call PassRS
With PassRS
.MoveLast
Debug.Print "count " & .RecordCount
End With
End Sub
----------------------------------------------------------

And its seems to work. But how do I destroy the recordset object, and the
qryDef object when i'm done with it?

Thanks in advance for any advice.


Gerry Abbott

[Larry Linson]

Re: Calling a recordset from a function.

First, you have gone to unnecessary work just to open a Recordset. There is
neither any need nor any advantage to create a Querydef. Use
db.Openrecordse t and refer directly to the SQL that you've created. Now, we
have eliminated the QueryDef as a problem.

Then if you'll refer directly to CurrentDB, you can eliminate your Database
object variable as a problem.

CurrentDB.OpenR ecordset (mySQL)

That only leaves the Recordset... you can either declare it as a Global
variable, so you can .Close it and Set it to Nothing from anywhere -- that's
a "sure thing". Or, you can just Close and Set to Nothing the Recordset
variable into which the Function delivers it, and hope that clears
everything away. As it was declared in the Function, it should go out of
scope when you exit the Function -- I've never passed a Recordset out of a
function, and, thus, never gave it any thought.

Larry Linson
Microsoft Access MVP



"Gerry Abbott" <please@ask.i e> wrote in message
news:bJQrb.5344 $bD.19611@news. indigo.ie...[color=blue]
> Hi all.
>
> I wish to call a recordset from a function.
> Ive tried the following approach,
>
> --------------------------------------------------------
> Function PassRS() As Recordset
> Dim db As Database
> Dim rs As Recordset
> Dim myQdf As QueryDef
> Dim mySql As String
>
>
> mySql = ".............m y sql............ ..."
>
> Set db = CurrentDb
> Set myQdf = db.CreateQueryD ef("")
> myQdf.SQL = mySql
> Set db = Nothing
> Set PassRS = myQdf.OpenRecor dset()
> End Function
> --------------------------------------------------------
>
> Then i call it like this,
> --------------------------------------------------------
> Private Sub Command1_Click( )
> Call PassRS
> With PassRS
> .MoveLast
> Debug.Print "count " & .RecordCount
> End With
> End Sub
> ----------------------------------------------------------
>
> And its seems to work. But how do I destroy the recordset object, and the
> qryDef object when i'm done with it?
>
> Thanks in advance for any advice.
>
>
> Gerry Abbott
>
>
>[/color]

[Fletcher Arnold]

Re: Calling a recordset from a function.

"Gerry Abbott" <please@ask.i e> wrote in message
news:bJQrb.5344 $bD.19611@news. indigo.ie...[color=blue]
> Hi all.
>
> I wish to call a recordset from a function.
> Ive tried the following approach,
>
> --------------------------------------------------------
> Function PassRS() As Recordset
> Dim db As Database
> Dim rs As Recordset
> Dim myQdf As QueryDef
> Dim mySql As String
>
>
> mySql = ".............m y sql............ ..."
>
> Set db = CurrentDb
> Set myQdf = db.CreateQueryD ef("")
> myQdf.SQL = mySql
> Set db = Nothing
> Set PassRS = myQdf.OpenRecor dset()
> End Function
> --------------------------------------------------------
>
> Then i call it like this,
> --------------------------------------------------------
> Private Sub Command1_Click( )
> Call PassRS
> With PassRS
> .MoveLast
> Debug.Print "count " & .RecordCount
> End With
> End Sub
> ----------------------------------------------------------
>
> And its seems to work. But how do I destroy the recordset object, and the
> qryDef object when i'm done with it?
>
> Thanks in advance for any advice.
>
> Gerry Abbott[/color]



Hi Gerrry
I'm not sure why you might need a general function like PassRS() , you might
retain more flexibility to open the recordset within the click sub. Anyway,
if you really did want this sort of function, you might structure it to
handle errors gracefully. What about this as possibility?


Public Function SetRS(strSQL, rst As DAO.Recordset) As Boolean

On Error GoTo Err_Handler

Set rst = CurrentDb.OpenR ecordset(strSQL , dbOpenSnapshot)

SetRS = True

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function




Private Sub Command1_Click( )

On Error GoTo Err_Handler

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM MyTable WHERE ID < 6 ORDER BY ID"

If Not SetRS(strSQL, rst) Then Exit Sub

rst.MoveLast

MsgBox rst.RecordCount

rst.Close

Set rst = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Gerry Abbott]

Re: Calling a recordset from a function.

Many thanks Larry,
I take your points re refering to the recordsed directly.
Code is much tidier. My belief was that it was necessary,
to use a queryDef object to use SQL to generate a recordset.
(wrong).

The only real benefit for the function now is to hold the SQL,
and so the recordset reference could now be taken out
of the function also.

What function does the queryDef object serve?



Gerry Abbott

----------------------------------------------------------------
Public Function AgendaRS(sqlPar ameter As Long) As DAO.Recordset
Dim mySQL As String

mySQL = " ..........."
Set AgendaRS = CurrentDb.OpenR ecordset(mySQL)
End Function
---------------------------------------------------------------




"Larry Linson" <bouncer@localh ost.not> wrote in message
news:gxRrb.1989 8$E9.4904@nwrdd c01.gnilink.net ...[color=blue]
> First, you have gone to unnecessary work just to open a Recordset. There[/color]
is[color=blue]
> neither any need nor any advantage to create a Querydef. Use
> db.Openrecordse t and refer directly to the SQL that you've created. Now,[/color]
we[color=blue]
> have eliminated the QueryDef as a problem.
>
> Then if you'll refer directly to CurrentDB, you can eliminate your[/color]
Database[color=blue]
> object variable as a problem.
>
> CurrentDB.OpenR ecordset (mySQL)
>
> That only leaves the Recordset... you can either declare it as a Global
> variable, so you can .Close it and Set it to Nothing from anywhere --[/color]
that's[color=blue]
> a "sure thing". Or, you can just Close and Set to Nothing the Recordset
> variable into which the Function delivers it, and hope that clears
> everything away. As it was declared in the Function, it should go out of
> scope when you exit the Function -- I've never passed a Recordset out of a
> function, and, thus, never gave it any thought.
>
> Larry Linson
> Microsoft Access MVP
>
>
>
> "Gerry Abbott" <please@ask.i e> wrote in message
> news:bJQrb.5344 $bD.19611@news. indigo.ie...[color=green]
> > Hi all.
> >
> > I wish to call a recordset from a function.
> > Ive tried the following approach,
> >
> > --------------------------------------------------------
> > Function PassRS() As Recordset
> > Dim db As Database
> > Dim rs As Recordset
> > Dim myQdf As QueryDef
> > Dim mySql As String
> >
> >
> > mySql = ".............m y sql............ ..."
> >
> > Set db = CurrentDb
> > Set myQdf = db.CreateQueryD ef("")
> > myQdf.SQL = mySql
> > Set db = Nothing
> > Set PassRS = myQdf.OpenRecor dset()
> > End Function
> > --------------------------------------------------------
> >
> > Then i call it like this,
> > --------------------------------------------------------
> > Private Sub Command1_Click( )
> > Call PassRS
> > With PassRS
> > .MoveLast
> > Debug.Print "count " & .RecordCount
> > End With
> > End Sub
> > ----------------------------------------------------------
> >
> > And its seems to work. But how do I destroy the recordset object, and[/color][/color]
the[color=blue][color=green]
> > qryDef object when i'm done with it?
> >
> > Thanks in advance for any advice.
> >
> >
> > Gerry Abbott
> >
> >
> >[/color]
>
>[/color]

[Gerry Abbott]

Re: Calling a recordset from a function.

Thanks Fletcher,

The main reason to take the recordset code away from my main code, was due
to a complex and cumbersome SQL. But Larry's suggestion requires only one
reference, and so the function similifies down to passing the sql. Your
suggestion for correct handling is well made.

"Fletcher Arnold" <fletch@home.co m> wrote in message
news:boorkr$sbj $1@hercules.bti nternet.com...[color=blue]
> "Gerry Abbott" <please@ask.i e> wrote in message
> news:bJQrb.5344 $bD.19611@news. indigo.ie...[color=green]
> > Hi all.
> >
> > I wish to call a recordset from a function.
> > Ive tried the following approach,
> >
> > --------------------------------------------------------
> > Function PassRS() As Recordset
> > Dim db As Database
> > Dim rs As Recordset
> > Dim myQdf As QueryDef
> > Dim mySql As String
> >
> >
> > mySql = ".............m y sql............ ..."
> >
> > Set db = CurrentDb
> > Set myQdf = db.CreateQueryD ef("")
> > myQdf.SQL = mySql
> > Set db = Nothing
> > Set PassRS = myQdf.OpenRecor dset()
> > End Function
> > --------------------------------------------------------
> >
> > Then i call it like this,
> > --------------------------------------------------------
> > Private Sub Command1_Click( )
> > Call PassRS
> > With PassRS
> > .MoveLast
> > Debug.Print "count " & .RecordCount
> > End With
> > End Sub
> > ----------------------------------------------------------
> >
> > And its seems to work. But how do I destroy the recordset object, and[/color][/color]
the[color=blue][color=green]
> > qryDef object when i'm done with it?
> >
> > Thanks in advance for any advice.
> >
> > Gerry Abbott[/color]
>
>
>
> Hi Gerrry
> I'm not sure why you might need a general function like PassRS() , you[/color]
might[color=blue]
> retain more flexibility to open the recordset within the click sub.[/color]
Anyway,[color=blue]
> if you really did want this sort of function, you might structure it to
> handle errors gracefully. What about this as possibility?
>
>
> Public Function SetRS(strSQL, rst As DAO.Recordset) As Boolean
>
> On Error GoTo Err_Handler
>
> Set rst = CurrentDb.OpenR ecordset(strSQL , dbOpenSnapshot)
>
> SetRS = True
>
> Exit_Handler:
> Exit Function
>
> Err_Handler:
> MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
> Resume Exit_Handler
>
> End Function
>
>
>
>
> Private Sub Command1_Click( )
>
> On Error GoTo Err_Handler
>
> Dim rst As DAO.Recordset
> Dim strSQL As String
>
> strSQL = "SELECT * FROM MyTable WHERE ID < 6 ORDER BY ID"
>
> If Not SetRS(strSQL, rst) Then Exit Sub
>
> rst.MoveLast
>
> MsgBox rst.RecordCount
>
> rst.Close
>
> Set rst = Nothing
>
> Exit_Handler:
> Exit Sub
>
> Err_Handler:
> MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
> Resume Exit_Handler
>
> End Sub
>
>
>[/color]