close recordset via code using arrays

[TimTDP]

I often have procedures that contain multiple recordsets. Rather than writing a line of code to close each recordset I want to write a module to do it.

In my procedure I may have the following:

Dim rst1 as dao.recordset
Dim rst2 as dao.recordset

When I want to close these I want to run a line code:
CloseRecordSet rst1, rst2

This will run the function

Public Function CloseRecordSet(ParamArray RecordsetName())
Dim x as Integer

For x = 0 to Unbound(RecordsetName)
RecordsetName.Close
Next

End Function

Access does not like the RecordsetName in RecordsetName.Close!

What am I doing wrong?

 

[DCrake]

You cannot close a recordset unless it has been set. Also you cannot close a recordset that is empty ie EOF and BOF = True

For good practice you should be closing then after your Loop and then setting them to Nothing to clear from cache

 

[TimTDP]

I have not shown the setting of the recordset
I do know understand that it needs to be set!

I will check that it has been set using
If Not RecordsetName is Nothing Then
RecordsetName.Close
Set RecordsetName = Nothing
End if

 

[DCrake]

Usual syntax

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenrecordSet("TableOrQuery")

If Not Rs.EOF and Not Rs.BOF Then

   Do Until Rs.EOF

      Code here

      Rs.MoveNext

   Loop
   Rs.Close
End If

Set Rs = Nothing

 

[boblarson]

What am I doing wrong?

Not 100% positive as I haven't used a parameter array for recordset objects but it might work if you gave the index number and used UBound instead of Unbound.

For x = 0 to UBound(RecordsetName)
RecordsetName(x).Close
Next

 

[TimTDP]

I haven't used a parameter array for recordset objects

I think that this is where the problem is