Print Chart From Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have a question relating to forms & charts.

I have a chart on a form which is linked to the forms record source via the
“Link Child Fields†& “Link Master Fieldsâ€. This works fine when moving
through the records on the form with the charts data staying synchronised.

This is not the case when I go to print the form & relevant records. In
Print Preview the first record appears as it should but after that the chart
goes blank for the remaining records. I believe this is due to the “Linked
Field†not being updated in this mode. Has anyone come across this before &
found a successful work around? I would appreciate any information, help or
direction.

I need the chart on a form as I have investigated putting it on a report
but you have limited control over the chart on a report. It is interesting &
frustrating that the same properties of a chart exposed on a form does not
carry through for a report.

* I have found a work around by filtering the current record bound to the
form & calling Print Preview. The only problem is I can't seem to tell when
the form has been printed or the user has closed the print preview & returned
to the form. This is a problem as the form is filtered & I need to remove
this filter & move back to the printed record & to allow the user to move
through all the records.

** I know I should not print from a form but I can’t access the charts
properties on a report, if anyone has any suggestions it would be appreciated.


Cheers,
Adam.
 
I'm not sure what properties of the chart you can access from a form that
you can't access from a report.
 
Hi Duane,

Thanks for replying. Basically I need to change the following properties;
* Rowsource of Chart
* Title, Xaxis & Yaxis labels
* Linked child & master fields
* Chart type

An example of how I am hoping to use charts is as follows;
Pulldown menu on form to select data type for chart ie Sales by month by
Sales Rep. Then if the user wants to see Sales by week by Sales Rep I have to
change the data source, labels etc. Also there will be over 50 different
combinations of charts, so I don't want to create one for each type, ie 50
reports with charts on them.

I have had trouble changing data sources for charts on reports & linking
child master fields and I have basically given up with reports. All the posts
I have found say that charts on reports have significantly reduced amount of
exposed properties through VBA. If you can point me in the right direction
with charts & reports it would be appreciated or if you have a solution with
forms would be great.

Cheers,
Adam.
 
Considering your statement "there will be over 50 different
combinations....", I would create one or more "master queries" in Access
that would combine tables, create calculated columns, provided user-friendly
aliases, etc. Then train your users on how to use pivot charts/tables in
Excel.
 
Hi Adam,

Don't give up on it - it's almost certainly going to be easier than
educating your users, as Duane suggests ;-)

You can easily set recordsource and link master/child fields through VBA.
The less commonly used bit is setting your chart properties. But it can be
done! From chart type to dotted major y-axis gridlines!!!

You can refer to the properties of a chart in a bound object control on a
form or report, using the following syntax:
me.oleObjectName.Object.chartobjectname.propertyname

For example, you could use
me.oleObjectName.Object.Axes(2).MaximumScale = 50
to programmatically set the maximum of the value ("y") axis to 50.

The MSDN library contains a wealth of information on the various objects,
methods and properties. Try
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/mschrt/html/vbobjaxisobject.asp
(watch for splitting of this URL across lines) as a starting point. This
may also be available in the Object Browser when you've got a chart in your
application.

HTH,

Rob
 
My only concern with setting the properties is with the Row Source. If I
recall correctly, the Row Source property is similar to the Record Source
property of a subreport which is difficult to change once the report has
opened.

I work with lots of number crunchers and once a basic pivot table has been
created, they can easily manipulate the fields and filtering. It takes much
less time than writing code to modify chart properties. It seems that once I
get a report or chart as requested in Access, they change their mind or want
something a little different. Teach them a couple basics about Excel Pivot
charts and tables and they can slice and dice to their hearts content.
 
Thanks for replying Rob, I know it can be done but I think it is going to
take quite a bit of work. Just to make things interesting the total has
increased to 60+.

I have got the charts to work on a form & they seem to be previewing OK. My
only problem now is that I cannot change the Columncount property of the
chart. I am using similair syntax as you mentioned in your post
"Me.OLEObjectName.ColumnCount = 3" but I get" error 2448 can't assign a value
to this object". Any ideas? the documentation mentions that it is read/write.

Anyway I am going to push on, thanks again.

Adam.
 
Thanks for replying Duane, in some ways you are right about using Excel to
produce the charts would have its advantages. Funny you should mention Pivot
tables, when I was talking to the client they mentioned Excel & how he did
not know or want to know how to use them. Also I just spent 12 months
developing educational software with Excel as a frontend, I did mention using
Excel to some extent but this was not taken on board.

Thanks,
Adam.
 
You've missed the .Object bit from the syntax I posted. Try:
Me.OLEObjectName.Object.ColumnCount = 3

Other than that, I can't tell what your problem is ...

I haven't exhaustively tested all the Chart methods and properties using the
syntax I posted, but the several things I have tried it on (mainly axis
scaling and labelling) have all worked as I expected.

HTH,

Rob
 
Hi Rob,

I really don't know what I am doing wrong.

Firstly when I am in design mode on the form & click on the chart I can
manually change the Colomncount value. At run time I get the following errors;

Me.OLEObjectName.Object.ColumnCount = 3 Give Error: 438 "Object doesn't
support this property or method

Me.OLEObjectName.ColumnCount = 3 Gives Error: 2448 "You can't assign a value
to this object"


* When I refer to the charts visibility, rowsource etc I use the
following(no errors);
Me.OLEObjectName.RowSource =""

* When I refer to the charts type, title etc I use the follwoing(no errors);
Public objMainChart As Graph.Chart
objMainChart..HasTitle = True

* I also have a reference to "Microsoft Graph 11.0 Ojbect Library", could
this be causing issues?

Would it posible to get an example of how you change the number of columns
of data graphed in code or sample file? I really need to be able change the
amount of data being viewed.

Thanks for all your help.

Cheers,
Adam.
 
Back
Top