By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

A Function that returns a Recordset

P: 48
Hello,

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub DBSelectUpdateListTable()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rs As DAO.Recordset
  5.  
  6.     Set db = CurrentDb
  7.     Set rs = db.OpenRecordset("SELECT ActivityID FROM " & DB_UPDATE_LIST & " ORDER BY ActivityID")
  8.  
  9.     While Not rs.EOF
  10.  
  11.         rs.MoveFirst
  12.         GatherDataHere = rs![ActivityID]
  13.         rs.MoveNext
  14.  
  15.     Wend
  16.  
  17.     rs.Close
  18.     db.Close
  19. End Sub
  20.  
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.
Aug 19 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,669
Here is an example of a Public Function that will:
  1. Return a DAO Recordset
    Expand|Select|Wrap|Line Numbers
    1. Public Function fReturnRecordset() As DAO.Recordset
    2. Dim MyDB As DAO.Database
    3. Dim MyRS As DAO.Recordset
    4. Dim strSQL As String
    5.  
    6. 'From the Employees Table in the Northwind Database
    7. strSQL = "Select * From Employees Order by Employees.LastName;"
    8.  
    9. Set MyDB = CurrentDb
    10. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    11.  
    12. 'Set the return value of the Function = a DAO Recordset
    13. Set fReturnRecordset = MyRS
    14. End Function
  2. Process the Recordset returned by the Function in the Click() Event of a Command Button:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Detail_DblClick(Cancel As Integer)
    2. 'Process the Recordset returned from the fReturnRecordset() Function
    3. Dim intCounter As Integer
    4. Dim rstRet As DAO.Recordset
    5.  
    6. Set rstRet = fReturnRecordset()
    7.  
    8. With rstRet
    9.   Do While Not rstRet.EOF
    10.     Debug.Print ![FirstName] & " " & ![LastName] & ", (" & ![Title] & ")"
    11.     .MoveNext
    12.   Loop
    13. End With
    14.  
    15. rstRet.Close
    16. Set rstRet = Nothing
    17. End Sub
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Steven Buchanan, (Sales Manager)
    2. Laura Callahan, (Inside Sales Coordinator)
    3. Nancy Davolio, (Sales Representative)
    4. Anne Dodsworth, (Sales Representative)
    5. Andrew Fuller, (Vice President, Sales)
    6. Robert King, (Sales Representative)
    7. Janet Leverling, (Sales Representative)
    8. Margaret Peacock, (Sales Representative)
    9. Michael Suyama, (Sales Representative)
  4. Any questions, please feel free to ask.
Aug 19 '08 #2

P: 48
Here is an example of a Public Function that will:
  1. Return a DAO Recordset
    Expand|Select|Wrap|Line Numbers
    1. Public Function fReturnRecordset() As DAO.Recordset
    2. Dim MyDB As DAO.Database
    3. Dim MyRS As DAO.Recordset
    4. Dim strSQL As String
    5.  
    6. 'From the Employees Table in the Northwind Database
    7. strSQL = "Select * From Employees Order by Employees.LastName;"
    8.  
    9. Set MyDB = CurrentDb
    10. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    11.  
    12. 'Set the return value of the Function = a DAO Recordset
    13. Set fReturnRecordset = MyRS
    14. End Function
  2. Process the Recordset returned by the Function in the Click() Event of a Command Button:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Detail_DblClick(Cancel As Integer)
    2. 'Process the Recordset returned from the fReturnRecordset() Function
    3. Dim intCounter As Integer
    4. Dim rstRet As DAO.Recordset
    5.  
    6. Set rstRet = fReturnRecordset()
    7.  
    8. With rstRet
    9.   Do While Not rstRet.EOF
    10.     Debug.Print ![FirstName] & " " & ![LastName] & ", (" & ![Title] & ")"
    11.     .MoveNext
    12.   Loop
    13. End With
    14.  
    15. rstRet.Close
    16. Set rstRet = Nothing
    17. End Sub
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Steven Buchanan, (Sales Manager)
    2. Laura Callahan, (Inside Sales Coordinator)
    3. Nancy Davolio, (Sales Representative)
    4. Anne Dodsworth, (Sales Representative)
    5. Andrew Fuller, (Vice President, Sales)
    6. Robert King, (Sales Representative)
    7. Janet Leverling, (Sales Representative)
    8. Margaret Peacock, (Sales Representative)
    9. 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.

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2.  
Guess it needs to be passed as well?
Aug 19 '08 #3

ADezii
Expert 5K+
P: 8,669
wOwoweee!
It definitely works! :) yey!

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

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2.  
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.
Aug 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.