Solved

access 2003 vba error 1004 unable to get tick labels property of the axis class

Posted on 2013-12-03
6
823 Views
Last Modified: 2014-06-12
I have the following code:
Dim FrmGraphObj As Object
Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart

FrmGraphObj.Axes(xlValue).TickLabels.NumberFormat = "0%"

I continually receive a runtime error 1004 " unable to get tick labels property of the axis class"

if I remove this code, then I error on the following code:
Dim FrmGraphObj As Object
Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart

If FrmGraphObj.SeriesCollection(2).HasDataLabels Then

also a runtime 1004: "unable to get the seriescollection property of the chart class"
0
Comment
Question by:baytowel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 39693126
I believe that Axes and Series collection are under the Graph Object so:

Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object
0
 

Author Comment

by:baytowel
ID: 39693308
tried:
Dim FrmGraphObj As Object
Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object
   
    With FrmGraphObj.Axes(xlValue)
        .TickLabels.NumberFormat = "0%"
    End With

same error.....don't i need to identify as a graph object?
tried:
Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Graph
received error: runtime 438 "Object doesn't support this property or method"



found this on web (http://www.access-programmers.co.uk/forums/showthread.php?t=177778):
If your chart is in a form (or report), you have to:

1) refer to the form (or report) name (Form_Charts)

2) refer to the name of the object frame holding your chart (.Graph1)

3) refer to the object within the frame (.Object)

4) refer to the application that created the object (.Application)

5) refer to the actual chart itself (.Chart)

6) refer to the axes collection and select the axis you want to reference - in this case the category, or X-axis (.Axes(xlCategory))

so....
I made the assumption, that I would just replace xlCategory with xlValue for the Y-axis. So I'm back to:
Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Graph
   
    With FrmGraphObj.Axes(xlValue)
        .TickLabels.NumberFormat = "0%"
    End With

Back to the original error.....
Looked in the Microsoft Graph Visual Basic Reference and it indicated:
"Tick-mark label text for the value axis is calculated based on the MajorUnit, MinimumScale, and MaximumScale properties of the value axis. To change the tick-mark label text for the value axis, you must change the values of these properties."

so....
I reset my code to call these 2 functions prior to changing the number format.....
Public Sub txtMaxPercent_AfterUpdate()
   
    Dim FrmGraphObj As Object
    Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart
    FrmGraphObj.Axes(xlValue).MaximumScale = txtMaxPercent
End Sub

Public Sub txtMinPercent_AfterUpdate()

    Dim FrmGraphObj As Object
    Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart
    FrmGraphObj.Axes(xlValue).MinimumScale = txtMinPercent
End Sub

now I am receiving error 1004 again, this time it states "Unable to set the minimumscale property of the axis class" erroring on this line....
FrmGraphObj.Axes(xlValue).MinimumScale = txtMinPercent

debug.Print me.txtMinPercent
 0.51
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39693407
This Works for me in the Current event of a form with a reference to Microsoft Graph Object Library:
Dim chrt as Graph.Chart

Set chrt = Me("Graph1").Object
chrt.Axes(xlValue).MinimumScale = .5

Open in new window


Maybe ensure that the value is being treated as a number by using CDbl(txtMinPercent)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
baytowel earned 0 total points
ID: 39695426
I was able to fix my errors by initializing the graph on form_load to a query (qryChartInialize) that returned data, then use my actual query(qryEmpEfficiency) as the rowsource of the graph after the criteria the query uses was selected from the controls on my form
0
 

Author Closing Comment

by:baytowel
ID: 39705681
Other solutions did not apply.  Found my own solution.
0
 

Author Comment

by:baytowel
ID: 40129605
Dim FrmGraphObj As Graph.Chart
    Set FrmGraphObj = Me("gph_WeeklyEfficiency").Object

   If FrmGraphObj.SeriesCollection(1).HasDataLabels Then

returns runtime error 1004 unable to get seriescollection of the chart class
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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