How to use VBA to create a dynamic chart embeded in a form

[RunTime91]

Hello ~



I am attempting to replicate a dashboard I built in Excel using a form in Access. The dashboard is basic in design incorporating a datasheet; bar chart & 3 speedometers, and, of course, CMDbuttons using VBA to alter the view of the charts based on changing the Chart SourceData in the datasheet.

The concept is equally simple - The user opens the database and the form appears with performance data for his/her team of employees pre-loaded into the datasheet. The user can then click one of the CMDbuttons to change and sort different fields of data as well as change the charts' SourceData & Title which is then, of course, reflected in the bar chart.

This works great in Excel and I am hoping to achieve the same result in Access.

At the moment I have the form and the chart and I have written a simple line of code which sorts the chosen bar chart SourceData in the table in ascending order; however, the chart view does not change to ascending order. Below is the line of code

Sub Test1()
DoCmd.SetOrderBy "UnitsProduced"
End Sub

I'm assuming I have to use the VBA to manipulate the ChartForm - DataSheet as opposed to the actual table data - but I can't find anything on this - so here I am

As always

Any assistance is greatly appreciated

[June7]

Was not even aware of SetOrderBy method. I don't see how that can be applied to chart object. https://msdn.microsoft.com/en-us/lib...ffice.15).aspx

I use code to manipulate chart objects. Not sure what you are trying accomplish with sorting data. How would that affect the chart display? I analyze graph issues best when I can work with data and objects. If you want to provide db, follow instructions at bottom of my post.

[RunTime91]

June,

As always Thank You for stepping in to assist - I'm currently operating on a computer different than the one housing my db, but this computer does have the dashboard I built in Excel which is the template for the (dashboard/form) I am trying to replicate in Access. When I return home this evening I will send you the db.

Hopefully this file will explain better than I can exactly what I am trying to accomplish.

'til later this evening and as always Thank You for all your help.


Attachment 19760