Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006

    Unanswered: error when query without data

    Hello everyone

    Im currently developing a database using MS Access,
    I had to introduce some macros which will run every time the database is loaded (unless it loads more than once in a day. (Therefore running at the most, once every day)

    I found some problems which I have sorted, however Ive now got another problem.

    The macros use queries to extract data from the tables, and copies the data from the queries into one generalized table (which a form to interfaces to)

    Sometimes/generally, the queries wont find any records in the tables therefore (in the macro) when I try to select the data and copy it, an error arrives because there is no data to copy.

    I then tried writing simple VBA/SQL code which world try to decide if the query will have data of not (if so run the macro which is already made OR if not, dont run it), and Bang, the error is gone

    But not rely, the thing crashes when attempting to look for an item (even if Im trying to see if there isnt one there)

    This is the code so far
    Public Function IncValidation()
    'Declare variables
    Dim TempDate As Date
    Dim SQL As String
    Dim r As New ADODB.Recordset
    Dim cn As ADODB.Connection

    'Set the SQL Statment
    SQL = "SELECT Increments.DateDue " & _
    " FROM Increments" & _
    " WHERE Increments.DateDue = Date()"

    'Set the Database to use
    Set cn = CurrentProject.Connection

    'Open the connection and perform the SQL
    r.Open SQL, CurrentProject.Connection

    TempDate = r.Fields.Item("DateDue")

    If TempDate = Date Then 'if the item is the same as todays date

    MsgBox "Running Macro" 'message for testing code
    DoCmd.RunMacro "mcrIncrementPart2"
    'do nothing
    MsgBox "Not Running Macro" 'message for testing code
    End If
    End Function


    This line

    TempDate = r.Fields.Item("DateDue")

    This is where it crashes because there is no record (it works fine when there is a record)

    I tried something to do with a .count instead of .item, but that returns a 1 all the time.

    Can anyone think of some way of doing this?
    I rely appreciate the help, and hold every sympathy if anyone has actually read the whole of this message,


  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    To check to see if there are any records in the recordset you can check EOF and BOF

    'Open the connection and perform the SQL
    r.Open SQL, CurrentProject.Connection
    if r.EOF and r.BOF Then
       'Recordset is empty do ???
       TempDate = r.Fields.Item("DateDue")
    end if
    If TempDate = Date Then 'if the item is the same as todays date
    EOF is true when you are at the end of the recordset, and BOF is True when you are at the beginning of the recordset. They are both True when you have nothing in the recordset.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts