Microsoft Office 2000/Visual Basic Programmer's Guide

Using Microsoft Access to Create a Spreadsheet Control

In Microsoft Access, you can add a Spreadsheet control to a data access page by opening the data access page in Design view, clicking the Office Spreadsheet tool in the toolbox, and then clicking the place on the page where you want the control to appear. The Spreadsheet control can be displayed and used like a worksheet, or it can be hidden and used as a recalculation engine that is tied to visible controls on the page.

To create a reference in a cell of the Spreadsheet control that gets data from another control on the page, you enter a formula that references the control by using the following syntax:

=document.controlname.value

For example, in a data access page with two controls named Value1 and Value2, the following formulas could be used to add the values from the two controls:

Cell A1 formula: =document.Value1.value
Cell A2 formula: =document.Value2.value
Cell A3 formula: =SUM(A1:A2)

The HiddenRecalcExample.htm sample file in the ODETools\V9\Samples\OPG\Samples\CH12 subfolder on the Office 2000 Developer CD-ROM uses seven visible text box controls and a hidden Spreadsheet control. The Spreadsheet control pulls the values from the first six controls into cells A1 through A6 and calculates the sum of those values in cell A7. The values from the text box controls are entered in the hidden Spreadsheet control by using the formulas shown earlier. The total of the values entered in the control is calculated in cell A7 and this total is then transferred to the visible txtTotal text box control by using the Spreadsheet control's Calculate event procedure:

Sub Spreadsheet1_Calculate(EventInfo)
   document.all("txtTotal").value = _ 
      formatNumber(document.all("Spreadsheet1").Range("A7").Value, _
      2, TristateTrue, TristateTrue)
End Sub

There are two ways to transfer a value from a cell on a Spreadsheet control to another control on a data access page. The first way is to use the Spreadsheet control's Calculate event, as shown in the previous example. The second way is to set the other control's ControlSource property to the cell in the Spreadsheet control you want to use. For example:

=document.all("Spreadsheet1").Range("A7").Value