Dynamic charts using vba code in access 2003

[Rameez]

Hi all,



I need help in preparing charts(not on a form). Please consider the example...

I have a table(zone,sub-zone,month,data) and lets say we have zones A,B,C...etc.
Each zone has 4 sub-zones S1,S2,S3,S4. Each sub-zone has sale-data for months Jan,Feb,...etc.

I need a combo box on a form with zone names(A,B,C,D) in it. When I click on A, a chart has to be generated with months on x-axis, data on y-axis for all the four sub-zones indicated in the chart.

I have done this with the help of chart wizard (didn't use combo box, used a bound form) and it is working fine...only thing I need it as a seperate report, not as a chart on the form.

PLEASE HELP...any model appliactions will be great

Thanks in advance.

[Rameez]

just to add to the stuff...

I used DoCmd.OpenReport "reportname", acViewPreview, , "zone=" & Me.cbo1 & ""

where reportname was a chart already prepared using chart wizard as a report and the name of the zone is in the combo box cbo1.

This code works fine for reports but when it is a chart, the same chart is coming up.


please...please...help required.

[davemr]

In access I am trying to go through a series of charts which are embedded in a form. With the selection of a new record in the form, new data is shown in the chart. This works. However the minimum value seems set at 0 in all of the forms. I would like to set a variable for each record in the form for the minimum x axis value. Then when I activate the next page of the form, have it set the minimum value to my variable. I can do this in Excel with the script:

ActiveWindow.Visible = False
Windows("test file.xls").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.031
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("test file.xls").Activate

However, I am having a hard time getting this to work in access.

[Rameez]

yes, looks like these things work well in excel....but in access they show up errors.


anyways i solved my problem...with regard to charts. though the solution may not be a correct approah...but it did solve the problem.

what i needed was....If the user wanted a graph about sale data between some period...The graph should be plotted only for that period.

what I did is every time the user gave some values in start date and end date....I queried the source table...and put the data in a temp table....the graph was drawn based on temp table...I put a delete from table query in the after update method of the graph....

[davemr]

It is looking like Access is inherantly flawed in this respect. I have seen examples of editing a chart in a report but not in a form. It looks like I may have to do an export to excell to make this work.

The frustrating thing is that I can do this really easily using the mouse and menu commands but can not find the magic words necesary to automate. Excell has a record function so you can see how your manual activities would be captured in code (Paradox database had this also) but with out such a feature in Access it is very hard to figure it out.

[thestappa]

So, is there a way to add a where clause to a chart that is on a report? I have a chart on a report using chart rowsource property to get the data. When I use a command button to open the report, I get the "report isn't bound to a table or query chart" error when I try to add a where clause.