Passing a Recordset to another Subroutine

I have 2 routines writing to the same output recordset.  The second routine is called from the first.  

Rather than opening and closing the recordset in each subroutine I want to pass it as a parameter to the 2nd routine.  I am getting an error

Code snip from routine 1
'
Dim rsOut As ADODB.Recordset
Set rsOut = New ADODB.Recordset
rsOut.Open "tblRpt_TaxRecs_Fees_ForExport_Local", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
Dim rsIn As ADODB.Recordset
Set rsIn = New ADODB.Recordset
rsIn.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
'///////////////////////////////////////
If rsIn.EOF Then
    Exit Sub
Else
    If rsIn.RecordCount > 0 Then
        '
        rsIn.MoveFirst
        '
        writeOneRecForEachCOPFeeType Nz(rsIn!BRT), rsOut
        '
        While Not rsIn.EOF

Open in new window



Routine 2:
Private Sub writeOneRecForEachCOPFeeType(passedBRT As Long, passedRsOut As Recordset)
'
' This routine is to put a record out for every possible COP Type using the first BRT encountered.
'  Since it is a summary report the detail records will never show anywhere.
'
'  If not done, the reprot may break since it's based on a crosstab query and every COP Type
'  might not be included in every set of selected records
'
'
Dim selectString2 As String

selectString2 = " Select COPFeeType From qry_COPFeeTypes_Unique_Sorted " & _
            "Where ( ( [COPFeeType] <> " & Chr(34) & wkConvertAdj & Chr(34) & " ) " & _
             " And   ( [COPFeeType] <> " & Chr(34) & wkDirectPayCreate & Chr(34) & " ) " & _
             " And   ( [COPFeeType] <> " & Chr(34) & wkRequesttoSatisfy & Chr(34) & " ) " & _
             " And   ( [COPFeeType] <> " & Chr(34) & wkStartUpPayCreate & Chr(34) & " ) " & _
             " And   ( [COPFeeType] <> " & Chr(34) & wkSynchCreate & Chr(34) & " ) )"
'

Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
'///////////////////////////////////////
If rsIn2.EOF Then
    Exit Sub
Else
    If rsIn2.RecordCount > 0 Then
        '
        rsIn2.MoveFirst
        '
        While Not rsIn2.EOF
            '
            passedRsOut.AddNew
                passedRsOut!BRT = passedBRT
                passedRsOut!COPFeeType = rsIn2!COPFeeType
                passedRsOut!CurrBalanceAmt = 0
              '  passedRsOut!DateSentToCOP = rsIn!DateSentToCOP
            passedRsOut.Update
            
            rsIn2.MoveNext
        Wend
    End If
End If
'
rsIn2.Close
Set rsIn2 = Nothing
'
End Sub

Open in new window


I am getting a 'Type Mismatch' error on this line in Routine 1

writeOneRecForEachCOPFeeType Nz(rsIn!BRT), rsOut

Open in new window


Is there something wrong with the way I'm passing theoputput recordset?
LVL 1
mlcktmguyAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,
pls try
Private Sub writeOneRecForEachCOPFeeType(passedBRT As Long, passedRsOut As ADODB.Recordset)

Open in new window

Regards
2
 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
In short, when you write:
Private Sub writeOneRecForEachCOPFeeType(passedBRT As Long, passedRsOut As Recordset)

Open in new window

VBA pick the first type within the loaded libraries corresponding to a Recordset object.

Bad luck with MS Access, the first Library with a recordset is DAO, so in your code, the recordset parameter is implicitly a DAO.Recordset object (of course it is nowhere near compatible with ADODB.Recordset).

Conclusion:
Always fully qualify your variables, this will prevent confusions.
1
 
mlcktmguyAuthor Commented:
Thanks for the correct syntax and the detailed explanation.

I was pretty sure I had used 'As Recordset' successfully previously.  I didn't realize it only worked because that recordset was DAO.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.