Check if recordset is populated?

[DanWallace]

Hi there,

I have a long function that checks for errors in a series of tables. Basically I'm using one recordset to step through my tables and check for errors and one recordset to update my error report table. I'll usually populate my first recordset with something like this:

strSQL1 = "SELECT table1.[field1], table1.[field2], table1.[field3] FROM tablename WHERE (conditional check for errors here)"
Set db1 = CurrentDb
Set rsTable = db1.OpenRecordset(strSQL1)

and then I go on to count and modify the results with a Do While Not rsTable.EOF().

This works fine as long as the field names are all correct. But if someone were to modify a fieldname, my function would then loop forever and crash the database. This is no good.

Is there a way that I can check to see if the recordset got any results or is there a better way of doing this that I'm overlooking?

 

[pbaldy]

I'm not sure I'm following. If the recordset doesn't return any records, the EOF will be true and the loop will be skipped. If they change a field name, I'd expect an error to be raised by the SQL when it tries to open the recordset.

 

[DanWallace]

I'm not sure I'm following. If the recordset doesn't return any records, the EOF will be true and the loop will be skipped. If they change a field name, I'd expect an error to be raised by the SQL when it tries to open the recordset.

So how do I check if an error was raised? It seems to just go into the loop and loop forever as it is now. I can step into it and it will just continually loop.

 

[pbaldy]

What's the full code for the loop? This line would have to be inside it:

rsTable.MoveNext

 

[The_Doc_Man]

So how do I check if an error was raised?

In VBA, use the ON ERROR THEN ... construct, which you can look up in Access VBA Help.

 

[DanWallace]

What's the full code for the loop? This line would have to be inside it:

rsTable.MoveNext

If TableExists("bif955") = True Then
    strSQL1 = "SELECT bif955.[I_TRANSNUM], bif955.[I_BILLNUMBER] FROM bif955 WHERE (((bif955.[I_TRANSNUM]) In (SELECT [I_TRANSNUM] FROM [bif955] As Tmp GROUP BY [I_TRANSNUM] HAVING Count(*)>1 ))) ORDER BY bif955.[I_TRANSNUM];"
        Set db1 = CurrentDb
        Set rsTable = db1.OpenRecordset(strSQL1)
    
    rsTable.MoveFirst
    counter = 0
    
    Do While Not rsTable.EOF()
        counter = counter + 1
        If counter < 11 Then
            rs2.AddNew
                rs2.I_TRANSNUM = rsTable.I_TRANSNUM
                rs2.I_BILLNUMBER = rsTable.I_BILLNUMBER
                rs2.Error_Code = 20
            rs2.Update
        End If
        rsTable.MoveNext
    Loop

......

MoveNext is there. I've stepped through it and if the field name is wrong (for example, it was I_BILLNUMBER where I had I_BILLNUM), it never seems to recognize the EOF. It should just be skipping over the loop all together but it's not.

 

[pbaldy]

If the field names are wrong, you should get an error at this line:

Set rsTable = db1.OpenRecordset(strSQL1)

and even if they're right, if the recordset is empty I think you'd get an error at this line:

rsTable.MoveFirst

You can't post a sample db, can you? There's no way it should get into the loop with a bad field name. You don't have something like this do you?

On Error Resume Next

 

[DanWallace]

Might be worth mentioning I didn't dim my recordsets as recordsets.

Just Dim rsTable, rs1, rs2

I dunno why but it won't work if I put "As Recordset"

 

[pbaldy]

You'd want to make sure the DAO reference is check in Tools/References, then

Dim rsTable As DAO.Recordset

 

[DanWallace]

If the field names are wrong, you should get an error at this line:

Set rsTable = db1.OpenRecordset(strSQL1)

and even if they're right, if the recordset is empty I think you'd get an error at this line:

rsTable.MoveFirst

You can't post a sample db, can you? There's no way it should get into the loop with a bad field name. You don't have something like this do you?

On Error Resume Next

Yeah, I do have that On Error Resume Next in there. Someone else actually started writing this and I took over, so they had that in there.

 

[pbaldy]

That's not error handling, it's error ignoring. I'd put regular error handling in. Generically it would look like:

Private Sub...
  On Error GoTo ErrorHandler

  'your code here

ExitHandler:
  'clean up recordsets, etc
  Exit Sub

ErrorHandler:
  Select Case err
    Case 2501          'Action OpenReport was cancelled.
      MsgBox "No data to display"
      DoCmd.Hourglass False
      Resume ExitHandler
    Case Else
      MsgBox err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

 

[DanWallace]

That's not error handling, it's error ignoring. I'd put regular error handling in. Generically it would look like:

Private Sub...
  On Error GoTo ErrorHandler

  'your code here

ExitHandler:
  'clean up recordsets, etc
  Exit Sub

ErrorHandler:
  Select Case err
    Case 2501          'Action OpenReport was cancelled.
      MsgBox "No data to display"
      DoCmd.Hourglass False
      Resume ExitHandler
    Case Else
      MsgBox err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

You'll have to excuse me, I'm a noob at all this, but when you say "clean up recordsets etc" what do you mean exactly?

 

[DanWallace]

You'd want to make sure the DAO reference is check in Tools/References, then

Dim rsTable As DAO.Recordset

Nah, still does the same thing. If I have more than two recordsets open it says "Method or Datamember not found" as soon as I try to add anything to the third one.

The first one will work fine. The second one will work fine. But if I attempt to do anything to rs3 it just gives me that error.

 

[pbaldy]

when you say "clean up recordsets etc" what do you mean exactly?

A general rule is that anything you "Set", you close and/or set to nothing when you're done with it, so in the exit handler I'd have:

Set rsTable = Nothing

Are you doing something different with rs3? You should be able to have more than 2.

 

[DanWallace]

A general rule is that anything you "Set", you close and/or set to nothing when you're done with it, so in the exit handler I'd have:

Set rsTable = Nothing

Are you doing something different with rs3? You should be able to have more than 2.

Oh ok, I get it.

And no, absolutely nothing. This is exactly what I've tested it with when it wouldn't work in my code:

Dim db1 As Database
Dim rsTable, rs2, rs3 As DAO.Recordset
Dim strSQL1 As String
Dim counter as Double

Public Function ErrorReports()

DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM [tblErrorStats]")
DoCmd.RunSQL ("DELETE * FROM [tblErrorRecords]")
DoCmd.SetWarnings True

Set db1 = CurrentDb

strSQL1 = "SELECT * FROM [tblErrorRecords]"
    Set rs2 = db1.OpenRecordset(strSQL1)

strSQL1 = ("SELECT * FROM [tblErrorStats]")
    Set rs3 = db1.OpenRecordset(strSQL1)

rs3.AddNew
rs3.Error_Code = 1 '<--- Error right here
rs3.Update

And if I try to update with rs2 or rsTable it works fine.

I've even switched it around so rs2 handles tblErrorStats and it works fine like that. It's just nothing will work in rs3.

 

[pbaldy]

Well, you are doing something different, but probably don't realize it. This line:

Dim rsTable, rs2, rs3 As DAO.Recordset

declares 2 variants and 1 recordset. In VBA you have to do this:

Dim rsTable As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset

but I suspect the problem is this:

rs3.Error_Code = 1

should be

rs3!Error_Code = 1

 

[DanWallace]

Well, you are doing something different, but probably don't realize it. This line:

Dim rsTable, rs2, rs3 As DAO.Recordset

declares 2 variants and 1 recordset. In VBA you have to do this:

Dim rsTable As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset

but I suspect the problem is this:

rs3.Error_Code = 1

should be

rs3!Error_Code = 1

Man, I never want to start coding from someone elses project ever again. I changed everything the way you explianed here and it works MUCH better now. Thank you so much for your help.

My only real remaining issue is, like you mentioned earlier, if I don't get any data back in my query and I go to MoveFirst on the recordset, it pitches me an error. Is there a simple ifcheck I can maybe throw in here to see if the recordset has anything at all?

 

[pbaldy]

In the context of what it looks like you're doing, you don't really need that line. The recordset will start at the first record anyway. If you did need it, you could test for .EOF with an If/Then and only execute that line if it wasn't EOF.

 

[DanWallace]

In the context of what it looks like you're doing, you don't really need that line. The recordset will start at the first record anyway. If you did need it, you could test for .EOF with an If/Then and only execute that line if it wasn't EOF.

Thanks mate! Everything's working great now. I can keep my job.

 

[pbaldy]

No problemo, glad we got it sorted out.