Help for MS Chart Control 6.0, thanks a lot!

[xpengi]

Hi guys,

i need to build statistical bar & pie chart based on defined query i
code. some questions here,

Q1. if i define some depended queriese in code, "query12, query13"
then i want to set the form's record source is query13, how to d
that?

i code as, but not work
...
dim defQuery as String
dim query12, query 13 as QueryDef

query12.SQL= "select.....from students...."
query13.SQL= "select.....from query12......"

defQuery = CurrentDb.QueryDefs(query13).SQL

Me.RecordSource = query13

' i pass the defined query to ADO function to query the database, the
get recordset object.
Call modADO.ConnectToThisDatabase
Call modADO.Students(modGlobal.curConn, modGlobal.rsRec
modGlobal.defQuery)
....



Q2. the query13 will output as below:

RANGE------------------PERCENT of TOTAL
0-5000---------------------0.3
5001-10000---------------0.1
10001-15000--------------0.2
null value-- ----------------0.4

I want to change 0.3 to 30%, how to do that in query13 or new query?
RANGE------------------PERCENT of TOTAL(%)
0-5000---------------------30%
5001-10000---------------10%
10001-15000--------------20%
null value-- ----------------40%





Q3. i paint charts by using MS Chart control 6.0 in this case and th
chart is built OK based on a test query.
the problem is how to assign the percent value(30%, 10%...) on eac
column of in chart (0-4999, 5000-9999....)? in Pie chart? if not, is i
possible to assign value to each corresponding legend in the chart?

i coded as below but not work:
...
With MSChart1 <-- bar chart
Set .DataSource = rsRec
.Visible = True
.ShowLegend = True
.Plot.Axis(VtChAxisIdY).AxisScale.Type = VtChScaleTypePercent
.Plot.Axis(VtChAxisIdY).AxisScale.PercentBasis
VtChPercentAxisBasisMaxChart
End With

With MSChart2 <- pie chart
Set .DataSource = rsRec
.Visible = True
.ShowLegend = True
End With
....


Q4. If i use MS Graphy Chart to paint the chart, it solved Question
via setting up its property and linking an existing query, but i canno
control the chart's data source in code...

i coded as below but not work:
....
With OLE1 <----name of MS Graphy Bar Chart
.RowSourceType = "Table / Query"
.RowSource = OLEQ
End With

how to code it?



Sorry for so many question & thanks a lot!

Peter




my student table is:

Student# -----------------City --------------------------Amount

 

[Michel Walsh]

Hi,


The querydef that lives in memory of your computer is not necessary the same
as the saved one living in the "database". Query13 would refer to the
query12 that is defined, if any, in the database, not to the one living in
memory of your pc, unless you explicitly saved query12 from your modified
copy, into the database.



In VB/VBA, you have to respecify the AS, in the multiple declaration else,
by default, you get a variant, not necessary what you want:


Dim i, j As Long

is the same as

Dim i As Variant
Dim j As Long



NOT equivalent to:

Dim i As Long
Dim j As Long



as running the following code may convince you:

Public Sub TestMultiDim()
Dim i, j As Long
i=null
j=null
End Sub



where an error will occur, at run time, on j=null, NOT on i=null, which is
valid for a Variant.



Hoping it may help,
Vanderghast, Access MVP

 

[xpengi]

as there are many tables(students99,students99...) in my project, i nee
to build these depended queries with variable table name in my code, no
as built-in query in Access, then sign the form's data source to th
query 13 which is based on query12. so, how to code it

 

[Michel Walsh]

Hi,


Use QueryDefs, with an s. Here, I modify Query10 (in the immediate debug
window) :


------------------


? CurrentDb.QueryDefs("Query10").SQL

SELECT *
FROM ParChi;



CurrentDb.QueryDefs("Query10").SQL = "SELECT TOP 10 * FROM ParChi"



? CurrentDb.QueryDefs("Query10").SQL

SELECT TOP 10 *
FROM ParChi;

-----------------------



Hoping it may help,
Vanderghast, Access MVP