Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Unanswered: Table has results but recordset won't 'open' them

    Hi everyone,

    I have a function that I run once a year to purge old results out of a database ready for the new year. However it's now not working. I get a Run-time error '3021': Either BOF or EOF are True, or the current record has been deleted.

    Code:
    Function purgeResults(mt As Integer, sn As String)
    
      Dim dbs As ADODB.Connection
      Dim rst As ADODB.Recordset
    
      'delete all old best results
      DoCmd.SetWarnings False
      DoCmd RunSQL "DELETE FROM RESULT WHERE MEET = " & mt
    
      'delete all but best results
      Set dbs = CurrentProject.Connection
      
      rst.Open "SELECT * FROM qryNonBestPerformances", dbs
    
      rst.MoveFirst
      While Not rst.EOF
        DoCmd RunSQL "DELETE FROM RESULTS WHERE RESULT.RESULT = " rst!OldResult
        rst.MoveNext
      Wend
    
      rst.Close
    
      rst.Open "SELECT * FROM qryNewEventIDs", dbs
      
      rst.MoveFirst   <-- this is where it errors
      While Not rst.EOF
        DoCmd RunSQL "UPDATE RESULT SET MEET = " & mt & ", MTEVENT = " & rst!NewID & " WHERE MTEVENT = " & rst!OldID
        rst.MoveNext
      Wend
    
      rst.Close
    
    End Function
    I have checked the query qryNewEventIDs and it has about 5000 records in it, so I don't understand why, when I 'open' it into the recordset, it has 0 records.

    Any help would be appreciated

    K

  2. #2
    Join Date
    Aug 2007
    Posts
    4
    Ok, so it turns out I had a "Like" operator in one of my queries which worked when I opened the Query to view, but not when I tried to open it into a recordset.

    Go figure

Posting Permissions

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