Re: Loop through recordset of Crosstab query

paul has brought this to us :
Hi

Im trying to loop through an Access crosstab query in a report in
Excel using the code below, however, whenever i get to the .movefirst
part i get the error:

"Operation is not supported for this type of object"

Any ideas how i can get around this?
Thanks for any suggestions.
Paul

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset

con.Open _
"Provider=Microsoft.jet.oledb.4.0; Data Source=\
\feltapp0174\mapinfo_share\Pip\resources\pip.mdb"

rst.Open "qryLandSpendRPT", con, adOpenForwardOnly, adLockReadOnly

rownum = 5

With rst
.MoveFirst
If Not (.BOF And Not .EOF) Then
Do While Not rst.EOF

Worksheets("Summary").Cells(rownum, 1).Value = .Fields
("OpenYear")
Worksheets("Summary").Cells(rownum, 2).Value = .Fields
("Site_Name")
Worksheets("Summary").Cells(rownum, 3).Value = .Fields
("08/09")
Worksheets("Summary").Cells(rownum, 4).Value = .Fields
("09/10")
Worksheets("Summary").Cells(rownum, 5).Value = .Fields
("10/11")
Worksheets("Summary").Cells(rownum, 6).Value = .Fields
("11/12")
'Worksheets("Summary").Cells(rownum, 7).Value = .Fields
("12/13")

rownum = rownum + 1
.MoveNext
Loop
End If
End With

I suspect it is because you have a Forward Only cursor, and you are using a MoveFirst, which would be a move back. Comment out the ..MoveFirst, and I'll bet it works...


.

Relevant Pages

  • Loop through recordset of Crosstab query
    ... Excel using the code below, however, whenever i get to the .movefirst ... Dim con As New ADODB.Connection ... Dim rst As New ADODB.Recordset ... rownum = rownum + 1 ...
    (comp.databases.ms-access)
  • Re: Adding multiple subform fields to a mail item in Access
    ... To ensure the recordset pointer is at the top, you need to MoveFirst before ... the loop starts. ... > number of records in that subform changes and I need it to populate the ... > Dim rst As DAO.Recordset ...
    (microsoft.public.access.forms)
  • RE: EOF getting "You cant go to the specified record"
    ... Movefirst ... This disables the buttons when first or last record is reached thus no more ... > Dim rst As ADODB.recordset ... > Exit Sub ...
    (microsoft.public.access.formscoding)