Tool and Code: Manipulate Graphs, Charts in Microsoft Access using VBA
Channel: LearnAccessByCrystal
Date: 2014-08-12

Numbers looks so much better on graphs! This is example shows how to manipulate Graphs (also called Charts) in Microsoft Access using VBA ... set chart properties using VBA from Access based on specified criteria such as dates and key fields.  The menu is called  f_Graph_MENU.  The form that shows the graph is f_Graph.  f_PopupCalendar is a for collecting dates (tiggered by double-clicking a date control).  The sample tables are called t_GraphData and t_GraphTopics.  The code creates the qGraph query used by the graph.  This was developed in 2010 using Microsoft Graph 14.0 Object Library, which does need to be referenced due to late binding.  If graph has no data, an error is generated.  This could be trapped.  SetControl_RowSource is used on the After Update of the topic to limit other combos.  This is done by putting the SQL without criteria in the Tag property of each respective combo.  

There is a form called f_Graph that has the Microsoft Graph control and a couple other controls that get modified.  The menu form is called f_Graph_Menu. The button that opens the graph is captioned "Chart".  The time frame can be changed from Days to Weeks to Months.  On the right are other properties such as Chart Title, format for X and Y axes, X-Axis Title, and Minimum and Maximum Scale for the Y-Axis.

What makes the data work is modifying the SQL of the query that the graph is based on, qGraph.

This example also illustrates the method I use to cascade comboboxes.  I put the SQL with no criteria in the Tag property of each respective combo then send the criteria and the control to a function I wrote called SetControl_RowSource, which is usually a public function.  This example, however, embeds it behind the form that uses it and declares it to be private.

Double-click a date to popup a calendar form called f_PopupCalendar.  If a date OpenArg is sent to the form, it will set the calendar to that date and not store it.  This is done on the second date of the date range for cases when Date1 is specified but not Date2.  

DOWNLOAD Graph Example :
http://www.accessmvp.com/strive4peace/Access_Graphs.htm

Popup Calendar posted on Rogers Access Library
http://www.RogersAccessLibrary.com/forum/popup-calendar-for-access-2007-and-above_topic597.html

Customize the QAT (Quick Access Toolbar)

Access QAT (Quick Access Toolbar): Alignment Icons (cc) 
http://www.youtube.com/watch?v=ZF4pb7e1QSc

Tip: Add Save As Icon to QAT (cc) - Français, Dutch, Deutsch, English 
http://www.youtube.com/watch?v=WrG4f_TElTE&list=PLis0tV85b5t4C_nEEwbAc14Ihm9p7qV3Y

~~~
To learn about Access, read my free Access Basics book
http://www.accessmvp.com/strive4peace

and watch the videos here (then others):

Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA

*** Original Music  ***
JD Live (Jerome Dixon, Houston)
    * http://myspace.com/jdlivepro
    * http://www.youtube.com/user/jdlivepro
 
happy graphing! 

* let's connect *
Do you need help? You know your business. I know how to teach, write programs, and develop applications. If you have a budget and want control of your project, let's connect and build it together!

visit MsAccessGurus and contact me today!
http://msaccessgurus.com 

I look forward to hearing from you ~

warm regards,
crystal

Remote Programming & Training -- connect to me, let's built it together

~ have an awesome day ~