Form Data as Source for Chart

[Bill Unger]

I have built a form that summarizes query data based upon several variables
and displays the results by categories in text fields - I am using VBA to
open the recordset and cycle through/calculate what I need.

I now have to create a graph of the results and don't want to re-process the
data. Is there a way to use the data displayed in the form as the source
for the graph?

thanks in advance,
bill

 

[Jeff Boyce]

Bill

Forms SHOW data, queries GET data, tables HOLD data (simplistic
understatement).

If you want to do a report on the same data you've put in a form, use the
same query you use for the form as the source for the report.

Or have I misunderstood...?

 

[Bill Unger]

Jeff,

Thanks for the reply!

I completely understand the difference between forms, queries, and
tables.....

The problem is that the calculations I am performing on the to display the
data in a particular fashion are beyond the scope of a query... that is why
I have the form. I wrote VBA code to pull data from a query, summarize it
( perform calculations ), then display the newly summarized data. I now
just need to graph the newly summarized data that is displayed on the
form... make sense?

As a side note, I don't want to do a report. I want to display the graph on
the same form as above...

Bill

 

[fredg]

I have built a form that summarizes query data based upon several variables
and displays the results by categories in text fields - I am using VBA to
open the recordset and cycle through/calculate what I need.

I now have to create a graph of the results and don't want to re-process the
data. Is there a way to use the data displayed in the form as the source
for the graph?

thanks in advance,
bill

Bill,

Try it this way.

As a sample I made a form showing a Record ID and a Number field from
a table, and 2 additional calculated form controls:
Text9 = [ANumber]*2
Text11 = [ANumber]*5/3

I've named the form "frmChart"

Make a new query, using the Form's record source table or query AND
add new columns to the query:
Exp:Val(forms!FormName!Text9)
Exp1:Val(forms!FormName!Text11)

Here is the resulting Query SQL:

SELECT tblBasicData.ID, tblBasicData.ANumber,
Val([forms]![frmChart]![Text9]) AS Exp,
Val([forms]![frmChart]![Text11]) AS Exp1
FROM tblBasicData
WHERE (((tblBasicData.ID)=[forms]![frmChart]![ID]));

(In my example above, I've restricted data to just the one record
shown on the form.)

I've named this query "qryFormChart".

Then use this query as record source for the chart.
Here is a resulting SQL as Chart row source:

SELECT [ID],[ANumber],[Exp],[Exp1] FROM [qryFormChart];

It worked fine for me.
Adjust the table, field, and form control names as needed. Use
whatever criteria you need in the query.

 

[Jeff Boyce]

Bill

See FredG's response. He beat me to the suggestion to use a "pointer" to
the fields on your form that hold the calculated values... <G> (way to go,
Fred!)