Read this in our Smart Access collections here

Probably the easiest way I found to work with the Graph object is by using Visual Basic IntelliSense to find out the methods and properties that are relevant to the graph object. This may not work with developers that haven't been involved with graphs before but it sure beats hunting around the very poor documentation and examples that come with the graph object. Figure 7 shows IntelliSense being used to explore the chart object.

Figure 7 Using IntelliSense to Explore The Chart Object

Now I have used a couple of tricks here that need explaining. Firstly it is important that you reference the Microsoft Graph 8 object library if you want to use IntelliSense. The code that I illustrated in Listings 3, 5 and 6 does not require this library to be referenced. This is deliberate as it is not a standard reference when Access is setup on a new machine. If I was doing a lot of Graph manipulation, I would keep this reference turned on.

So make the reference to the Graph 8 Object Library and include the following declaration

Dim Tester as Chart

So now whenever you type Tester, IntelliSense will start up. Whenever I have the properties and methods that I want, I cut and paste use the remainder of the object settings after "Tester" into the longer direct reference to the object. E.g. for the Legend Position Property, the code

tester.HasLegend = True

is replaced into

With [GraphFX].Object.Application.Chart

  .HasLegend = True

End with

By using the full reference to the Graph object using ControlName.Object.Application.Chart, I am allowing myself to run the application without a library reference to the Graph 8 object library.

Using The Object Browser

The second trick that I had to use was using the object browser to find the actual values of the constants. For this you will still need to have a reference to the Graph 8 Object Library. To open the object browser, view the code behind the form and hit the F2 key (Menu .. View .. Object Browser). Now select the Graph Library and view the Constants that appear at the bottom of all the Graph classes (see Figure 8).

When I have finished using the VB IntelliSense and the Object Browser, I turn the reference to the Graph 8 Object library off and recompile/test the code.

For Access 95, you will first need to reference the Graph 5 library. The settings inside graph object are different for the two libraries. If you are programming for Graph 5, you are not going to get IntelliSense to help you out so this is going to be hard. I would highly recommend that you try the Excel Cheating methods in lesson 6 to find out some of the more difficult settings.

  Click here for the download file if you own "The Toolshed"  Else click here 

Using Excel To Find Out About The  Access Graph Object  (Lesson 6)

Included in the training files is a Excel 97 file called smartGraf.xls. Use this to work through the following technique to find out how you can derive suitable visual basic code to manipulate the graf object.

1. Firstly cut and paste some data from a suitable Access query and store it in the spreadsheet.
2. Make up a graph inside Excel using the chart wizard.
3. At this stage it is important to experiment with the chart to practice changing the components of the chart in Excel. As a general rule of thumb, look for the section of the chart that you want to change and right click on it.
4. Now start recording a new excel macro which I have called YAxis as I am going to extract the code to change the scale of the yAxis. You will find this under Tools .. Macro .. Record New Macro
5. Make the changes to the graph and stop recording the macro.
6. Return to Tools .. Macro and open the new macro which will display the visual basic that it took to change the graph.

ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)

  .MinimumScaleIsAuto = True
  .MaximumScale = 200000
  .MinorUnitIsAuto = True
  .MajorUnitIsAuto = True
  .Crosses = xlAutomatic
  .ReversePlotOrder = False
  .ScaleType = xlLinear

End With

Copy the Visual Basic across to a command button on an Access form and make the following changes to the code as follows.

With Me![GraphFX].Object.Application.Chart.Axes(xlValue)

  .MinimumScaleIsAuto = True
  .MaximumScale = 200000
  .MinorUnitIsAuto = True
  .MajorUnitIsAuto = True
  .Crosses = xlAutomatic
  .ReversePlotOrder = False
  .ScaleType = xlLinear

End With

Now you have the visual basic code to do a lot to your Access graphs without anywhere near the steep learning curve. This will not always return the correct code but it sure will give you a good start on the properties that you should be manipulating.

  Click here for the download file if you own "The Toolshed"  Else click here 

Manipulate  Microsoft Access Graph Using Queries    (Lesson 7)

by Garry Robinson from GR-FX Pty Limited

Whilst all the lessons for controlling Microsoft Graph will add some pizzazz to your displays, the real art of making Graphs work is in setting up the correct queries to feed your graph. The way you go about doing this is through the Row Source Property of the graph object (see Figure 9).

Figure 9 The Row Source Property Of The Graph Object

In most cases you are going to manipulate this property using Consolidation Queries (see the October edition of Smart Access for the detailed article I wrote on this topic).

If we were to setup a consolidation query on this table to show the top 25 percent of sales results by product, we would have the following SQL query

[Top 25 Percent] Optional Top Values Clause

productName, Group By Columns

Sum(sales) AS totSales Aggregated values

FROM TblSalesResults Table/Query source

WHERE ((state="NSW") Where Clause

GROUP BY productName, region; Group By Columns

ORDER BY Sum(sales); Order By Clause

As a general guide, consolidation queries can be broken up into 5 different components.

Group By Columns These are the columns that you want to consolidate. Generally you will only select one consolidation field or function at this stage.

Aggregated Values If a column is not being grouped on, it should be subject to an aggregate function. These aggregate functions fall into two groups. Those that require a number field Sum, Avg, StDev, Var, and those that also work on text data Count, Min, Max, First, Last. MS Graph will plot any number of these at one time but more than 5 would generally not make much sense.

Table/Query source Simply the table or query where the data is derived from.

Where Clause Criteria that restrict the rows of data that are being analyzed (filters)

Order By You can sort the results of the query by any output field in the query and more if you choose. If you do not select a column, the query will sorted by the first Group By field.

Top Values Access supports a non compliant extension to the SQL standard that allows you to display only a portion of the rows that you retrieve from a query. This can be invoked by asking for a certain number of rows or by asking for a percentage of the total number of rows that are returned from the data set.

  Click here for the download file if you own "The Toolshed"  Else click here 

Summing Up - Manipulating Microsoft Access Graph     (Lesson 8)

by Garry Robinson from GR-FX Pty Limited

References

There are 2 invaluable Microsoft downloads that you should have. The main one is the Microsoft graph examples database which was the catalyst for many of these ideas. Also you should look for the Microsoft Knowledge Base article "Q154582". I explain how to get it at http://www.vb123.com/tips/99/freestuff.htm

Or you can find a diagram of the full Graph 5 object model at

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvb4/html/msdn_msgraph5.asp

or try the search engine at Microsoft at type in "MICROSOFT GRAPH OBJECT"

Smart Access has a free article on saving graphs as Gif files Click Here for more

Other Information

When you purchase "The Toolshed" you will recieve the database, forms and source code for these series of lessons.  You will also find out how to sort the graphs on the fly and use the Top values clause to limit the number of items that you view on each of these graphs.

Conclusion

In these lessons, I aimed to give you enough information to go ahead and start doing things with Microsoft Graph. There has been a trend in the programming of MS Office where developers are showing that they and their end users are comfortable with the Microsoft Office and want more control over the whole environment. As always when you start to programming outside the bounds of normallity, bear in mind that the costs of programming and support will be higher than simply keeping within the Access envelope. Also if you are trying to do some really important things with graph, remember that there are some pretty good third party graph controls around that might just do the job better and easier than MS graph.

  Click here for the download file if you own "The Toolshed"  Else click here 

Click for help

Related Microsoft Chart / Graph Issues

A couple of months ago I became involved in a project for a company that sells a popular management analysis program written in visual basic 5. The aim of this project was to convert the use of third party graph control across to the chart control that comes with visual basic. Even though I have been involved in graphing related activities over a long period of time, I had never had a look under the hood of the Microsoft Chart control. I simply assumed that it would be very similar/the same to the graph objects that come with Office 97. This is definitely not the case so I thought it would help other users if I gave a brief outsider summary of Microsoft’s offerings in the graphing area.

MS Graph 8 / MS Chart 8 that comes with Access 97 and Excel 97

MS Graph / Chart 5 that comes with Access 95 and Access 2 and Excel 95

MS Chart Control that comes with VB 6

MS Chart Control that comes with VB 5

The first big difference is the way that you interact with the object. For all versions of Access, the object is smart enough to allow you to pass a sql string to it as a source of its data. This is the most flexible programming method as it will handle any amounts of data. The way that Access does this is to turn the query results into spreadsheet grid before passing the data to the chart control. The chart control in Visual Basic 6 will now allow you to talk to MS Chart control by assigning a ADO data control to the source of the object. If you wish to populate a Excel graph, you will need to extract the data into some cells in the spreadsheet before applying the graph to it. If you wish to populate a VB 5 chart control, you will first need to make up a 2 dimensional array to populate the control.

The most significant difference between the current generation of tools is that which occurs in Visual Basic. The chart control in VB 5 has a very primitive look that probably was born in the early 90’s. Simple things like the histograms not having black borders through to some very unusable 3D displays makes for a lot of preparation work prior to passing data to the object. Also the different styles of charts supported is limited even compared to MS Graph 5 that came with Access 2. So along came VB 6 and the only improvement was the addition of the ADO data source. If you are programming in Visual Basic, please test the output of the control before you spend any substantial time programming the object. More than likely you will either have to source an external Active X control or you could active the Chart Control as an OLE object.

The final difference is the time that it takes to Manually setup the layout of the graph. In this case Excel definitely wins out as many an end user will support. With Access you not only have to battle with a tougher customization environment but you also have to ensure that you are starting off with good data prior to manipulating the chart. Also as there will only be one graph for each task, you will not be allowing the end users to manipulate the graph in your application. In the visual basic chart control, the object has less properties to manipulate but is a long way behind in the quality of design that occurs in Graph / Chart 8.

So now that the graphing project is coming to an end, here is what I ended up doing. I ended up using the User Controls in visual basic 5 and wrote a custom graph control. This was never my initial intention but the final control did everything the customer wanted, was far simpler to implement into their product and was about 100k in total size.

Why not try these VB 6 Graph Lessons

A lot of the information in these Access graph lessons first appeared in Smart Access by Pinnacle.  Garry highly recommends the Smart Access magazine as they have greatly assisted with 3 major projects undertaken by GR-FX during during 1999.  They are a pretty good read for advanced Access developers  !!!

Published  1999-08           

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals