MS Access 2007 VBA for QueryDef in PivotChart view


  • I have searched through a lot of code to create PivotCharts using OWC and to even work with the ChartSpace of a form to some degree... I have not been able to find anything on working with the MS Access 2007 Query when opened in PivotChart view. It appears to be using some form of OWC that is built in maybe version 11 or 12.0 ? Anyway in my mind I believe I should be able to write SQL to a querydef then open that querydef in PivotChart view (All very easy) but then why am I not able to reference the objects in that querydef PivotChart view?

    Like this:

    Private Sub Example()

        Dim daoDBS As dao.Database
        Dim daoQDF As dao.QueryDef
        Set daoDBS = CodeDb
        Set daoQDF = daoDBS.CreateQueryDef("MyNewQueryDef")
        With daoQDF
            .SQL = "SELECT This, That, TheOther FROM AnyOldTable;"
        End With
        Set daoQDF = Nothing
        Set daoDBS = Nothing

        DoCmd.OpenQuery "MyNewQueryDef", acViewPivotChart, acEdit

    End Sub

    That example will create a new querydef in Access 2007 and set the SQL to select three columns from a table then open that same querydef in PivotChart view. At this point we have everything we need. If I want to manually customize the view by dropping and dragging data fields into "Drop Zones" etc.. etc... I am looking for some example code to then turn around and reference that new QueryDef and customize that new PivotChart view by placing Column This & Column That in the data area as well as place the Month-Day & Week-Day version of date Column TheOther down at the bottom for the Category fields. As well as the other OWC basics like showing the legend, moving the legend, adding filter data fields etc... I think once I know how to reference the PivotChart view itself that the new QueryDef is presenting I can then figure out most of the other stuff from all the OWC code on the internet which it all appears to be geared for working with Forms not QueryDefs. Still struggling with the "adding multiple data elements" inside the data area but that is beside the point... Any help would be great! Obviously we should not have to create forms and create OWC chartspaces on them when Access 2007 already has the ability to open a querydef in PivotChart view. We just need the ability to then customize it programmatically using VBA. Thanks to all!

    Thursday, April 08, 2010 5:40 PM