Passing a recordset as a byref parameter (1 Viewer)

grenee · Aug 19, 2017

Good Day All,

I am passing a recordset variable as a byref parameter and it would not work.
Is there a special way to accomplish this objective?

Here is my code:

Code:

Public Sub SumSalesAccounts(ByRef rst As Recordset)
    Dim curDatabase As Database
    Dim strSql As String
    Dim Number As Integer
    
    strSql = "SELECT Sum(Quantity) AS SumQty, Account, Sum([Extended Price]) AS SumPrice, code3 " _
& "FROM [Chart of Accounts] INNER JOIN [Order Details Extended] ON [Chart of Accounts].[Account Name] = [Order Details Extended].Account " _
& "WHERE [Order ID]= 201  GROUP BY Account, [Order ID], code3;"
  Debug.Print strSql
    
    Set curDatabase = CurrentDb
    Set rst = curDatabase.OpenRecordset(strSql)
    Do While (Not rst.EOF)
     MsgBox rst.Fields(0) & " " & rst.Fields(1) & " " & rst.Fields(2)
     rst.MoveNext
    Loop
  
   Set rst = Nothing
   Set curDatabase = Nothing
   
End Sub

grenee · Aug 19, 2017

My error message says simply "Argument not optional"

isladogs · Aug 19, 2017

In order to run this as written, you would need to define the recordset at the start - NOT part of the way through as you are now

Try changing the function header line to:

Code:

Public Sub SumSalesAccounts()

grenee · Aug 20, 2017

Thanks. I am using your suggestion but it's not what I want. I want to pass a recordset variable to collect data to take to a calling module

isladogs · Aug 20, 2017

Not sure I understand what you're saying
- its late here & I'm tired so probably me.

In that case, can you specify strSQL & therefore rst BEFORE running the procedure?

moke123 · Aug 20, 2017

I want to pass a recordset variable to collect data to take to a calling module

Are you saying that you want to take the results of this procedure and pass them back to the calling procedure?
you would need a function not a sub.

Cronk · Aug 20, 2017

Grenee, I'd say it's not working because the recordset you have created before calling your Sub, is 'destroyed' when you set it to nothing. So you are setting the referenced recordset variable to Null.

Delete the line
set rst = nothing

MarkK · Aug 20, 2017

Maybe you mean to do this???

Code:

Public Function GetSumSalesAccounts(OrderID As Long) As DAO.Recordset
    Const SQL As String = _
        "SELECT Sum(Quantity) AS SumQty, Account, Sum([Extended Price]) AS SumPrice, code3 " & _
        "FROM [Chart of Accounts] INNER JOIN [Order Details Extended] " & _
            "ON [Chart of Accounts].[Account Name] = [Order Details Extended].Account " & _
        "WHERE [Order ID] = p0 " & _
        "GROUP BY Account, [Order ID], code3;"
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = OrderID
        Set GetSumSalesAccounts = .OpenRecordset
        .Close
    End With
End Function

Just guessing...
Mark

JHB · Aug 20, 2017

grenee said:
My error message says simply "Argument not optional"

How do you call the sub SumSalesAccounts, show your code!

Code:

Dim rst As DAO.Recordset
Call SumSalesAccounts(rst)

grenee · Aug 20, 2017

Thanks to all of you for your help. It seems I may need a lesson in the use of function

However the question I would ask is can a variable of reccordset type be passed a parameter?

Here are my procedures:

Code:

Public Sub GetSalesAccounts()
                 
    Dim curDatabase As Database
    Dim rst As Recordset
    
    SumSalesAccounts rst
     Do While (Not rst.EOF)
     MsgBox rst.Fields(0) & " " & rst.Fields(1) & " " & rst.Fields(2)
     rst.MoveNext
    Loop
    
   Set rst = Nothing
   Set curDatabase = Nothing
   
End Sub
Public Sub SumSalesAccounts(ByRef rst As Recordset)
    Dim curDatabase As Database
    Dim strSql As String
    Dim Num As Integer
    
    Num = 201
    
    strSql = "SELECT Sum(Quantity) AS SumQty, Account, Sum([Extended Price]) AS SumPrice, code3 " _
& "FROM [Chart of Accounts] INNER JOIN [Order Details Extended] ON [Chart of Accounts].[Account Name] = [Order Details Extended].Account " _
& "WHERE [Order ID]= " & Num & " GROUP BY Account, [Order ID], code3;"
  Debug.Print strSql
    
    Set curDatabase = CurrentDb
    Set rst = curDatabase.OpenRecordset(strSql)
   
  
End Sub

Actually it is now working. not sure why

JHB · Aug 21, 2017

grenee said:
..It seems I may need a lesson in the use of function

What you've isn't a function, but a procedure/Sub.
A function mostly has a return value:
https://msdn.microsoft.com/en-us/vb.../articles/calling-sub-and-function-procedures

grenee said:
..
However the question I would ask is can a variable of reccordset type be passed a parameter?

Yes it can, (which you've discovered now, then you write your code works).

grenee said:
..Actually it is now working. not sure why

stylo9 · Oct 2, 2023

You're passing a recordset to the sub but then you're re-defining the recordset by using strsql.
then you're setting the recordset "rst" to strsql but without the dbopendynaset argument
set rst = currentdb,openrecordset(strsql, dbopendynaset) or whichever type of recordset you wish.
if you just pass the rst then you don't have to re-define it.
-------------------------------------------------------------------------------------------------------------------
Public Sub SumSalesAccounts(ByRef rst As Recordset)

Do until rst.eof
MsgBox rst.Fields(0) & " " & rst.Fields(1) & " " & rst.Fields(2)
rst.MoveNext
Loop

Set rst = Nothing

End Sub

Mike Krailo · Oct 2, 2023

That was a six year old post you just responded to. Click the What's New button for more recent posts.

Passing a recordset as a byref parameter (1 Viewer)

grenee

Registered User.

grenee

Registered User.

isladogs

MVP / VIP

grenee

Registered User.

isladogs

MVP / VIP

moke123

AWF VIP

Cronk

Registered User.

MarkK

bit cruncher

JHB

Have been here a while

grenee

Registered User.

JHB

Have been here a while

stylo9

New member

Mike Krailo

Well-known member

Similar threads

Users who are viewing this thread