Solved: Charts in Access

[Arty-G]

I am a beginner at Access, but I know enough VBA to be dangerous (Excel experience). I need to create a chart for 200 different datasets (the same chart over and over and over again). I'm looking for a way to dynamically control all the charts, so if one thing changes, I change it once, not 200 times.

I heard there was a way to do this in Access, but the question is: is it simple enough that I can be tutored through it in a venue such as this, or should I just go get an Access for Dummies book and start a-readin'? Is the Access solution the best one here?

Thanks! -art

[SJ McAbney]

You would set the chart's RowSource to the query you want to represent.

[Arty-G]

I have a report that contains a chart in the company group footer. So, for each company in the report, the chart at the end of each company section shows only the data for that company. Is that what you're looking for?

Yes, that's exactly what the output will be like. I want to be able to control all my charts at once. So if there is a change to the chart format, or if different data is requested in the charts, I make one change vs. 200.

What would be my first step toward this end?

[Pat Hartman]

The query for the chart needs to contain the companyID. That way, you can set the master/child links properly so that the report can control what is shown on the chart. My charts are in the "company" footer so at a break in company, the chart appears. Take a look at the attached picture.

PS - if you want your charts to show your own data in design view rather than the dummy data, create them first as forms then copy them to the report.

One more thing. You may need to requery the charts before printing if your computer is "too fast" so that they don't stay in sync with the report data.

[VBA]Private Sub FtrProjVer_Format(Cancel As Integer, FormatCount As Integer)
Me.chrtPlan.Requery
Me.chrtImplem[/VBA]

[Arty-G]

Thanks, Pat. I'll give it a whirl. -art

[Zack Barresse]

Hey Art,

Did this ever work for you?

[Arty-G]

I ended up using a different solution--not access based after all, but in Excel. So my question is closed, thanks.

BTW, I used a data validation list on my chart sheet with the company names in it, then pointed the data source to a vlookup range at the top of my datasheet. So all the user needs to do is to choose the company name, and the charts update dynamically.