Problems when no rows returned

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

I need help in handling situations when there are no rows in a record set.
When the following script returns rows, li_count has a value of 1, no
matter how many are actually returned. I know I can deal with that by doing
a .movelast .movefirst before getting the value of recordcount. But if
there happen to be no rows returned, then the .Move* code produces a "no
current record" error. Feels like a catch 22... This seems like such a
common situation. There must be a simple technique?

ls_temp = "Select * from tblStudents Where class_code = '" & cboClass_Code
& "'"
Set rstClassList = CurrentDb.OpenRecordset(ls_temp)
li_count = rstClassList.RecordCount
 
You can use code like:
Dim lngRecCount
With rstClassList
If Not (.eof and .bof) then
.movelast
lngRecCount = .RecordCount
Else
lngRecCount = 9
End If
End With
 
Hi,
If no records are returned then both EOF and BOF are true.
So check for that:

If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
End If
 
I think Duane meant 0 rather than 9 in the code. Perhaps, it is because 9
is right next to 0 on the keyboard.
 
Back
Top