[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1194
  • Last Modified:

Run-time error 3021; Either BOF or EOF is true or the current record has been deleted

Hi, I get the following error in our database when we run the below code which is designed just to remove a customer from our table.

Any ideas on what this might be; I have tried on two different machines; both XP Pro SP3 with Access 2003.

Many Thanks

Private Sub TerminateContract(clientID, dteDate)

    Dim cnArray(9) As Variant
    Dim x As Integer
    x = 0
       
    Set cnn = CurrentProject.AccessConnection
    Set rst = New ADODB.Recordset
           
    With rst
   
        Set .ActiveConnection = cnn
            .Source = "tbl_cnDetails"
            .LockType = adLockOptimistic
            .CursorType = adOpenDynamic
            .CursorLocation = adUseServer
            .Open
           
            If .BOF Or .EOF = True Then GoTo Exit_Procedure
       
            .MoveFirst
            Do Until .EOF
               
                If .Fields("fld_cnClientID") = clientID Then
                                   
                     cnArray(0) = .Fields("fld_cnClientID")
                     cnArray(1) = .Fields("fld_cnContractStartDate")
                     cnArray(2) = .Fields("fld_cnContractRenewalDate")
                     cnArray(3) = .Fields("fld_cnSchedule1")
                     cnArray(4) = .Fields("fld_cnFee")
                     cnArray(5) = .Fields("fld_cnWetPercentage")
                     cnArray(6) = .Fields("fld_cnDryPercentage")
                     cnArray(7) = .Fields("fld_cnSpecialAgreement")
                     cnArray(8) = dteDate
                   
                End If
            .MoveNext
            Loop
            .Close
           
            .Source = "tbl_cnHistory"   ' Add contract details to cnHistory table.
            .Open
            .AddNew
            For x = 0 To 8
                .Fields(x) = cnArray(x)
            Next x
            .Update
            .Close
           
            .Source = "tbl_cnPending"   ' Delete contract from cnPending if there is one.
            .Open
            .MoveFirst
            Do Until .EOF
                If .Fields("fld_cnPendingClientID") = clientID Then
                    .Delete
                End If
                .MoveNext
            Loop
            .Close
           
            .Source = "tbl_cnDetails"   ' Delete contract from Contract Details table.
            .Open
            .MoveFirst
            Do Until .EOF
                If .Fields("fld_cnClientID") = clientID Then
                    .Delete
                End If
                .MoveNext
            Loop
           
    End With
   
Exit_Procedure:
    rst.Close
    Exit Sub

End Sub
0
chrisbarr35
Asked:
chrisbarr35
1 Solution
 
peter57rCommented:
Which line produces the error?
0
 
BitsqueezerCommented:
Hi,

beyond the question where the error occurs:

Why don't you simply use a DELETE-SQL-Command to delete the record(s)?
Why don't you simply use referential integrity to automatically delete the contract details?
Why don't you use a INSERT SELECT-Command to copy the data to the history?

The complete procedure would be a few lines only and a lot of faster.

Cheers,

Christian
0
 
Helen FeddemaCommented:
I agree with Bitsqueezer -- was about to post a similar comment.  

0
 
Patrick MatthewsCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now