Accessing a report's recordset from VBA

[J_Orrell]

Morning chaps
I have another query regarding recordsets and reports in Access 2003.

I have a report linked to a query. When the report opens, I want to use some VBA behind the scenes to interrogate its recordset. I’ve done this before without difficulty by opening a report’s query as a recordset in its On Open or On Activate event, eg:

Set rst = CurrentDB.OpenRecordset(“[name of my report’s query]”)
…blah blah…
rst.Close

Trouble is, this particular report’s query takes a parameter from the user, so opening it as a Recordset fails because it needs a parameter. So basically I want to access the report’s recordset directly.

I tried to be smart with:

Set rst = CurrentDB.OpenRecordset(Me.Recordset)

...but I got:

Run-time error '2593':

This feature is not available in an MDB

Any ideas?

Thanks,

John

 

[Beetle]

When you open a parameter query from an Access object (you open the query itself, or through a form or report), Access retrieves the value entered by the user (either through a prompt or a form control) and passes that value to the Jet engine. However, when you open a parameter query in code, you are bypassing Access and going directly to Jet. Jet has no way of interpreting a value in a prompt or a form control, so you have to re-supply the values at run time. The following is an example of using code to create a record set based on a parameter query that references form controls on an open form.

Private Sub Report_Open(Cancel As Integer)
 
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
 
    Set qdf = CurrentDb.QueryDefs("YourQuery")
 
     With qdf
        .Parameters(0) = Forms!SomeForm!TextBox1
        .Parameters(1) = Forms!SomeForm!TextBox2
    End With
 
    Set rst = qdf.OpenRecordset
 
    With rst
        'do something here
    End With
 
 
End Sub

 

[Galaxiom]

How about this:
Set rst = Me.Recordset

or

Set rst = Me.Recordset.Clone

 

[HartJF]

report.recordset is only available in an ADP. What about the rest of us?