A Function that returns a Recordset

[keirnus]

Hello,

I'm into DB access now.
I have a SELECT Query code here:

Public Sub DBSelectUpdateListTable()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT ActivityID FROM " & DB_UPDATE_LIST & " ORDER BY ActivityID")
    
    While Not rs.EOF
        
        rs.MoveFirst
        GatherDataHere = rs![ActivityID]
        rs.MoveNext
        
    Wend
    
    rs.Close
    db.Close
End Sub

I want this function to be exclusively for DB access only.
So, I want to get the gathered recordset data out from this function.

What is the best way to do that?

I was thinking of return function but I don't know what to use for a Recordset.

[ADezii]

Here is an example of a Public Function that will:

1. Return a DAO Recordset
   

   Public Function fReturnRecordset() As DAO.Recordset
   Dim MyDB As DAO.Database
   Dim MyRS As DAO.Recordset
   Dim strSQL As String
   
   'From the Employees Table in the Northwind Database
   strSQL = "Select * From Employees Order by Employees.LastName;"
   
   Set MyDB = CurrentDb
   Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
   
   'Set the return value of the Function = a DAO Recordset
   Set fReturnRecordset = MyRS
   End Function

2. Process the Recordset returned by the Function in the Click() Event of a Command Button:
   

   Private Sub Detail_DblClick(Cancel As Integer)
   'Process the Recordset returned from the fReturnRecordset() Function
   Dim intCounter As Integer
   Dim rstRet As DAO.Recordset
   
   Set rstRet = fReturnRecordset()
   
   With rstRet
     Do While Not rstRet.EOF
       Debug.Print ![FirstName] & " " & ![LastName] & ", (" & ![Title] & ")"
       .MoveNext
     Loop
   End With
   
   rstRet.Close
   Set rstRet = Nothing
   End Sub

3. OUTPUT:
   

   Steven Buchanan, (Sales Manager)
   Laura Callahan, (Inside Sales Coordinator)
   Nancy Davolio, (Sales Representative)
   Anne Dodsworth, (Sales Representative)
   Andrew Fuller, (Vice President, Sales)
   Robert King, (Sales Representative)
   Janet Leverling, (Sales Representative)
   Margaret Peacock, (Sales Representative)
   Michael Suyama, (Sales Representative)

4. Any questions, please feel free to ask.

[keirnus]

Here is an example of a Public Function that will:

1. Return a DAO Recordset
   

   Public Function fReturnRecordset() As DAO.Recordset
   Dim MyDB As DAO.Database
   Dim MyRS As DAO.Recordset
   Dim strSQL As String
   
   'From the Employees Table in the Northwind Database
   strSQL = "Select * From Employees Order by Employees.LastName;"
   
   Set MyDB = CurrentDb
   Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
   
   'Set the return value of the Function = a DAO Recordset
   Set fReturnRecordset = MyRS
   End Function

2. Process the Recordset returned by the Function in the Click() Event of a Command Button:
   

   Private Sub Detail_DblClick(Cancel As Integer)
   'Process the Recordset returned from the fReturnRecordset() Function
   Dim intCounter As Integer
   Dim rstRet As DAO.Recordset
   
   Set rstRet = fReturnRecordset()
   
   With rstRet
     Do While Not rstRet.EOF
       Debug.Print ![FirstName] & " " & ![LastName] & ", (" & ![Title] & ")"
       .MoveNext
     Loop
   End With
   
   rstRet.Close
   Set rstRet = Nothing
   End Sub

3. OUTPUT:
   

   Steven Buchanan, (Sales Manager)
   Laura Callahan, (Inside Sales Coordinator)
   Nancy Davolio, (Sales Representative)
   Anne Dodsworth, (Sales Representative)
   Andrew Fuller, (Vice President, Sales)
   Robert King, (Sales Representative)
   Janet Leverling, (Sales Representative)
   Margaret Peacock, (Sales Representative)
   Michael Suyama, (Sales Representative)

4. Any questions, please feel free to ask.

wOwoweee!
It definitely works! :) yey!

But I noticed something.
The MyRS was closed (rstRet in the other function) but not MyDB.

Dim MyDB As DAO.Database

Guess it needs to be passed as well?

[ADezii]

wOwoweee!
It definitely works! :) yey!

But I noticed something.
The MyRS was closed (rstRet in the other function) but not MyDB.

Dim MyDB As DAO.Database

Guess it needs to be passed as well?

The MyRS was closed (rstRet in the other function) but not MyDB.

You cannot close the Recordset in the Function since you will receive the Object invalid or no longer set Error Message, it must be closed in the calling Routine.

Guess it needs to be passed as well?
Dim MyDB As DAO.Database

No, it does not need to be passed.