[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Type error with MS Graph Chart Objects

Posted on 2009-05-14
5
Medium Priority
?
790 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 1000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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 just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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