view data of recordset in immediate window

Question

I have a recordset rst with 2 columns/fields ID and Value. The recordset has multiple rows. While debugging, I am able to view records in the first row of the recordset in the immediate window using the following statements.

?rst.fields(0)
?rst.fields(1)

But I am not able to view data in 2nd or 100th row?

Solitaire

Not directly an answer to your question, but I've found DoCmd.OpenQuery "someQueryDef" to be very useful from Immediate window when I want to browse over a set of query results. – Sarmentose

Answer 1

Regarding moving through a DAO recordset and the comment by @nyarlathotep:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbljournaltitles")

Debug.Print rs(0).Name
Debug.Print rs(0)
Debug.Print rs("MyText")

rs.Move 10
Debug.Print rs(0)

rs.Move -4
Debug.Print rs(0)

''Find does not work with all recordset types
rs.FindFirst "MyText Like 'Text*'"
Debug.Print rs(0)

Adorne

Answer 2

You'd have to iterate over the rows to get their data. You could e.g. do the following simple loop:

Do While Not rst.EOF
    'Do something with the data
    rst.MoveNext
Loop

Treachery

thanks this works. but are there any commands to view a data at a particular row and column other than moving to that row. since I will have to use movenext 100 times before I can view data in 100th row. – Solitaire

Not all the data is necessarily in memory at the same time when using a recordset, so I think there is no other way than to iterate. But you could store the rows in some kind of data structure of your own, and check that data structure afterwards – Treachery

@user1075037 It is indeed possible to move through a recordset without iterating. I have posted a supplement to this answer. – Adorne

Answer 3

Leveraging answers from @Fionnualla and @codeling (and adding closing & cleanup for the recordset), also adding help from VBA: Debug.Print without newline? to make this look more like a table (still need to work on making the columns the actual width of the max size of the col).

This procedure will print out any query you drop onto it.

Public Sub debugPrintQuery(ByVal myQuery As String)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(myQuery)

' print column names
Dim i As Integer
For i = 0 To rs.Fields.Count - 2
    Debug.Print rs(i).Name & vbTab; 'print col names separated with a tab one from each other
Next i
Debug.Print rs(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline

Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 2
        Debug.Print rs(i) & vbTab; 'print values separated with a tab one from each other
    Next i
    Debug.Print rs(rs.Fields.Count - 1) 'last one without ; so it adds the newline
    rs.MoveNext
Loop

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

End Sub

Librettist