While loop hitting EOF before last record

[RadioWriter]

I'm working on building a database in Access 2013, and I'm coming across an error in a subroutine which checks to see if there are duplicate entries in the field PartNumbers (and marks a True/False field as true if an entry is a duplicate).

Public Sub CheckDupes()
    'Create variables; myNum and numCheck refer to the value in the 'PartNumbers' field
    'cur- first- and lastRec refer to record ID
    Dim RecSet As ADODB.Recordset
    Dim myNum As String
    Dim numCheck As String
    Dim curRec As Long
    Dim firstRec As Long
    Dim lastRec As Long
    Dim rst As Object
    Dim cn As ADODB.Connection
    'Set object variables; rst is only used to find the number of records
    Set RecSet = New ADODB.Recordset
    Set rst = Me.RecordsetClone
    Set cn = New ADODB.Connection
    'Open connection, open RecSet, set lastRec
    cn.Open CurrentProject.Connection
    Call RecSet.Open("tblIandP", cn, adOpenStatic, adLockOptimistic)
    rst.MoveLast
    lastRec = rst.RecordCount - 1
    'Set starting values for first- curRec to avoid skipping loops
    curRec = 3
    firstRec = 1
    'Until last record, get part num and ID
    Do While firstRec < lastRec
        myNum = RecSet.Fields.Item(1).Value
        firstRec = RecSet.Fields.Item(0).Value
        'Move to the last record so the next loop can search backwards
        RecSet.MoveLast
        'Set curRec to avoid skipping inner loop
        curRec = RecSet.Fields.Item(0).Value
        'Until firstRec, get part num and ID
        Do While curRec > firstRec + 1
            numCheck = RecSet.Fields.Item(1).Value
            curRec = RecSet.Fields.Item(0).Value
            'If part nums are same, change Duplicate? value to true
            If numCheck = myNum Then
                Call RecSet.Update("Duplicate?", True)
            End If
            'Move up the list
            RecSet.MovePrevious
        Loop
        'Move to the next record to be checked
        RecSet.MoveNext
    Loop
End Sub

I'm getting Run-Time Error '3201': "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record." This error occurs on line 25 after 2 loops (curRec = 4, firstRec = 2 - firstRec is about to be updated). The code works fine for the section it runs (records 2 and 3 are duplicates), but there are over 3.5k records. I'm not entirely sure why it's hitting EOF with the precautions I've taken to avoid skipping the inner loop. I've also tried replacing the ADODB Recordset with a regular one, and get the same results.
Thank you in advance for your help!

[jforbes]

Hey RadioWriter,

Off the top of my head, you may want to .MoveLast before rst.RecordCount as I don't think RecordCount is 100% reliable until the recordset is navigated to the end of the Recordset.

Also, how do you know RecSet.Fields.I tem(0).Value will be sequential when you are iterating through the records. If your recordset isn't ordered on this column I'm pretty sure it will break.

So I need to ask, is this Code something you inherited or something you are writing? Because if you are writing it, I think there is a less error prone way to go about this.

[RadioWriter]

Edit- Yes, this worked. Very sorry.
Thank you for the help!

[jforbes]

Glad you got it going!

You may want to take the Question Mark (?) out of your Fieldnames as it a reserved character in SQL. It may cause you trouble down the road.

Also, another approach you may want to consider for this is to run a single update query against the Records to calculate and update your Duplicates Flag. It might be less complicated and probably quicker as you are executing one statement against the database instead of multiple updates.

I mocked this up, it's not the best approach as it's using a Dlookup, but even using a DLookup I have a feeling it will run faster than the looping approach.

Private Sub Command6_Click()
    Dim sSQL As String
    sSQL = "UPDATE DuplicatesTest SET "
    sSQL = sSQL & "  Duplicate = "
    sSQL = sSQL & "    (DCount(""ID"",""DuplicatesTest"",""[ID]>"" & [ID] & "" AND [PartNumber]='"" & [PartNumber] & ""'"")>0)"
    CurrentDb.Execute sSQL
End Sub

[RadioWriter]

Well, definitely faster. (Looping clocks in around 1.5 minutes to go down the list and then back up, but the above method takes only 10 seconds.)

Unfortunately, the return is necessary to flag all of the initial entries being checked against. Another field contains prices which need to be compared to find the current/correct one. Once they've been properly updated, the excess entries will be deleted and another method will be used to change the current price/add new entries such that no more duplicates can be created.

Again, thank you SO much for your help. It is very much appreciated.