I have a pivot table in Access 2007 and I am trying to create a drill down form that opens when a user double-clicks on a total in the pivot table. The code that runs when a user double-clicks is:

    Dim pvtAgg As OWC10.PivotAggregate
    Dim rst As ADODB.Recordset
    Set pvtAgg = sel.Item(0)
    Set mfrmPopup = New Form_frmReportBioMassBalancePopUp
    Set rst = pvtAgg.Cell.Data.CurrentCell.Recordset
    '   The recordset returned is disconnected and needs the active connection set before it can be assigned to a form
    rst.ActiveConnection = CurrentProject.BaseConnectionString
    Set mfrmPopup.Recordset = rst
    mfrmPopup.Visible = True
I get an error message of:

The object you entered is not a valid Recordset property
If I inspect the recorset object i can see that the recordset has records and fields that matches the drill down total.

If I use the command:

Set rst = pvtAgg.Cell.Data.Recordset
and attach the recordset object
to the form this works perfectly. However this recordset is the pivot table data and not the drill down data the user has clicked on.

As the recordset needs an active connection when you attach the recordset to the form I have also tried adding the code:

rst.ActiveConnection = CurrentProject.BaseConnectionString
to the recordset object, but I still get the same error message.

I am using an MS Access 2007 access data project connected to a SQL Server database.

Does anyone have a suggestion as to how to fix this?