Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Type error with MS Graph Chart Objects

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 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