Select * from temp table selects only 1 record

[Margarita]

Hello,
I am banging my head against the wall with this problem. BEGGING for your help!
I am working in vba, access 2003. I need to run a parameter query on a loop with a new parameter at each iteration of the loop, store the result of each iteration in a temporary table called CumulativePS, and then paste the entire contents of the table in an excel workbook, wb. At the end of the loop, the CumulativePS table should have 35 records (checked this- it works). When I run the simple line 'Select * from CumulativePS;' from a stored query, it gives me the right results. HOWEVER, when I do the following code, only one record (it's the last added record every time) gets selected and pasted into excel:

'PASTE into cumulative sheet:
'-------
' Tried putting in a sql string directly in vb, but didn't work:
'Dim sql_cum_paste As String
'sql_cum_paste = "Select * from CumulativePS;"
'---------
Dim rs_cum_paste_qdf As QueryDef
Set rs_cum_paste_qdf = dbs.QueryDefs!CumulativePS_selectall
Dim rs_cum_paste As DAO.Recordset
Set rs_cum_paste = rs_cum_paste_qdf.OpenRecordset
'debugger prints '1':
Debug.Print rs_cum_paste.RecordCount
 
Dim lastrowcumreg As Long
Dim cum_reg_recordcount As Long
If Not (rs_cum_paste.EOF = True And rs_cum_paste.BOF = True) Then
rs_cum_paste.MoveLast
cum_reg_recordcount = rs_cum_paste.RecordCount
lastrowcumreg = header_cumreg + cum_reg_recordcount + 1
wb.Sheets("CumulativeSummary").Range(namecol & header_cumreg + 1).CopyFromRecordset rs_cum_paste
Else
GoTo exitcumreg
End If

Where am I going wrong? Is this my error, or does it seem like a sign of some kind of corruption? Incidentally, I should say that this 'select from table and paste into an excel sheet' method is used multiple times in this same module, and it works for all other sheets- the right records get selected and pasted. But for some reason, it is throwing this at me at this point.
Please let me know if pasting more code here would be helpful in figuring out what the problem is. I appreciate all help in advance!

 

[pbaldy]

It's a shot in the dark, but have you tried moving the recordset back to the first record after the MoveLast (which you do need to get an accurate record count)?

 

[Margarita]

I should add that the row number of lastrowcumreg corresponds to the right formula that it would have been if all 35 records were inserted:
lastrowcumreg = header_cumreg + cum_reg_recordcount + 1

The sums (which I put in after the records are pasted) are pasted in row 42, which is exactly header_cumreg+ 35+1

So it's as if the records are pasted as blanks, instead of not being pasted at all. Is this possible? Or am I going nuts?
Thanks!!

 

[Margarita]

It's a shot in the dark, but have you tried moving the recordset back to the first record after the MoveLast (which you do need to get an accurate record count)?

Paul, thank you so much! I tried doing just that after reading your reply and it worked like a charm! You are officially my favorite forum member!!
Just out of curiosity- why would it matter if the recordset was at the first or the last position in this instance? The recordset isn't pasted record-by-record on a loop, but by using CopyFromRecordset- doesn't it just grab the whole recordset and paste?
Anyway- that question is just for informational purposes.
Thanks again!!!

 

[pbaldy]

It was just an educated guess to be honest. My assumption is that since the recordset was on the last record, it only copied from that point. You would think it would copy the entire recordset, regardless of cursor position. Perhaps it works like it does to give you the flexibility of copying less than a full recordset.

 

[Margarita]

It was just an educated guess to be honest. My assumption is that since the recordset was on the last record, it only copied from that point. You would think it would copy the entire recordset, regardless of cursor position. Perhaps it works like it does to give you the flexibility of copying less than a full recordset.

I will definitely do more searching on this to understand CopyfromRecordset better. Thank you, Paul!

 

[pbaldy]

Happy to help!