How to test and see if a recordset exists before running close command 1

[kjv1611]

I was working on some error trapping code which I would like to use to handle any type of error, and clean up where need be IF necessary. The part I am wanting to verify is how to check for open recordsets (DAO preferable), and close them if open, and set to nothing if they exist.

So, here is some psuedo code demonstrating what I would like to do:

Private Sub CreateSomeRecordsets()
On Error GoTo HandleErr
  Dim db As DAO.Database
  Dim rs1 As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim rs3 As DAO.Recordset

  Set db = CurrentDb
  Set rs1 = db.OpenRecordset("Table1")
  Set rs2 = db.OpenRecordset("Table2")
  Set rs3 = db.OpenRecordset("Table3")

  'Code to do what I want to do with the 3 tables

  Exit Sub

HandleErr:
  MsgBox Err.Code & " " & Err.Description
  If Exists(rs1) Then  
    If IsOpen(rs1) Then
      rs1.Close
    End If
    Set rs1 = Nothing
  End If
  If Exists(rs2) Then  
    If IsOpen(rs2) Then
      rs2.Close
    End If
    Set rs2 = Nothing
  End If
  If Exists(rs3) Then  
    If IsOpen(rs3) Then
      rs3.Close
    End If
    Set rs3 = Nothing
  End If
  If Exists(db) Then  
    If IsOpen(db) Then
      db.Close
    End If
    Set db = Nothing
  End If

Or if were possible to do something like this:

  For Each rs in db.Recordsets
    'Check to see if exists, and if open, close/clear as necessary.
  Next rs

If anyone has any critiques or suggestions at all on this, I would greatly appreciate any input given.

Thanks!

 

[pjm]

Hi

You don't need to check...

on error resume next
rs1.close
rs2.close
rs3.close
set rs1 = nothing
set rs2 = nothing
set rs3 = nothing

HTH

pjm

 

[kjv1611]

That's a thought, but I'd rather try to do it outside of using that sort of command, as it negates the whole error-trapping/handling idea, anyway. But, if I did, I guess I could use the error handling for everything else, and just change the On Error portion just above the closing of everything. Thanks for the suggestion.

Any other thoughts?

 

[pjm]

Hi

Check for types of errors that you can do something about, and handle those appropriately.

If you get an error you can't handle then you should still close and destroy the recordset objects. i.e. after a critical error you want to try to clean up as best you can.

pjm

 

[kjv1611]

True.

One thing I saw elsewhere was this sort of idea:

If rs1 Is Not Nothing Then
  rs.Close
  Set rs = Nothing
End If

Haven't tried it yet, and not sure if it would be wise at all, but thought I'd throw that idea out to see if anyone thought it was good/bad.

 

[kjv1611]

Just thought I'd post back with an update. Using:
On Error Resume Next
Did not work. I even tried adding in DoCmd.SetWarnings False and then True afterwards, and that didn't work.

Also, this does not seem to work, either:
If rs1 Is Not Nothing...
It gave errors when tried it.

 

[PHV]

and this ?
If Not (rs1 Is Nothing) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pjm]

Just thought I'd post back with an update. Using:
On Error Resume Next
Did not work.

In what way did it "not work"?

pjm

 

[kjv1611]

Sorry, pjm, I should have posted more detail.
I was getting an error message about "object or with not defined" - I forget the exact error, but it's normally the error you get when an object has not been instantiated.

I'll try PHV's note, and post back. Thanks.

 

[kjv1611]

PHV's changing of the statement worked just fine. Thanks!

I may still add the "On Error Resume Next" at the very end where it is just closing out everything, anyway, just to cover, possibly. I didn't think I wanted to use that, simply b/c I had read in some posts, here, some say that was sloppy coding.

Well, thanks all for the assistance!