Pin Me

How to Format Your Graphs Using Visual Basic for Microsoft Access

written by: Alan Jones•edited by: Linda Richter•updated: 5/11/2011

What is a simple way to format a Microsoft Access graph? Visual Basic offers a simple way to provide your users with a wide range of options on how they would like the data displayed.

  • slide 1 of 4

    Introduction to Graphs

    Graph Options 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.

  • slide 2 of 4

    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 <tablename> SET ShowData = False WHERE GraphYear = '2013';"

    Then you need to refresh the graph itself.

    [Forms]!<name of form with graph>.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 <tablename> INNER JOIN <SeriesTable> ON <tablename>.SeriesName = <SeriesTable>.SeriesName SET <TableName>.ShowData = <SeriesTable>.ShowData;"

    Then refresh the graph again.

  • slide 3 of 4

    Changing the Appearance of the Graph

    Line and Bar 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]!<name of form with graph>!<Graphname>.Object

    If you right click on the graph in design view and go to properties, you can view/set <graphname> 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.

  • slide 4 of 4

    References and Images

    Based on writer's experience.

    Screenshots created by the writer.