I Neep Help Passing ADO Recordset

[Suidae]

I've spent the past two hours reading and I'm still lacking the lightbulb coming on.

I have a function that opens a recordset. I want to be able to use this same recordset in another function. Can someone explain this to the village idiot please.

S-L-O-W-L-Y

Thanks!

[James Stanich]

You could load all the recordset values into an global array then use the array the poulate the next form...

Are you familiar with arrays?

How are you retrieving your data ? Post Code...

[Kunchesm]

declare the recordset globally so that u can use the same recordset in any function...

[NateBrei]

If you're using ADO Objects in code, try putting your Connection and/or Recordset declaration as a public declaration in a module. At that point, you can set all your properties and open the connection and recordset in one form and you should be able to reference that same recordset in other form(s) or module(s).

If you are using an ADO Data Control on a form, & you want to use that same recordset on another form, try putting another ADO Data Control on the second form & before you show that second form use something like...

adodcForm2.Recordset = adodcForm1.Recordset

Now you should have access to the same data/recordset from each form.

Hope this helps.
Nate

[Suidae]

Originally posted by James Stanich

How are you retrieving your data ? Post Code...

I'm just displaying the query in a grid so the user can review the data. I want a second function that will use the recordset that has been created and export the data to an Excel Workbook. I have both functions working and they work just fine. I just can't figure out how to pass the recordset from LoadGrid to CreateExcel. These functions reside on the same form.

I've tried declaring the recordset globally but I kept getting an error is was closed. (The code did not close it)

This isn't the actual code but you will get the general idea.

Private Sub LoadGrid()
    Dim rs As New ADODB.Recordset
    Dim sql As String
    
    sql = "Select * from tbl123"
    
    rs.Open sql, cnn, adOpenKeyset, adLockReadOnly, adCmdText
        
        'display results to user in grid
        Set grid1.DataSource = rs1
        grid1.Refresh
    
End Sub

Private Sub CreateExcel()
   'Dims

   With objWorkSheets
        .Application.ScreenUpdating = False
        .Cells(17, 1).CopyFromRecordset rs '<---Recordset
        .Cells.Select
        .Cells.EntireColumn.AutoFit
        .Cells(17, 1).Select
    End With

[techgnome]

[B]Dim rs As New ADODB.Recordset[/B]
'Move this out of the sub, this way it can be module level
 
 
Private Sub LoadGrid()
    Dim sql As String
    
    sql = "Select * from tbl123"
    
    rs.Open sql, cnn, adOpenKeyset, adLockReadOnly, adCmdText
 
[B]       'disconnect the recordset -- this prevents the recordset from "closing" if the connection is dropped.
       set rs.ActiveConnection = nothing
[/B]        
        'display results to user in grid
        Set grid1.DataSource = rs1
        grid1.Refresh
    
End Sub
 
Private Sub CreateExcel()
   'Dims
 
   With objWorkSheets
        .Application.ScreenUpdating = False
        .Cells(17, 1).CopyFromRecordset rs '<---Recordset
        .Cells.Select
        .Cells.EntireColumn.AutoFit
        .Cells(17, 1).Select
    End With