Formatting Microsoft Access Graphs: Visual Basic

Introduction to Graphs

Graphs can be a very powerful way of presenting a summary of your data, but like any other form or report in Access, they are based on a table or query in your Access database. It’s very easy to create a graph using the built-in wizard, but what if you want to allow your users a range of options so that they can display the data that they want to see? By using VBA to alter the source of your graph as well as the settings used to display it, you can give your users a wide range of options.

Altering the Source Data

The simplest way to give your user some control over the graph is to allow him to alter the range of data. I usually find it best to create a table that holds the data for a graph, and add a yes/no field that determines which records should be shown on the graph. In the graph pictured above, you could use SQL commands in VBA to alter the date range visible to the user, e.g.:

DoCmd.RunSQL “UPDATE SET ShowData = False WHERE GraphYear = ‘2013’;”

Then you need to refresh the graph itself.

[Forms]!.Refresh

The filter button on the graph above allows the user to choose which series (lines) should be displayed on the graph. I created another table with each series and a yes/no field to determine whether they should be shown. The button opens a form with the data from that table, and then updates the main graph data table based on the user choices:

DoCmd.RunSQL “UPDATE INNER JOIN ON .SeriesName = .SeriesName SET .ShowData = .ShowData;”

Then refresh the graph again.

Changing the Appearance of the Graph

When working with Microsoft Access graphs, Visual Basic–as usual–allows you to change pretty much any aspect of the graph through code, whether it’s functional (changing the way the data is set out), or purely cosmetic (changing the color of the background or the lines).

In order to make any of these changes, you’ll need to declare the graph object in your code

Dim grph as Graph.Chart

Set grph as [forms]!!.Object

If you right click on the graph in design view and go to properties, you can view/set under the ‘Other’ tab.

Here are a few things you can do now:

grph.ChartTitle.Text = “This is the title of the graph”

With grph.Axes(1, 1)

.HasTitle = True

.AxisTitle.Caption = “This is the x-axis text”

End With

grph.ChartType = 52 - This sets the graph to a bar graph

grph.SubType = 2 - This then sets it to a stacked barchart.

If you want to set the graph back to a line type, you can use this code to set every line back to a curved type.

grph.Type = 4

For X = 1 To grph.SeriesCollection.Count

With grph.SeriesCollection(X)

.Type = 4

.Smooth = True

.Border.Weight = xlMedium

End With

Next X

You can combine these to create a bar graph with a single (or group of) line series, by setting everything to a bar chart, then setting specific series to lines.

For X = 1 To grph.SeriesCollection.Count

With grph.SeriesCollection(X)

.Type = 3

End With

Next X

grph.SeriesCollection(“Name of Line Series”).Type = 4

That should be enough to help you create some Microsoft Access graphs with Visual Basic.

References and Images

Based on writer’s experience.

Screenshots created by the writer.