CHAPTER

18

Charts and Graphs

F

rom Access 2007 onwards, creating and manipulating charts using VBA has
become quite difficult and complicated. There is noAddChartmethod as in Excel
VBA and if you create a chart on a report, you will find that the object model for it
does not have the usual properties and methods you may be expecting.
Even worse, there is no macro recorder in Access, so you cannot cheat and record the
VBA code while you create a chart manually on a report!
To use VBA with charts, you will need to put a reference in your VBA code to the
Microsoft Graph 12.0 Object Library. To do this, click Tools | References in the VBE menu
and then scroll down to the Microsoft Graph 14.0 Object Library. Your screen should look
like Figure 18-1.
Check the box on the reference and click OK.
Next, you need to create a table to hold your chart data. Click Create in the Access menu
and then click the Table Design icon in the Tables Group of the ribbon. Create two fields
(aNameas text andaValueas a number). Save the table astblChart. Click No on the dialog
asking you to create a primary key.

231