SQL - Test response for results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following SQL string:
strSQL = "SELECT tblorderissues.iissueind " & _
"FROM tblorderissues " & _
"WHERE tblorderissues.iorderid = '" & Forms!frmorders!OOrderID & "'"

If it returns results, I want to perform one action. If there are no
results, I want to return another. How do I test to see if there are any
results?

Thanks, Judy
 
Hi Judy,

You can use code like this. This code requires a reference set to the
Microsoft DAO 3.6 Object Library.


Sub Test()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "SELECT tblorderissues.iissueind " & _
"FROM tblorderissues " & _
"WHERE tblorderissues.iorderid = '" & Forms!frmorders!OOrderID & "'"

Set rs = db.OpenRecordset(strSQL, dbFailOnError)

If rs.RecordCount > 0 Then
'Do Something
Else
'Do Something else
End If


ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Test subroutine..."
Resume ExitProc
End Sub
Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
I believe it is:-

if rs.bof and rs.eof then 'there are no records
else ' there is something to work with
 
Back
Top