[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-22
5
Medium Priority
?
465 Views
Last Modified: 2008-01-09
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
Comment
5 Comments
 
LVL 10

Author Comment

by:Christian de Bellefeuille
ID: 13854914
Anybody home!?  Am i the only one working with Office Graph's?
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13869028
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
 
LVL 2

Accepted Solution

by:
Sizey earned 1000 total points
ID: 13869061
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
 
LVL 2

Expert Comment

by:mjwills
ID: 13873187
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
 
LVL 10

Author Comment

by:Christian de Bellefeuille
ID: 14112874
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question