CopyFromRecordSet not working (1 Viewer)

[twcaddell]

I have a procedure that passes a subform and creates a recordset using the subform's recordset. I then use copyfromrecordset to write the recordset to an excel file, but it does not work; however it does not cause an error and the procedure continues. I put a debug.print to see if I can access each record in the recordset with success. I then wrote two fields of each record to the excel file with success (but not very efficient for large tables). Here is my code:

 Private Sub LoadExcel(SheetNum As Integer, MainSubForm As Form, XL As ExcelClass, Optional Contract As String)
Dim rst As DAO.Recordset
Dim count As Integer
  
     'get recordset
    Set rst = MainSubForm.Form.Recordset
 
    Set XL.p_xlSht = XL.p_xlBK.Worksheets(SheetNum)
    XL.AddFirstRow rst  'class object that adds a Column Header in the first row using the fields name in the recordset
    Debug.Print rst.RecordCount
    With XL.p_xlSht
        rst.MoveFirst
        For count = 1 To rst.RecordCount
            Debug.Print count & vbTab & rst!PONum & vbTab & rst![Vendor Num]   [B]'This Works
 ' The Following worked I writing two fields to the excel file
[/B]'            .Cells(count + 1, 1) = rst!PONum
'            .Cells(count + 1, 2) = rst![Vendor Num]
            rst.MoveNext
        Next count
 
        'dump the rst to excel
        .Range("A2").CopyFromRecordset rst  [B]'does not work[/B]
        .Name = Contract
    End With
    rst.Close
    Set rst = Nothing
End Sub

I would appreciate any and all help.
Thanks
TC

 

[pbaldy]

With the code as is, you're at the end of the recordset. Have you tried without the loop above, or adding .MoveFirst before the CopyFromRecordset?

 

[twcaddell]

I did place the rst.movefirst before the copyfromrecordset to no avail. I then saved, quit access and restarted with success. Guess too much junk in the buffer that it couldn't decide what to do.
Thanks for the suggestion
TC

 

[pbaldy]

Glad you got it sorted.