Question on accessing a recordset - only the first event works

Question

I have a question, and I don't know why this is happening. I'm trying to access a recordset and I used two different approaches, however only the first event works.

I tryed to reopen the recordset, but when I do that the recordset opens empty.

Application.StatusBar = "Connecting"
Application.Cursor = xlWait

Set db = OpenDatabase(DbLoc)

SQL = "SELECT Blister, Shelf"
SQL = SQL & "FROM LISTA_BLISTERS "
SQL = SQL & "WHERE Blister LIKE " & "'" & blister & "'" & ""
SQL = SQL & " ORDER BY 'Blister'"
 Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

' Copy to sheet

Application.StatusBar = "Loading..."
If rs.RecordCount = 0 Then
MsgBox "There is no data", vbInformation + vbOKOnly, "No Data"
GoTo SubExit
Else
rs.MoveLast
recCount = rs.RecordCount
rs.MoveFirst
End If

Do While i < rs.RecordCount
local_shelf = rs.Fields(1).Value
rs.MoveNext

i = i + 1
Loop

xlSheet.Range("H30").CopyFromRecordset rs

So if I put the "Do while..." before "xlsheet....CopyFromRecordset rs" only local_shelf gets value and nothing is written on cell "H30". In other hand, if I put the "Do while" after "xlsheet....CopyFromRecordset rs" then, only the cell "H30" gets values, local_shelf don't have any value and a error pops up: "3021 no current record". I tryed rs.open, but anything happened differently. I just want to know why this is happening, since I'm a beginner on programming VBA with Access. And a medium beginner on VBA.

Gustav Gustav · Accepted Answer · 2019-07-16T10:16:08

Don't know what you are trying to do, but this will leave only the value from the last record in local_shelf:

Do While i < rs.RecordCount
    local_shelf = rs.Fields(1).Value
    rs.MoveNext
    i = i + 1
Loop

Question on accessing a recordset - only the first event works

1 Answers