Passing a recordset as a byref parameter

[grenee]

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:

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]

My error message says simply "Argument not optional"

 

[isladogs]

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:

Public Sub SumSalesAccounts()

 

[grenee]

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]

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]

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]

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]

Maybe you mean to do this???

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]

My error message says simply "Argument not optional"

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

Dim rst As DAO.Recordset
Call SumSalesAccounts(rst)

 

[grenee]

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:

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]

..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

..
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).

..Actually it is now working. not sure why

 

[stylo9]

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]

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