How do I filter an OWC Chartspace chart based on a field value.

I have a chartspace object (pivotchart) that I need to be able to filter when it initially displays so that one of the field values is pre-selected. The filter field represents months of the year. When I open the pivotchart I need it to default to the current month, but still display the other months as selectable. At present when I open the chart if shows all months (1-12) selected.

The code that displays the chart is as follows:

    'Open the form to contain PivotChart
    DoCmd.OpenForm strFName, acFormPivotChart
    Set frm1 = Forms(strFName)
   
    With frm1.ChartSpace
    ' Open PivotChart without drop areas, and set its categories and values
        .Charts(0).Type = strChartType
        .SetData chDimCategories, chDataBound, "ProjectName"

        With frm1.ChartSpace
            .SetData chDimValues, chDataBound, Array("FTE Utilisation", "FTE Forecast")
            If strView = "Simple" Then
                .DisplayFieldButtons = False
                .DisplayFieldList = False
            End If
            If strView = "Pivot" Then
                .SetData chDimFilter, chDataBound, Array("Month", "Year")
            End If
            frm1.InsideHeight = 10000
            frm1.InsideWidth = 17000
        End With
    ' Assign and format titles to axes and overall chart
        With .Charts(0)
            .Axes(1).Title.Caption = "FTE (man-days)"
            .Axes(0).Title.Caption = "Project"
            .HasTitle = True
            .HasLegend = True
            .Title.Caption = "Resource Budget - Month View"
            .Title.Font.Size = 14
        End With
   
 End With

The query that the chart is based on is shown in the code snippet.

PS The obvious answer would seem to be to put a filter in the underlying query, but this means that the only month that displays is the filter month; I need all months to be displayed (but only one selected).
PPS I am using OWC10

Tony

SELECT tblProject.ProjectID, tblProject.ProjectName, Sum(tblProjectResourceUtilisation.FTEUtilisation) AS [FTE Utilisation], Sum(tblProjectResourceUtilisation.FTEForecast) AS [FTE Forecast], tblProjectResourceUtilisation.Month, tblProjectResourceUtilisation.Year
FROM tblProject INNER JOIN tblProjectResourceUtilisation ON tblProject.ProjectID = tblProjectResourceUtilisation.ProjectID
GROUP BY tblProject.ProjectID, tblProject.ProjectName, tblProjectResourceUtilisation.Month, tblProjectResourceUtilisation.Year, tblProjectResourceUtilisation.DetailSubtotalFlag
HAVING (((tblProjectResourceUtilisation.DetailSubtotalFlag)="DETAIL"));

Select allOpen in new window