Re: How do I test for no records returned in code?

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 11:23:48 +0300

Kate,

Here's one way to do it:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as String

strSQL = "SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,"
strSQL = strSQL & " AVAILABILITY.Room, AVAILABILITY.Day,
AVAILABILITY.BookingID"
strSQL = strSQL & " WHERE AVAILABILITY.BookingDate >= " & EndDate

Set db = CurrentDB()
Set rst = db.OpenRecordset(strSQL)

On Error GoTo No_Rec
rst.MoveLast
On Error GoTo 0

'Code to do what you want

No_Rec:
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

You will need to add the Microsoft DAO reference ( 3.51 for A97, 3.6 for A2K
or later) if you don't already have it. To do so go to menu item Tools >
References from the VB editor window, scroll down to find the Microsoft DAO
reference and click the tickbox next to it.

HTH,
Nikos

"Kate" <anonymous@discussions.microsoft.com> wrote in message
news:5e0a01c42de8$d88df0e0$a601280a@phx.gbl...
> In a macro I have a select statement
>
> SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
> AVAILABILITY.Room, AVAILABILITY.Day, AVAILABILITY.BookingID
>
> WHERE AVAILABILITY.BookingDate >= EndDate
>
> How do I test for no records returned?
>
> Is there some variable set that I can test?
>
> Thanks

Relevant Pages

  • Re: Export a reprot to Excel
    ... 'you can choose which worksheet will receive the data ... Dim objXLApp As Object 'Excel.Application ... Dim rst As DAO.Recordset ... 'replace with names and cell references that suit your template ...
    (microsoft.public.access.reports)
  • Re: CopyFromRecordset Run-time error 430:
    ... >rge.CopyFromRecordset rst. ... The error message returned is 'Run-time error 430: ... I've check the references within Access and Excel on the selected ... > Dim xlApp As New Excel.Application ...
    (microsoft.public.access.formscoding)
  • VB IDE Collating Seq error
    ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ... Dim strSQL As String ...
    (microsoft.public.vb.database.ado)
  • Re: Creating a query in code
    ... If Access complains about an unknown data type on this line: ... Choose References on the Tools menu, ... Dim db As DAO.Database and the procedure wont ... Dim strSql As String ...
    (comp.databases.ms-access)
  • Re: auto-run all of my queries
    ... Dim rst As DAO.Recordset ... Dim strSQL As String ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)