• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

How to get the "Chart" object in an Access Form from VBA (Without using ActiveChart)

I'm creating a program with RSView32.  It allow me to create some code with VBA.
I've created an Access file to be able to produce my reports.  I have to change the
Minimum and Maximum value for the Y Axis of my chart with my code.

I can't figure out how i can reach my Axis.  I'm stuck because i can't get this Chart
object.

    Dim AppAccess As New Access.Application
    Dim f As Form
    Dim chart as Graph.Chart
    Dim o As Object

    AppAccess.OpenCurrentDatabase "C:\LOREAL\ReportingEngine.mdb"
    AppAccess.DoCmd.OpenForm ("TREND_FT_PEROX")
    Set f = AppAccess.Forms("TREND_FT_PEROX")
    Set chart = f.Controls(0)   ' I know that my chart is at index 0.  But this cause an error.
    Set o = f.Controls(0) ' This doesn't cause any error.

It keep telling me "Can't find project or library".

As soon as i get that object, i'll be able to reach the Axis and Scaling of my axes and i'll be able to change my minimum and maximum value.  But please, keep in mind that i can't use "ActiveChart"... i must open my Access file from my VB code, then open my form, then access my chart, then access my axis, then my minimum and maximum properties.

Thanks for your help.
0
Christian de Bellefeuille
Asked:
Christian de Bellefeuille
1 Solution
 
Christian de BellefeuilleProgrammerAuthor Commented:
Anybody home!?  Am i the only one working with Office Graph's?
0
 
anthonywjones66Commented:
I sounds like the library that defines the object Graph.Chart isn't referenced in the VBA project.

If you have set this reference you could also try:-

Set chart = f.Controls(0).object

Anthony.
0
 
SizeyCommented:
The message "cannot find project or library" means that the Graph Library (which can be found in Tools->References, probably as "Microsoft Graph X.X Object library") is not compatible with the charts that Access uses.

Looking at the definition within the controls collection (in Access 2003) I can see that the type is actually defined as MSGraph.Chart.8 , but this is not available in the code editor or references list.


A simple solution is available however, and that is to reference the object via the form.  Assuming the chart is called Graph0, this code should work for you:

   Dim AppAccess As New Access.Application
    Dim f As Form

    AppAccess.OpenCurrentDatabase "C:\LOREAL\ReportingEngine.mdb"
    AppAccess.DoCmd.OpenForm ("TREND_FT_PEROX")
    Set f = AppAccess.Forms("TREND_FT_PEROX")
    f.Graph0.BackColor = vbRed

Note that as soon as you type the . after the control name (graph0) the list of properties/methods is shown.

Assuming that you want to perform a few tasks with the graph, that last line would be better using a With block, eg:

    With f.Graph0
      .BackColor = vbRed
      .Left = 0
    End With
0
 
mjwillsCommented:
Change:

    Set chart = f.Controls(0)   ' I know that my chart is at index 0.  But this cause an error.
    Set o = f.Controls(0) ' This doesn't cause any error.

to:

    Set o = f.Controls(0) ' This doesn't cause any error.
    Msgbox TypeName(o)

and tell us what happens.
0
 
Christian de BellefeuilleProgrammerAuthor Commented:
Sorry for not answering for so long.

i had to do that code quite fast (that's the reason why i gave 500 pts for this question).  But since i had to finish that fast, i had to use a less flexible solution.  I had to give the points to somebody... so here some points for you Sizey.

Anthony: Could have been that, but it is already referenced.

Sizey: Not working anyway.  I've not tried that under Visual Basic, but i can tell you that with RSView32 VBA, it doesn't work.  When i type "f." i don't see anything like "GraphX"...  i see Application, Form, Parent, Report.  When i type ".form." i don't see anything like "BackColor".    I also tried to declare F as Access.Form but it's the same.

I'll try later to see if in VB6 it does the same thing... maybe it's just a problem of RSView32 VBA.  It wouldn't be the first time!!  

Thanks to all of you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now