MS ACCESS VBA Run-time error '3021'; with .MoveNext

Question

this is my code

Dim Reloc, RelocPrev1, RelocPrev2 As String
Dim Blk, Blk2, Lt, Lt2, PrevDate As String
Dim LotComp, BlockComp As Integer


    Dim DB As Database
    Dim RS As Recordset

Private Sub SearchBtn_Click()

    Reloc = Me.RArea.Value

    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset(Reloc, dbOpenDynaset)

    Blk = RS!Block
    Lt = RS!Lot
    Blk2 = Me.BlockTxt
    Lt2 = Me.LotTxt

    BlockComp = StrComp(Blk, Blk2, 1)
    LotComp = StrComp(Lt, Lt2, 1)

    RS.MoveFirst

    Do Until BlockComp = LotComp
        RS.MoveNext
    Loop

   Call RetrieveData

End Sub

RS!Block and RS!Lot works fine, only RS.Movenext and RS.Edit which I tried doesn't work. My table is populated with more than 50 rows. First item in the table can be pulled just fine, I just can't move to the next row with RS.MoveNext

Any ideas to make this work?

I've not used access for some time, but I wonder if you may be reaching the end of RS? ... Try adding And Not RS.EOF to the loop condition so that it looks like: Do Until BlockComp = LotComp And Not RS.EOF. There is a nifty debugger built-in that is very useful. You can use that to step through the VBA code and inspect/print variables, if what I've mentioned does not help. — Paul T.
I tried putting a Msbox to show BlockComp and LotComp values, and it stays the same until the end and says no record found. Now I wonder why EOF doesn't work? — Richmon Santos
Provide sample data as a text table in question. As already suggested, step debug code. Why would you expect BlockComp or LotComp to change? Nothing inside loop would cause a change. What are you trying to accomplish? Not seeing RS.Edit in this procedure. — June7
Blk2 and Lt2 is a user input. so I need to stop the loop when the user input and the data from table(Blk, Lt) is matched. Edit comes next, after I located the row and pulled the data — Richmon Santos
is there any way I can locate the rows like in excel? by x and y positions? — Richmon Santos

June7 June7 · Accepted Answer · 2020-02-24T08:55:05

You apparently expect only 1 record to match criteria. It would be best to apply filter criteria to recordset SQL so only desired records are pulled then test if recordset is empty.

To go to a single record that meets criteria, instead of looping recordset use FindFirst and NoMatch.

RS.FindFirst "Block='" & Blk2 & "' AND Lot='" & Lt2 & "'"
If Not RS.NoMatch Then

MS ACCESS VBA Run-time error '3021'; with .MoveNext

1 Answers