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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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]

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.Openrecordset 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.OpenRecordset (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" <pl****@ask.ie> wrote in message
news:bJ*****************@news.indigo.ie...

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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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

[Fletcher Arnold]

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bJ*****************@news.indigo.ie...

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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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

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.OpenRecordset(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]

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(sqlParameter As Long) As DAO.Recordset
Dim mySQL As String

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


"Larry Linson" <bo*****@localhost.not> wrote in message
news:gx*****************@nwrddc01.gnilink.net...

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.Openrecordset 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.OpenRecordset (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" <pl****@ask.ie> wrote in message
news:bJ*****************@news.indigo.ie...

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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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

[Gerry Abbott]

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" <fl****@home.com> wrote in message
news:bo**********@hercules.btinternet.com...

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bJ*****************@news.indigo.ie...

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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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
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.OpenRecordset(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