Solved

Type error with MS Graph Chart Objects

Posted on 2009-05-14
5
764 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
  • 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

707 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

15 Experts available now in Live!

Get 1:1 Help Now