Access Charts on forms / reports

(Guidelines for creation and dressing up)
=============================

 

1 - Open the form in design view and insert a chart by clicking Insert > Chart in the menu bar, followed by drawing a rectangular outline for the chart. This will invoke the chart wizard.

 

2 - Select the desired query (Q_Chart in this case) to serve as RowSource and click Next.

 

3 - Select the field required to serve as X Axis (DateTime field in this case) and move it to right list box by clicking forward arrow. No need to select any other field. Click Next.

 

4 - For type of chart, select Line Type (Third button in third row on this page of chart wizard) and click Next, resulting in Chart Layout page of wizard.

 

5 - On chart layout page of chart wizard:
      5.1 - You should find the DateTime field automatically assigned to the Axis box at bottom of chart.
      5.2 - If Axis box is empty, drag the DateTime field (from field button at right) to Axis box.
      5.3 - Don't try to do anything else at this stage. You will find the Data box and Series box empty. Let it be. Don't even click the preview button at this stage. Do not worry how the chart layout looks at this stage.
      5.4 - Click Next.

 

6 - On the next page, select a suitable title for the chart in text box at top (say "Sample Chart"). Select the radio button with label "Yes, display a legend". Click Finish.

 

7 - On closing the chart wizard:
      7.1 - The form is still in design view. Select the chart object, go to Other tab of its property dialog box and enter a suitable name (in this case C_BP).
      7.2 - Go to the Data tab of properties dialog box. If the source query contains only the fields to be actually used in the chart (either as Axis or as Series), simply put the name of this query (in this case Q_Chart) as the row source for chart object. This is in replacement of SQL inserted as row source by chart wizard. If the source query has extra fields (not meant to be directly shown in the chart), put a suitable SQL statement as row source, for example:
      SELECT XAxisField, SeriesField1, SeriesField2, SeriesField3 FROM SourceQuery;
      (IMP: First Field In The Row Source, Whether Saved Query or SQL, MUST BE THE ONE MEANT TO SERVE AS X-Axis)
      7.3 - Close the property dialog box, save the form in design view, close the form and re-open it in normal view. This would give you an idea as to further dressing up needed. Close the form.

 

8 - Dressing Up:
      8.1 - Re-open the form in design view, select the chart object and expand its size suitably.
      8.2 - Right click on the chart object and then click Chart Object > Edit on the sub-menus. When you do so, the chart object becomes editable. At the same time, source data gets displayed as a datasheet. DO NOT INTERFERE WITH THIS DATASHEET.

 

      8.3 - Chart Options:
      8.3.1 - Right click any blank space on chart object and select Chart Options in the shortcut menu. This will open the dialog box for Chart Options.
      8.3.2 - On Titles tab, make suitable entries for the titles pertaining to X and Y axis respectively. For this example, enter Date/Time for X axis and Readings for Y axis. Chart title reads Sample Chart as entered in chart wizard stage. Here, you can change it to anything else as desired.
      8.3.3 - On Axes tab, you will find the check boxes for X and Y axis, as well as the radio button labeled Automatic in selected state. No need to disturb.
      8.3.4 - On Gridlines tab, you will find both the check boxes for X axis in de-selected state. For Y axis, only the check box labeled Major gridlines is in selected state. No need to disturb.
      8.3.5 - On Legend tab, make sure that the check box labeled Show legend is in selected state. Select the radio button labeled Bottom for Placement.
      8.3.6 - On Data Labels tab, nothing is in selected state. No need to disturb.
      8.3.7 - On Data Table tab, check box labeled Show data table is in de-selected state while that labeled Show legend keys is in selected state. No need to disturb. Click OK to close the dialog box for Chart Options.

      8.4 - Fonts for X and Y axis:
      8.4.1 - The font size for readings on X and Y axes, as inserted by chart wizard, will most likely be too large. Right click the X axis and click Format Axis on the shortcut menu. On the Font tab, select the desired font and size (In this case: Times New Roman, size 8). Repeat similar action for the Y axis.
      8.4.2 - If the depiction of readings on X axis is getting skipped due to space constraint, go to Alignment tab in Format Axis dialog box and drag the arrow pointer to make the alignment close to vertical.

 

      8.5 - Fonts for titles and legend: You can adjust the font type and size for chart and axis titles as well as the legend by right clicking and selecting Format Chart Title (or Format Axis Title / Format Legend as the case may be). Thereafter, proceed as per 8.4 above.

 

      8.6 - Positioning of titles and legend: To move the titles and legend to desired destination, click the title or legend. It will get surrounded by a rectangle which can be dragged suitably as desired. The legend rectangle can be re-sized as well.

 

      8.7 - Resizing the plot area: Click any blank space within the grid lines. Plot area will get selected, which can be re-sized as desired.

 

      8.8 - Converting chart type for a data series: Right click the line graph for pulse and select Chart Type. In the resulting dialog box, select Column as the chart type.

 

      8.9 - Click in any clear area of the form window (other than form canvas itself) to get out of the chart edit mode. Save and close the form.

 

      Note - Further fine tuning, to the extent desired, can be carried out by right clicking and exploring various available settings in formatting dialog boxes for different components of the chart, apart from Chart Options and Chart Type dialog boxes.

    References: DAO 3.6, Excel Object Library, Microsoft Graph Object Library, Microsoft Scripting RunTime.

==========================================