Create a Stacked Bar Chart in Code

[ISaunders]

Working in latest version of Access and trying to create a stacked bar chart in VBA. Got it partially working, but having some troubles. See some code below. It is not working correctly, just my last attempt. Can't figure out how to incorporate the COUNT of a value (trackingnumber) which is how I determine number of packages shipped. Attached is the correct I use currently by using the wizard, I would like to move it to code to allow for more flexibility at runtime.


str = "SELECT Orders.OrderID, Orders.ShipDate, Orders.PackedBy, Orders.Cancelled, UPSTrackingNumbers.TrackingNumber, Orders.Shipped, UPSTrackingNumbers.ShippingMethod FROM Orders INNER JOIN UPSTrackingNumbers ON Orders.OrderID = UPSTrackingNumbers.OrderNo WHERE (((Orders.ShipDate)=Date()) AND ((Orders.Cancelled)=False) AND ((Orders.Shipped)=True))"

With objchart
.ChartType = acChartBarStacked
.ChartTitle = "Total Orders Shipped by Packer"
.RowSource = str
.ChartAxis = "[ShippingMethod];[PackedBy]"
.ChartValues = "ShipDate" '"PackedBy"
.ChartLegend = "ShipDate"
End With

Thank you,

Ira

 

[June7]

You want to produce the second graph programmatically? You built it with wizard? What is its RowSource?
I deal best with graph issues when I can work with data. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[ISaunders]

I would like to create the code in VBA to produce the graph attached. Row source is
str = "SELECT Orders.OrderID, Orders.ShipDate, Orders.PackedBy, Orders.Cancelled, UPSTrackingNumbers.TrackingNumber, Orders.Shipped, UPSTrackingNumbers.ShippingMethod FROM Orders INNER JOIN UPSTrackingNumbers ON Orders.OrderID = UPSTrackingNumbers.OrderNo WHERE (((Orders.ShipDate)=Date()) AND ((Orders.Cancelled)=False) AND ((Orders.Shipped)=True))"

 

[June7]

So it is the same query in original post. This would go faster if you would provide data either as attached db or tables in post.

 

[ISaunders]

This is the result of the query.

 

[June7]

Getting that into Access is just a lot of work. I don't know when I will get around to. You are not making it easy to help you.
And now I feel a migraine coming on (seriously, happens occasionally) so will definitely be a while before I get back to this.

 

[Gasman]

Like trying to get blood out of a stone.
Whý do people make it so hard to help them?
If you are that concerned about your data, see the randomizer in my signature.
Make sure you still have the issue with the randomized data.

I feel a migraine coming on with this thread, and I do not even suffer from them.

 

[ISaunders]

Hey guys, sorry for the delay the database had 1,500,000+ records, so had to strip it down.

 

[ISaunders]

This is the result of the query.

 

[ISaunders]

This is the database with an Orders table, upstrackingnumber table and the query.

 

[June7]

Would have been nice to include the VBA developed so far as well as working chart built by wizard. Because the provided SQL alone could not have produced the chart shown in image.

This one does work as chart RowSource:

TRANSFORM Count(OrdersByPackerQuery.OrderID) AS CountOfOrderID
SELECT OrdersByPackerQuery.PackedBy
FROM OrdersByPackerQuery
GROUP BY OrdersByPackerQuery.PackedBy
PIVOT OrdersByPackerQuery.ShippingMethod;

If you built chart with wizard, you should have known that query. So exactly what is issue you are having?

More about CROSSTAB query http://allenbrowne.com/ser-67.html

 

[ISaunders]

When you drop a chart in a report and select the query as a row source, it asks you to define the axis, legend, etc. That's all I did to build the current chart.

Now I am trying to build it in VBA for more control and flexibility. There is very little documentation available on how to build and manipulate charts in VBA. You have all the VBA code that I have written so far. For example, as one of the values you can select Count(TrackingNumber). How would I do that in VBA.

I have trouble telling the chart what value to use and where in VBA.

 

[arnelgp]

check and test your chart.
i also created a temporary table ChartZ.
see the code on the combobox.

//Edit: replaced the db, now minimal flickering. also added datasheet subform.

 

[June7]

When you drop a chart in a report and select the query as a row source, it asks you to define the axis, legend, etc. That's all I did to build the current chart.

And once it is built you can view and modify its properties, including the SQL used in RowSource.
I use wizard to build a chart object - I just pick any table and fields and once the object is established I go change properties to what I really want, including RowSource. Most of my charts have not needed CROSSTAB or grouping query and the wizard generally insists on creating one or the other. I haven't been able to figure out way to manually create a chart object without the wizard. I should mention I can only use classic MSGraph charting.

I have code that manipulates chart features such as axis scale and titles and setting colors of data points (https://www.accessforums.net/showthread.php?t=29178) but that's about as far as I've gone. I am not sure where you want to reference the Count(TrackingNumber) value.

Your code references objchart variable but does not show declaring and setting it. Do you have Option Explicit in all module headers?

@arnelgp tried your version of database but I get error "cannot find field Chart7". Did you use Modern Charts?

 

[ISaunders]

arnelgp: This is sick! Thank you so much!​

I have been studying your work for the last few days. Is it possible to do the same thing, but without the external queries? The entire SQL in VBA only. I have been trying to code it, but running into issues.

 

[Gasman]

arnelgp: This is sick! Thank you so much!​

I have been studying your work for the last few days. Is it possible to do the same thing, but without the external queries? The entire SQL in VBA only. I have been trying to code it, but running into issues.

I think you might mean slick ?

 

[Minty]

I think you might mean slick ?

Nope - It's down with the Kids - Sick is Good/great

 

[Gasman]

Nope - It's down with the Kids - Sick is Good/great

Jeez, really banging then? roflmao

 

[KitaYama]

Nope - It's down with the Kids - Sick is Good/great

I wish you had used teenagers instead of kids.

Sick ;
Slang for cool or hawt or fabulous.
You’re a sick girl

cool
thats sick man

When something is really good, cool or very impressive.
I'm gonna make sick money at that place!

Ooooo- dat new tune is sick man
I played sick on my bio test