Solved

Ad Hoc Charts in Access

Posted on 1998-08-14
3
170 Views
Last Modified: 2006-11-17
I'm supposed to generate reports and charts based on a variety of factors selected from a form. Kind of like a report/chart wizard. I think I can do the reports, but not the charts. Do I use MS Graph or the MSChart ActiveX control? Can it be generated/modified dynamically with VBA?
0
Comment
Question by:dwbrown
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
ramrom earned 100 total points
Comment Utility
MS Graph is pretty versatile. If you have Office 97 Professional, there is a help file on the CD:\valupack\morehelp\vbagrp8.hlp. It isn't a vrey good help file. Here is some sample code I'm using to drive it (from FoxPro), but the translation to VBA is straightforward:

set gr = me!graph.graph.application '  where graph is the control name.
with gr
  with .datasheet ' holds the axis labels, legend and data
    .range("B:Z").delete   ' remove initial set of data
    .range("2:12").delete
  end with
  with .chart
    .charttype = xlBarClustered
    .hastitle = .t.
    with .charttitle
      .font.size = 12
      .font.bold = .f.
      .text = rpt_name + ' ' + dtoc(date()) + ' ' + sub_title
    end with
    .hastitle = .f.
    with .axes(2)
      .hastitle = .t.
      with .axistitle
        .font.size = 10
        .font.bold = .f.
        .orientation = 0
        .text = "% Proficient or Count"
      end with
      .maximumscale = 100
      .ticklabels.font.bold = .f.
      .ticklabels.font.size = 10
    end with
    if somecondition
      with .legend
        .font.size = 10
        .font.bold = .f.
      end with
    else
      .haslegend = .f.
    endif

Loading data (from an array) :

for ds_row = 2 to n_cols + 1
  gr.datasheet.cells(ds_row, ds_col) = graph_data(DataRow, ds_row - 1)
next j

Setting x axis label:
gr.datasheet.cells(1, ds_col) = "First Trail"

Setting legendl:
gr.datasheet.cells(2, 1) = "Boys"
gr.datasheet.cells(3, 1) = "Girls"

In FoxPro
  .t. = true
  .f. = false
  endif = end if
  if stmt does not have then

Hope this helps
0
 
LVL 17

Expert Comment

by:ramrom
Comment Utility
BTW dtoc() is a FoxPro date to character conversion function.
0
 

Author Comment

by:dwbrown
Comment Utility
thanks for your answer, before I accept it, which i will, I have an additional question or 3. I have tried just setting the HasTitle property, just to establish communication with the graph, via VBA, with no success. Have you done this in Access? I assume you put the control on a form or report first? Is this an ActiveX control? Maybe I need to re-register it. I can insert the control on a form but can't access it in code. thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

763 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

12 Experts available now in Live!

Get 1:1 Help Now