PivotChart View Bugs!!!

[Athaydes]

Hi, I have a few queries that I use to display data as graphs (pivotchart
view). I inserted all the graphs in a Form through Subforms (using the
queries directly as the source objects of the Subforms), so that each Subform
displays one query, always in pivotchart view.
My problem is that the colors of the graphs keep changing, and even worse,
the type of the graphs change.... two of my graphs are best viewed as pie
charts, but I save them as pie charts, and they just switch back to bar
charts when I open the Form again, and this makes the graphs look very
awkward.
I will appreciate any advice!!!
I am using Access 2003 under Windows XP. There's some simple VBA code behind
the Form, so that the time scales of the charts can be changed (with 'date'
text boxes in the Form) by the user (and on the Load event, automatically
dates are set to between current date and one year ago). The graphs
apparently started changing after I introduced this code, but the color
problem was happening before that.

 

[Athaydes]

I will answer my own question.... after doing some research, I found that
lots of people also got into trouble by working with pivocharts.
Most decide to export the data and use an Excel chart.
That wouldn't be a very elegant solution for me. So, what I did was:
Instead of importing the queries in pivotchart view directly into my form, I
left them in their nice datasheet view (just by doing it I got rid of all
bugs like the window for the query opening at the very bottom of the screen
and in a very small size) and created a form for each query, each with one
query as its record source, and then imported all forms as sub-forms into my
main form, where all the subforms are displayed as pivotcharts. This way, I
was able to get around the problem, and all my formats remain unchanged.

The problem with my code was that I was trying to set the time scales for
the queries on the load event of my form, and this apparently was altering
the graph properties. I deleted that part of the code, and instead set the
default values of the text boxes (from which the queries get the time scale)
to the values:
Date()
=DateAdd("yyyy",-1,Date())
That is, between the current date, and one year ago. Then, if the user
wants, he/she can change that.
All working perfectly. I hope this information will help other people
besides myself!
Cheers,
Athaydes

 

[ac512]

Hi Athaydes

I was reading through your post, and thought you may be able to help me?
Using Access 2000, I am trying to alter the y(value) axis of my chart using
some user input (I tried to write some VB that enabled me to do this but have
not had any success doing so). Are you able to provide an example of your VB
code that alters the x axis time scale?
Any assistance would be greatly appreciated.

Thanks in advance
Kind regards
AC

 

[Athaydes]

ac512,

I didn't use much code really to change the 'x' scale of my chart.
I actually used a parameter query to source the data...
I typed the following in the "criteria" for the 'date' field in my query:

Between [Forms]![frmStats]![Date3] And [Forms]![frmStats]![Date4]

As you will understand, my form is called frmStats, and the text boxes Date3
and Date4 in this form are used to inform the query the dates.
Date3 and Date4 must be unbound and you should have a default value set in
order to initialize the values for the query to work. In my case I want the
user to see one year worth of data on the chart, so I used the following
default value for Date3
=DateAdd("yyyy",-1,Date())
and just
=Date()
for Date4.

All the code does for me is to requery the subforms in case the user updates
any of the dates, like this:

Private Sub Date3_AfterUpdate()
Me.SubFrmOccurrences.Requery
end sub

and also in the form load event to initialize the charts when the form is
started.

The only complication is that in order to avoid the bugs mentioned before, I
had to structure my forms in a pretty complicated way:

Charts are created in a subform which uses the parameter query as its record
source, then the subform is displayed in a form which the user uses to see
the charts and to manipulate the dates.

 

[ac512]

Thanks for the information Athaydes

Kind regards
AC

ac512,

I didn't use much code really to change the 'x' scale of my chart.
I actually used a parameter query to source the data...
I typed the following in the "criteria" for the 'date' field in my query:

Between [Forms]![frmStats]![Date3] And [Forms]![frmStats]![Date4]

As you will understand, my form is called frmStats, and the text boxes Date3
and Date4 in this form are used to inform the query the dates.
Date3 and Date4 must be unbound and you should have a default value set in
order to initialize the values for the query to work. In my case I want the
user to see one year worth of data on the chart, so I used the following
default value for Date3
=DateAdd("yyyy",-1,Date())
and just
=Date()
for Date4.

All the code does for me is to requery the subforms in case the user updates
any of the dates, like this:

Private Sub Date3_AfterUpdate()
Me.SubFrmOccurrences.Requery
end sub

and also in the form load event to initialize the charts when the form is
started.

The only complication is that in order to avoid the bugs mentioned before, I
had to structure my forms in a pretty complicated way:

Charts are created in a subform which uses the parameter query as its record
source, then the subform is displayed in a form which the user uses to see
the charts and to manipulate the dates.

Hi Athaydes

I was reading through your post, and thought you may be able to help me?
Using Access 2000, I am trying to alter the y(value) axis of my chart using
some user input (I tried to write some VB that enabled me to do this but have
not had any success doing so). Are you able to provide an example of your VB
code that alters the x axis time scale?
Any assistance would be greatly appreciated.

Thanks in advance
Kind regards
AC

 

[Atif]

I am creating several Pivot Chart from Queries (PivotChart view of the Query).

Problem is I want to Export/Copy those Charts to PowerPoint Slide, where as
i am able to export/copy data not the Chart, is there any way that we can
copy PivotChart in PowerPoint as we do same with Excel Pivot Charts.

Thanks

 

[Guest]

I am creating several Pivot Chart from Queries (PivotChart view of the
Query).

Problem is I want to Export/Copy those Charts to PowerPoint Slide, where
as
i am able to export/copy data not the Chart, is there any way that we can
copy PivotChart in PowerPoint as we do same with Excel Pivot Charts.

Thanks

 

[Atif]

Hi

One solution which i have found for Access 2007 is to Export Pivot Chart in
PDF format (External Data menu), and then copy (copy file to Clipboard under
Edit menu in Acrobat Reader) and Past in PowerPoint slide.

anyother suggestions are welcome

Atif