Solved

Type error with MS Graph Chart Objects

Posted on 2009-05-14
5
773 Views
Last Modified: 2013-11-27

Hi,

I am trying to run a vba chart code example  for MS Access I found by a.p.r. pillai.  I get a "type error" at grphChart.Activate or .chartType = xlColumnClustered.   (see relevant portion of code below).  I feel like I have tried everything, but am very confused about how MS Graph Chart works as an object in Access...  Any enlightenment as to where I am going wrong would be greatly appreciated.  

The full example cam be found at:  www.msaccesstips.com/2008/07/column-chart-and-vba.shtml.

Thanks!

Public Function ColumnChart(ByVal ReportName As String, _
ByVal ChartObjectName As String)
'---------------------------------------------------
'Author : a.p.r. pillai
'Date   : June-2008
'URL    : http://www.msaccesstips.com
'Customized Source Code : from Microsoft Access Help
'---------------------------------------------------
Dim Rpt As Report, grphChart As Object
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As Integer
 
lngType = 
 
DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)
 
Set grphChart = Rpt(ChartObjectName)
 
grphChart.RowSourceType = "Table/Query"
recSource = grphChart.RowSource
 
If Len(recSource) = 0 Then
   MsgBox "RowSource value not set, aborted."
   Exit Function
End If
 
'get number of columns in chart table/Query
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
rst.Close
 
With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 300
    .Top = 250
    .Width = 5000
    .Height = 4000
End With
 
grphChart.Activate
 
With grphChart
     .chartType = xlColumnClustered
     .RightAngleAxes = True
     .AutoScaling = True
     .HasLegend = True
     .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = chartType(typ) & " Chart."
    .HasDataTable = True
    .ApplyDataLabels xlDataLabelsShowValue
End With

Open in new window

0
Comment
Question by:klwAtMich
[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
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24392612
As far as I can tell, these are all charts that can be created with the Access Chart Wizard *without* using VBA code.

If you need help creating the basci chart using the Access chart wizard, let me know, and post a sample of your data and I will help you through it.

On the other hand, if you need help with this specific code, I suggest you contact the author who wrote it:
aprpillai@msaccesstips.com

JeffCoachman
0
 

Author Comment

by:klwAtMich
ID: 24394988
Thanks, boaq2000,

I know I can create these graphs by hand.  I am just trying to figure out how on earth one WOULD edit a chart using VBA,  so I can figure out how to edit that chart of mine.  I did contact the author, but who knows how long it will take to get a response, and I need to finish this project today.  

I figured someone here must know how to edit charts with VBA. I see that you are strongly opposed to the idea, but I really don't see how my labels are going to work otherwise.

It looks like I may just have to go back to cutting and pasting the query into the Excel sheet each time, because I've just spent 2 days on this and seem to be getting nowhere...  

Thanks for trying to help.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 24399279
Make sure you set a reference to the MSGraph Library in your VBA editor:

Open the VBA editor and click:
Tools-->References

Jeff
0
 

Accepted Solution

by:
klwAtMich earned 0 total points
ID: 24825611
As per my request, the author posted a sample database.  It can be downloaded at:
www.msaccesstips.com/downloads/2008/06/workingwith_chartobject.shtml

This allowed me to see where I was going wrong.  boaq2000 was on to something, although the problem was not the MSGraph Library, but a set of additional libraries that must be turned on with it (and some that I had on that conflicted with others that needed to be on).  I am not sure which exact ones are essential here, but the set in the attached image seem to do the trick to allow one to edit MSChart Objects in VBA.


ReferenceLibraries.jpg
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24859296
Ok
Glad I was able to help
;-)
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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