Chart: Using VBA to Create and Modify

[jared5]

A few questions regarding graphing in Access...

How do I change the recordsource for a chart so that the y-axis becomes the category axis and numerous fields can
be plotted against this y-axis?

In Excel graphing what I am trying to graph is simple (XY Scatterplot Chart), for each series the x-axis and y-axis can easily be chosen...But in Access I can not figure out
how it works. Is there a way in Access to manually program several series to graph..where you can separately
choose the x-axis and y-axis of several series and then plot them all on the same graph?

I want to graph various data versus depth in the ground...so I want depth on the y-axis and several fields plotted against depth (on a line graph)

I could plot one set of data versus depth using SQL: SELECT Data1, Depth ...but I need to be able to plot many sets of data versus the depth.

I haven't figured out how to rotate the chart either...but have been able to plot a bar graph with several sets of data plotted on the x-axis versus depth on the y-axis...but haven't been able to switch this to a line graph while still keeping the y-axis as the category axis.

I've also attempted to insert an unbound object as an excel chart. This way I can choose the data like I would in excel (in which case it is easy to graph what I am attempting to do in access). But I haven't had any success in selecting data from a query for the x and y axis rowsources.

I was hoping there was a way to graph in Access using VBA coding. I haven't had much success in finding information on this topic. Using a macro recorder in Excel I have viewed the code that excel used to create the desired chart. ..but when programming with VBA in Access, things such as .charttype are not recognized.

Anyone have any ideas? Thanks.

 

[rich.barry]

There are 2 ways of graphing in Access (that I know about)

1) Embed an MS Chart Control from the ActiveX components
2) Insert Chart to embed a Microsoft Graph Chart

I find the MS Chart Control great in Visual Basic, because you can point it straight to an array as a data source. In Access it is a dog so far. I haven't managed to get it running.

The MS Graph Chart seems to work quite well however.
I suspect the Row Source can be set by code, so you could use a form to generate an SQL statement to point the graph to the correct data. A quick play round and multiple lines were fine.

Row Source SELECT [FEX],[FEY],[ERY] FROM [BendingGraphTable];

I have a table that my graphs look at, and I use a delete query to clear the table, then fill it in with data (via code) based on the criteria in a form.

A bit waffley, but it might give you a few clues to make progress