Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Communicating between Pivot Charts

    In Access 2007 I have created a (parent) form which contains four subforms. The subforms were created using the Pivot Chart wizard. I need to communicate a change in the filter field (this is the field at the top left on the chart) to the other pivot charts (subforms) on the (parent) form.

    The filter field on each pivot chart is a dropdown of dates. If a different date is selected on one pivot chart I want the filter field on all the pivot charts to be changed automatically. The "On" events do not appear to fire when a selection is made from the filter dropdown list.

    I have looked throught the Access 2003 reference book I have and "The Microsoft Office Web Components Black book with .NET" book for help. Neither offers any help on this subject.

    Is it possible to reference and set the filter field in VBA code and, if so, how is it done?

  2. #2
    Join Date
    Jun 2007
    Posts
    33
    check this post:
    http://www.dbforums.com/showthread.php?t=1624128

    the idea is to have the filter field in some form and
    on the query of the pivot chart form:
    where MyDate = iif(isnull(Forms!FilterForm!FilterDate), Date(), Forms!FilterForm!FilterDate)
    then, on change event of filter date just requery the pivot chart

    hope it helps

  3. #3
    Join Date
    Jan 2008
    Posts
    2
    The date dropdown list was generated by the PivotChart wizard and is part of the chart itself. This must have been done by the wizard because the query used for the PivotChart did not limit the data to a specific day. It was during the design portion of the wizard process that I moved the date field to the filter area of the chart.

    It is your suggestion that I put criteria on the query used for the PivotCharts that will allow me to specify a date value for the query. I then put a control on a form, separate from the chart/form itself, that contains the dropdown list of dates available to the query. The OnChange event for the control would force a requery for each of the forms which contain PivotCharts.

    If this works it is a brilliant solution. I was hoping to be able to programmatically access the filter control on the PivotChart itself, thus eliminating the need to recreate the PivotCharts using your solution. However, I have not gotten any response from any of my resources that have said it was possible to do that way.

    Thank you for your response.

  4. #4
    Join Date
    Jun 2007
    Posts
    33
    Quote Originally Posted by RandyHarding
    It is your suggestion that I put criteria on the query used for the PivotCharts that will allow me to specify a date value for the query. I then put a control on a form, separate from the chart/form itself, that contains the dropdown list of dates available to the query. The OnChange event for the control would force a requery for each of the forms which contain PivotCharts.
    Exactly... but remember not to let null values in the creteria of the pivotchart query or you will loose the pretended format.
    The credits are to SimonMT.
    I was also trying to doit by vba but if so the pivotchart will loose the right format (names of series values)...


    But i dont know if is availebal on Access 2007 new features regarding to pivotchart's...
    Last edited by PedroF; 01-15-08 at 12:16.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •