Displaying a Saved PivotTable from Code

[afh110]

Hi guys,

Im stuck and im sure you guys can make the difference

In the code below im trying to pass a parameter (Master_Key) to my saved query (BSC_View) and call it into my subform. I know how to pass a parameter to a saved query but when i need to set the query or query string (after i passed the parameter) to the "SourceObject" of my Subform my code gives me an error. Not sure how to do that. My code is below. Mind you the following facts:

Application :Access 2007
OS: Windows XP

[VBA]
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim db As DAO.Database
Dim rs As DAO.recordset
Dim strResult As String
Dim StrSQL As String
Dim qdf As QueryDef
Dim rst As recordset
Dim BSC_Master_Key As String
Set db = CurrentDb

BSC_Master_Key = Forms!Data_Management!DD_BSCDef_BSCRecord
Set qdf = db.QueryDefs("BSC_View")
qdf.Parameters(0) = BSC_Master_Key
StrSQL = qdf.sql
Forms!BSC_View!DS_BSC_View.SourceObject = "Query.BSC_View" '<<<<<----Need to set SourceObject to "BSC_VIEW" query with its set parameter!!
'rst.Close
qdf.Close

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub

[OBP]

Shouldn't you be using the code from your previous Thread?
Set NewQry = db.CreateQueryDef("Qry", "SELECT Activity_Log.Activity_Type AS ['Type'], SH_Def.First_Name+' '+SH_Def.Last_Name AS ['Shareholder'], Activity_Log.Planned_Date AS ['Scheduled Date'], Activity_Log.Actual_Date AS ['Actual Date'], Activity_Log.Log_Date AS ['Log Date'] FROM Activity_Log INNER JOIN SH_Def ON Activity_Log.SH_Key = SH_Def.StrKey WHERE (Activity_Log.Creation_User='" + User + "');")


Other than that you could just put the Parameter directly in the Query (not using VBA) using Forms![formname]![fieldname], place it in both the criteria row and the Parameter.

[afh110]

Hi OBP

The reason i dont want to use a "CreateQueryDef()" because I want to use my saved query. The saved query has a pre-formatted Pivot Table which im trying to display in the subform. I thought it would be easier to call on a saved query than to create one then turn it into a pivot table and format it all of this in Code!

Unless there is no other way, then i would ask you how to do that :)!

[OBP]

Use the second option that I suggested of using the filter directly in the Query, it does not use VBA to pass the Filter or SQL or QueryDef.

[afh110]

ok now im simply calling a query with the preloaded parameters.Im not even calling the saved query ( is that what you ment?) the problem is that the data returned is displayed in datasheet layout in the subform! I need to show it in a pivot table layout and further do some formatting on the Pivot table to make it look good. Can you direct me to that? Thanks OBP