|
vb123.com |
|
|
Home Contact Us Order our Software RSS &
Newsletter
Get Good Help
The
Workbench
Find out who has your database open, start the
correct version of Access, easy compacting and backups, change startup
options, mde compile, shutdown database
Read and
Download Garry's Blog Like FMS Products?
About The Editor
Garry Robinson
writes for a number of popular computer magazines, is now a book author
and has worked on 100+ Access databases. He is based in Sydney,
Australia or try our new site built with
SharePoint Designer |
Is Your Database Corrupt ?
Taking Control Of Microsoft Graph from Microsoft Access
(Lesson 1)by Garry Robinson from GR-FX Pty Limited
For a long time my approach to managing graph objects was to set them up with the required look and then change the graph by manipulating the query. When Access 97 became the standard version used by most of my clients and shareware users, it became time to invest some resources into manipulating the complicated Graph object. These lessons concentrate on the techniques that you can use to program the object plus demonstrates some property changes that you can utilize to make graph more interactive.
Technology Introduction
Microsoft Access ships with many smart components including forms and reports, text and combo and list boxes, options groups and the ability to add Active X and OLE components. The most complicated of the standard objects is Microsoft Graph. As MS Graph is part of the standard install process with Access, you will generally find that the object is available on most computers that Access is installed on.
The reason that the graph object is complicated is probably because it has so many features and also because the changing of any of these features usually involves a corresponding change to some other feature to make the object act in a satisfactory manner. The other reason is that whilst the graph wizards would infer that the graph object is a fully integrated component like a text box, it really has to be managed in code in the same way that you might manipulate Excel from Access.
The Demonstration Database
The demonstration database is called smartgraph.mdb. Inside that database is a table called zWorld_Demo plus a form called z_GraphPlus with the Visual Basic that manipulates MS Graph. Listing 1 shows the demonstration data used in these lessons. You can find all the code used in these lessons plus the Access forms when you purchase "The Toolshed".
Product Name |
Region |
Country |
SalesDate |
Sales |
Budgets |
| Bottled Water | The Americas | Mexico | 28-Oct-98 |
780.00 |
760.00 |
| Bottled Water | The Americas | USA | 28-Oct-98 |
420.00 |
400.00 |
| Cola | Asia/Pacific | Australia | 14-Jan-98 |
343.00 |
600.00 |
| Cola | Asia/Pacific | Japan | 14-Jan-98 |
4000.00 |
3600.00 |
Listing 1 A number of lines of information from the zWorld_Demo table
Adding A Smart Graph To Your Database (With Some VB)
The smartest way to make the most of these lessons is to simply import the form (see figure 2) from the demonstration database into your application, change the row source of the graph object to suit your data and then deploy the form. The code under the form will work for any data in any Access 97 or 2000 database.
Figure 2 - Click to zoom
Click here for the
download file if you own "The Toolshed" Else click
here
Changing The Microsoft Access Graph Type (Lesson 2)
by Garry Robinson from GR-FX Pty Limited
To change the graph type from a bar graph to a pie graph, using the option box, I use the following code behind the form
Private Sub GrafType_AfterUpdate()
[GraphFX].Object.Application.Chart.ChartType = GrafType
End Sub
Listing 3 Changing the Graph Types
GraphType is the name of the option box and [GraphFX] is the name of the MS Graph Object. The trick here is that each of the option boxes has a option value property that matches the correct constant that relates to the ChartType. These constants are shown in table 4 as follows
ChartType |
97 Constant |
97 Value |
95 /2 Equiv |
95/2 Value |
|
Bar |
xlBarClustered |
57 |
XlBar |
2 |
|
Stacked Bar |
xlBarStacked |
58 |
N/A |
N/A |
|
Line |
xlLine |
4 |
xlLine |
4 |
|
3d Line |
xl3DLine |
-4101 |
xl3DLine |
-4101 |
|
Area |
xlAreaStacked |
76 |
xlArea |
1 |
|
3D Area |
xl3DAreaStacked |
78 |
xl3DArea |
-4098 |
|
Column |
xColumnClustered |
51 |
xlColumn |
3 |
|
3D Column |
xl3DColumn |
-4100 |
xl3DColumn |
-4100 |
|
Pie |
xlPie |
5 |
xlPie |
5 |
|
3D Pie |
xl3DPie |
-4102 |
xl3DPie |
-4102 |
Table 4 - The constants used used in MS Graph to change the graph type
Yes I know I should never store the constants as hard coded numbers but the resultant code is simpler as the default value will only allow an integer value.
Click here for the
download file if you own "The Toolshed" Else click
here
Turning the Microsoft Access Graph Legend On and Off (Lesson 3)
by Garry Robinson from GR-FX Pty Limited
Adding the legend to the graph starts to demonstrate the vagaries of the Graph object (see listing 5). Initially you would imagine that it would simply be a case of turning the Legend on and off and yes you can do this. But in this case turning the legend on causes the graph width to be reduced in size to accommodate the space that is automatically allocated to the legend. This doesn’t matter if the form that you are using occupies a full SVGA screen but when it occupies a smaller amount of space, the auto spacing allocation may not be so useful. After a lot of experimentation, my preferred setting is to use the Legend as a pseudo title line and display it at the top of the screen. This means I do not have to tangle with the same issues with title as well as legends and it also saves some valuable real estate for the graph object.
Private Sub legendTgl_Click()
' Turn the legend on at the top of
the object
' and ensure the width of the graph is restored
' Use xlLegendPositionTop in place
of -4160
' if you have the graph library referenced
With [GraphFX].Object.Application.Chart
If legendTgl = 0 Then
.HasLegend = False
.PlotArea.Width = .Width
.PlotArea.Height = .Height
Else
.HasLegend = True
.Legend.Position = -4160
.PlotArea.Width = .Width
.PlotArea.Height = .Height
End If
End With
End Sub
Listing 5 VBA that will turn the Legend On And Off
The most important lessons demonstrated here is the fact that if you alter the property of the graph is some way, it is fairly likely that other parts of the graph are going to be affected. If this is an issue, you are going to have to work out a way to fix those other properties with some compensating property changes. This is one of the reasons that manipulating the graph object can be time consuming. The other lesson is to use the With Statement whenever you can when programming with the graph object. This will make for cleaner looking code and it will also improve performance as the reference to the object only has to be established once rather than for each line of code.
Click here for the
download file if you own "The Toolshed" Else click
here
Viewing the Data Table in Microsoft Access Graph (Lesson 4)
Graph 8 allows you to view the data that makes up the graph by turning on the data table. This is handled in Listing 6 by referencing the Boolean constant provided by the dataTableTgl toggle button.
Private Sub dataTableTgl_Click()
With Me![GraphFX].Object.Application.Chart
.HasDataTable = dataTableTgl
End With
End Sub
Listing 6 Toggling the Data Table View On and Off
Click here for the
download file if you own "The Toolshed" Else click
here
If you like these lessons, why not try Graf-FX for some very versatile Access graphing.
Now you need to click on this button
to start lesson 5
Related Documents at VB123
Setting Up A Scatter ( XY ) Graph In Microsoft Access
Making The Most Of The OLE Chart Object
Consolidation Queries
Data Mining Using MS Access
Using Access Filters to Graph and Explore Your Databases
Published 1999-08