ACC2: Cannot Display More Than Four Graph Objects at Once

• Microsoft Access version 2.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you open a form or preview a report that contains more than four Microsoft Graph version 5.0 objects, you may receive the following error message:

   Not enough memory to complete the operation on the OLE object.

CAUSE

In Microsoft Access version 2.0, displaying each unbound Graph 5.0 object requires approximately 10% of system resources. This limits the number of graphs that you can display at one time to three or four (the actual limit is determined by the resources available in a particular system).

Activating a graph object in-place takes approximately 15% of the available system resources. For performance reasons, once a graph is activated in-place, the instance of Microsoft Graph 5.0 that was started to support the editing remains in memory until the form is closed. This limits the number of graphs that can be activated to three.

RESOLUTION

Limit the number of graphs on a single page to four. Reduce that number to three in forms where in-place activation of the graphs will take place.

If the graphs are not based on data that will change, convert them to pictures or store them in tables. More than four bitmaps or bound graphs can be displayed on a single page. If multiple graphs overlap on a form or report but only one is shown at a time, consider using only one graph object and setting its row source with code each time a different set of data is shown. For a detail description of how to do so, see the "Workaround" section later in this article.

STATUS

This behavior no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new report.

2. Add a Microsoft Graph 5.0 object to the report.

3. Copy and paste the Microsoft Graph 5.0 object to create a total of six graphs.

4. Preview the report. Note that you receive the error message mentioned earlier. If you choose OK, the report will be displayed, but one or more of the graph objects will be blank.

Workaround

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

The following example demonstrates how to modify the Sales By Product form in the sample database NWIND.MDB so that you can choose between a graph showing sales by month for 1992 and a graph showing sales by month for 1993:

1. Open the Sales By Product form in Design view.

2. Add a command button to the form. Change the button's Caption property to "1992" (without the quotation marks).

3. Create an event procedure for the button's OnClick property by clicking the OnClick property field and then choosing the Build button to the right of the field. Select Code Builder, and then add the following code.

   NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Me!Embedded13.RowSource = "SELECT DISTINCTROW_
         Format([Order Date], ""MMM \'YY"") AS [Sales for 1992], _
         SUM([Sales by Product].[Product Amount]) AS [Product Amount]_
         FROM [Sales by Product] WHERE [Order Date] BETWEEN _
         #1/1/92# AND #12/31/92# GROUP BY Year([Order Date])*12_
         +Month([Order Date])-1, Format([Order Date], ""MMM \'YY"");"
   

   HINT: This query was created by copying the RowSource property of the graph on the Sales By Product form. The year in the criterion and chart title was changed and extra quotation marks were added to the second argument of the Format() function.

4. Choose the Compile Loaded Modules button on the toolbar, and then close the module.

5. Add another command button to the form. Set the new command button's Caption property to "1993" (without the quotation marks).

6. Create the following event procedure for the button's OnClick property.

   NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Me!Embedded13.RowSource = "SELECT DISTINCTROW_
         Format([Order Date], ""MMM \'YY"") AS [Sales for 1993], _
         SUM([Sales by Product].[Product Amount]) AS [Product Amount]_
         FROM [Sales by Product] WHERE [Order Date] BETWEEN _
         #1/1/93# AND #12/31/93# GROUP BY Year([Order Date])*12_
         +Month([Order Date])-1, Format([Order Date], ""MMM \'YY"");"
   

7. Choose the Compile Loaded Modules button on the toolbar, and then close the module.

8. View the form in Form view. Choose the 1992 button to change the graph's row source so that it displays sales for 1992.

REFERENCES

Microsoft Access "User's Guide," version 2.0, Chapter 19, "Using Pictures, Graphs, and Other Objects," pages 478-483