Solved

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

Posted on 2013-12-03
6
776 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
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Accepted Solution

by:
baytowel earned 0 total points
Comment Utility
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
Comment Utility
Other solutions did not apply.  Found my own solution.
0
 

Author Comment

by:baytowel
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now