Dynamically set chart title from source data (1 Viewer)

mliungman · Jan 6, 2017

Hi!

I am creating a form with multiple charts from a chemical dataset. I have created all queries together with a "blueprint" chart, designed to fit my needs. Now I want to copy this chart, paste it into the form at a specific position and then change the rowsource to show the next parameter in the table. My questions:

When I use the following code

Code:

Public Sub test()
Dim mychart As Object
Set mychart = Forms!FormName.BlueprintChart.Object.Application.Chart
With mychart
    .Activate
    .Copy
    .Paste
    .Left 50
    .HasTitle = True
    .ChartTitle.Text = ""
    End With
Set mychart = Nothing
End Sub

only the title parts work, nothing else. Why is that, and how do I copy/paste and position my "blueprint" chart?

After copying the charts, I want to set the Chart Title to the name of the parameter. This data is stored in the rowsource, and also happens to be the name of the rowsource query. How to I capture it with vba? I have tried using
Code:
```
Chart.Application.DataSheet.Cells(1, x)
```
according to www(dot)utteraccess(dot)com/forum/Ms-Graph-Chart-Series-Man-t1965477.html but cannot make it work.

Please advice!

Regards, Martin

sneuberg · Jan 7, 2017

I've played with your problem in the attached database. The code in it looks like:

Code:

Private Sub Test_Click()

Dim mychart As Chart
Set mychart = Forms!FormName.BlueprintChart.Object.Application.Chart
With mychart
    .Activate
'    .Copy
'    .Paste
    .Left = 3000
    .HasTitle = True
    .ChartTitle.Text = BlueprintChart.RowSource
    End With
Set mychart = Nothing

End Sub

Note that I used early binding, i.e., Tools, References, and selected Microsoft Graph xx.0 Object Library. This allows me to declare mychart as a Chart and gives me the benefit of Intellisense. So I can tell right away that the Chart object does not have copy and paste methods. To copy objects there is the Docmd.CopyObject Method but I don't know if you can copy OLE objects with it. If you want to copy the entire form with the chart this might help. Can you tell us more about what you are doing and why?

The code above does copy the row source name to the chart title in the line:

Code:

.ChartTitle.Text = BlueprintChart.RowSource

Bedtime now so I can't play with this anymore today. Hope this helps some.

mliungman · Jan 7, 2017

Thank you for your valueable input!My goal is this: I have a table with locations and dates where several chemical substances have been sampled. The different substances are in different fields. I want to loop through all the substance fields and create a chart for each. I could probably do this with addchart, but I would rather copy an existing blueprint chart if possible, since the formatting is quite complicated, and not necessarily finished yet.
Furthermore, since the HasTitle and ChartTitle.text worked I thought my default bindnings where ok. Will check this further.

sneuberg · Jan 7, 2017

I've played a little more with this and I believe one way you could to this is to create a subform for each chart you want to display with CreateControl, use the DoCmd.CopyObject Method to make copies of your form with the BlueprintChart, modify the properties according the the database data and then assign the form to the Source Object of the subforms. A problem with this approach might be that in order to add the subform to the main form the main form needs to be in design view, i.e, if you intend on deploying your project as a .accde file (executable only file) you can't use this approach and it also may not work in a runtime environment. Would this be a problem for you?

mliungman · Jan 7, 2017

Thanks again for your input!

An update on the Title part:
I can see how your chart title changes correctly. I saw in your example database that your chart has a whole query as rowsource. My rowsource is actually a select query based on the query with the name I need to retrieve. That is, I have a field in the query that I use for linking to selected station, but that field should not appear in the chart. Consequently, using BlueprintChart.RowSource does not return a string in my case... Furthermore I cannot move the chart with your code - changing the .left variable has no effect , however I get no error (in my own code "Left method in Chart class failed"). I have activated the Microsoft Graph 16.0 Object Library.

As to the question of subforms and design mode: that would not constitute any problem since this database is meant to be a template for me to use when starting with new projects. However it seems a bit overkill to create subforms (there will be ca 15 charts/page). Maybe I should reconsider the addchart method? The most important problem, though, is that I still don't seem to be able to position charts or set their title correctly with vba.

Regards, Martin

sneuberg · Jan 7, 2017

mliungman said:
I can see how your chart title changes correctly. I saw in your example database that your chart has a whole query as rowsource. My rowsource is actually a select query based on the query with the name I need to retrieve. That is, I have a field in the query that I use for linking to selected station, but that field should not appear in the chart. Consequently, using BlueprintChart.RowSource does not return a string in my case...

Sorry, but I'm not understand any of this. Could you upload your database so that I could see how have this set up?

Furthermore I cannot move the chart with your code - changing the .left variable has no effect , however I get no error (in my own code "Left method in Chart class failed").

I know. I should have mention that. The only thing I did was put an equal sign in to get rid of the compile error. However today I found that

Code:

Me.BlueprintChart.Left = 4000

will position the chart 4000 twips from the left margin. So it appears to need to position the OLE object rather than the chart in it.

I have activated the Microsoft Graph 16.0 Object Library.

That will at least give you Intellisense if you declare the chart as a Chart, i.e.,

Code:

Dim mychart As Chart

As to the question of subforms and design mode: that would not constitute any problem since this database is meant to be a template for me to use when starting with new projects. However it seems a bit overkill to create subforms (there will be ca 15 charts/page). Maybe I should reconsider the addchart method? The most important problem, though, is that I still don't seem to be able to position charts or set their title correctly with vba.

I couldn't find an addchart method in Access. Where did you find this? Of what object is this a method?

JHB · Jan 7, 2017

mliungman said:
.. Maybe I should reconsider the addchart method? The most important problem, though, is that I still don't seem to be able to position charts or set their title correctly with vba.

Regards, Martin

I don't think you can add a chart at runtime only if the form is in design view.
Have you look at the Move Control method?
https://msdn.microsoft.com/en-us/library/office/ff837237.aspx
There shouldn't be a problem to give a chart a title at runtime, you can use the .ChartTitle.Caption = "Some text", if you know which text you want to show in the title.

mliungman · Jan 7, 2017

Hi!
Weird! I was about to explain my problems in more detail, then started reading about the Control.Move method you mentioned. I was not able to get the actual method to work, HOWEVER, when I redefined myChart to "dim myChart as Control", everything else in the original code started to work (except the copy/paste business)! I can now get the query name for the title (I can see the source data SELECT query as a string), and can move the charts around (with .left .top etc). I still cannot duplicate an existing chart object, but I am satisfied nonetheless. Thanks for the tips and if you stumble upon a way to copy/paste an access chart via vba then please let me know!
Cheers!

JHB · Jan 7, 2017

mliungman said:
.. if you stumble upon a way to copy/paste an access chart via vba then please let me know!
Cheers!

It is NOT possible to create a chart/control on a form that NOT is in design view!

sneuberg · Jan 7, 2017

JHB said:
It is NOT possible to create a chart/control on a form that NOT is in design view!

In post #6 the OP said that the design view was ok. So the question is whether there is any way to copy a chart. I'd like to know too.

JHB · Jan 7, 2017

sneuberg said:
In post #6 the OP said that the design view was ok. So the question is whether there is any way to copy a chart. I'd like to know too.

Sorry - I have not just noticed it.
Here is a quick sample. Open form "CopyFromForm" and click the button.

mliungman · Jan 8, 2017

Darn. Making it that simple makes me look really dumb...

Dynamically set chart title from source data (1 Viewer)

mliungman

New member

sneuberg

AWF VIP

Attachments

mliungman

New member

sneuberg

AWF VIP

mliungman

New member

sneuberg

AWF VIP

JHB

Have been here a while

mliungman

New member

JHB

Have been here a while

sneuberg

AWF VIP

JHB

Have been here a while

Attachments

mliungman

New member

Similar threads

Users who are viewing this thread