checking .bof and .eof for empty recordset?

[Matt Williamson]

What am I missing here? I'm trying to determine if the recordset returned
from a dynamic query is empty, but it's always returning empty even when it
contains records. Is there a better way to check for an empty recordset?


'decs snipped

Set MyDB = CurrentDb()

strSQLPorts = "Select " 'Gets list of portcodes to use in next query
MyDB.QueryDefs.Delete ("Portlist")
Set qdf = MyDB.CreateQueryDef("Portlist", strSQLPorts)
Set rstPorts = qdf.OpenRecordset(dbOpenSnapshot)

With rstPorts
Do While Not .EOF

strSQL = "SELECT ..."
strWhere = 'Where clause

strSQL = strSQL & strWhere
' Debug.Print strSQL
MyDB.QueryDefs.Delete ("DynPerfCalcs")
Set qdf = MyDB.CreateQueryDef("DynPerfCalcs", strSQL)
Set rstData = qdf.OpenRecordset(dbOpenSnapshot)
If Not rstData.BOF And rstData.EOF Then
DoCmd.OutputTo acOutputReport, "YearEndPerfDyn", acFormatRTF,
"C:\temp\" & .Fields("portfoliocode") & ".doc"
Else
Debug.Print "No records returned for portcode: " &
..Fields("portfoliocode")
End If
strSQL = ""
.MoveNext
Loop
End With

TIA

Matt

 

[Matt Williamson]

What am I missing here? I'm trying to determine if the recordset returned

from a dynamic query is empty, but it's always returning empty even when
it contains records. Is there a better way to check for an empty
recordset?

Nevermind.

I just needed Or where I had And

'no work
If Not rstData.BOF And rstData.EOF Then

'works
If Not (rstData.BOF Or rstData.EOF) Then

 

[Klatuu]

I would suggest a different method:

If rstData.RecordCount > 0 Then
'It is not empty

 

[Tony Toews [MVP]]

I would suggest a different method:

If rstData.RecordCount > 0 Then
'It is not empty

I've been using rstData.EOF = true without any problems. Not the BOF
though.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Klatuu]

okay.
Not really an issue. Just making a suggestion.