Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-12-03
6
Medium Priority
?
842 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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