Special Edition Using Microsoft Office Access 2003

Recordsets underlying forms and reports trigger data events when you move the record pointer or change the value in one or more cells of the Recordset. The two most important of these events are BeforeUpdate and OnCurrent. The following two sections illustrate use of these two data-related events of bound forms.

Validating Data Entry in a BeforeUpdate Event Handler

The most common use of data events is to validate updates to the Recordset; you add validation code to the event-handling subprocedure for the BeforeUpdate event. The use of code, instead of setting field-level or table-level ValidationRule property values, is that VBA provides a much more flexible method of ensuring data consistency. Validation rules you write in VBA commonly are called business rules. Business rules often are quite complex and require access to multiple lookup tables some of which might be located in other databases.

For information on enforcing business rules, see "Validating Data Entry," p. 227.

Listing 28.3 shows an example of a set of validation rules for postal codes in the Suppliers table of Northwind.mdb, the Recordset of which is bound to the Suppliers form. The BeforeUpdate event, which triggers before a change is made to the Recordset, includes a predefined Cancel argument. If you set Cancel = True in your event-handling code, the proposed update to the Recordset doesn't occur.

Listing 28.3 A VBA Validation Subprocedure for Some International Postal Codes

Private Sub Form_BeforeUpdate(Cancel As Integer) ' If number of digits entered in PostalCode text box is ' incorrect for value in Country text box, display message ' and undo PostalCode value. Select Case Me!Country Case IsNull (Me![Country]) Exit Sub Case "France", "Italy", "Spain" If Len (Me![PostalCode]) <> 5 Then MsgBox "Postal Code must be 5 characters", 0, _ "Postal Code Error" Cancel = True Me! [PostalCode].SetFocus End If Case "Australia", "Singapore" If Len(Me![PostalCode]) <> 4 Then MsgBox "Postal Code must be 4 characters", 0, _ "Postal Code Error" Cancel = True Me![PostalCode].SetFocus End If Case "Canada" If Not Me![PostalCode] Like _ "[A-Z][0-9][A-Z] [0-9][A-Z][0-9]" Then MsgBox "Postal Code not valid. " & _ "Example of Canadian code: H1J 1C3", _ 0, "Postal Code Error" Cancel = True Me![PostalCode].SetFocus End If End Select End Sub

Using the On Current Event to Set Linked PivotChart Properties

To review how to create linked PivotChart graphs, see "Working with PivotChart Forms," p. 726.

Form_Current's code illustrates the use of Forms!... references to objects in subforms, sbf1997SalesPCLinked for this example. The sbf1997SalesPCLinked form contains only a PivotChart and is restricted to PivotChart view. Although you can use VBA code to create a PivotChart from scratch, using the Office Web Components (OWC) 11 design tools grafted to Access 2003's Form View toolbar is much easier.

Adding a Reference to the Microsoft Office XP Web Controls

Gaining programmatic access to OWC requires adding a VBA reference to OWC11.dll manually. Versions 10.0 and 11.0 of the PivotChart, PivotTable, Spreadsheet, Data Source, Expand, and Record Navigation controls both of which are provided by OWC11.dll appear individually in Access 2003's Insert ActiveX Control list. Adding an individual Web control to a form and then adding a reference to OWC10.dll can cause conflicts.

Tip

Adding a PivotChart (or any other OWC) control to a form from the Insert Object dialog adds a reference to the individual control, so you don't need to add a reference to OWC11.dll.

To add the required reference to OWC11.dll and explore PivotChart objects in Object Browser, do the following:

1. Open the form in which you intend to write event-handling code for a PivotChart (or PivotTable) in Form Design view or select the form in the Database window and click the Code button to open the VBA editor.

2. Choose Tools, References to open the References dialog, which doesn't contain a reference to the Microsoft Office Web Components 11.0 in the Available References list.

3. Click the Browse button to open the Add Reference dialog, navigate to the \Program Files\Common Files\Microsoft Shared\Web Components\11 folder, and click to select OWC10.dll (see Figure 28.28).

   Figure 28.28. The VBA editor's References dialog doesn't include Microsoft Office Web Components 11.0 in the Available References list, so you must add the reference to OWC11.dll manually.

4. Click Open to add the Microsoft Office Web Components 11.0 reference to the end of the Available References list (see Figure 28.29). Verify that the new reference's check box is marked, and click OK to close the dialog and add the reference to your VBA project.

   Figure 28.29. Manually adding a reference to an object (.olb), type (.tlb), or dynamic link (.dll) library file adds the reference to the bottom of the Available References list and enables the reference.

5. Press F2 to open Object Browser, and select OWC11 in the Project/Library list. Scroll to the PivotChart objects, which have names with a Ch... prefix (see Figure 28.30).

   Figure 28.30. Object Browser's Classes list for the OWC11 library displays all objects exposed by OWC11.dll. PivotChart objects begin with Ch.... PivotTable objects have a Pivot... prefix.

6. To obtain programming help for PivotChart objects and their properties and methods, select the object in the Classes list and click the Help (?) button to open the VBA help topic for the object (see Figure 28.31). Many topics have simple VBA programming examples for the object.

   Figure 28.31. Click the Help button of the Object Browser to display the help topic for a Web component.

Writing VBA Code to Apply Non-Persistent Property Values to Charts

After you've created a reference to OWC11.dll, you declare ChartSpace (all charts or graphs), ChChart (the current graph), ChSeries (the graph's line), and if your graph includes a trend line, ChTrendline object variables. The Form_Current event handler begins with a series of Set statements to create a pointer to each of these objects when you move to a new record (see Listing 28.4). The remaining Form_Current code sets custom property values of the ChChart, ChSeries, and ChTrendline objects. You can modify the code of Listing 28.4 to apply special properties to any line chart, not just linked charts. If your line chart isn't linked, change the subprocedure name to Form_Load to apply the property values when the form opens.

Listing 28.4 VBA Code to Set Non-Persistent Properties of Linked PivotCharts

Option Compare Database Option Explicit 'Declare the required OWC object variables for PivotCharts Private chtSpace As OWC11.ChartSpace Private chtChart As OWC11.ChChart Private chtSeries As OWC11.ChSeries Private chtTrendLine As OWC11.ChTrendline Private Sub Form_Current() 'Update non-persistent linked PivotChart properties 'Specify the subform's ChartSpace object Set chtSpace = Me. sbf1997SalesPivotChart.Form.ChartSpace 'Specify the first (and only) chart in the Charts collection Set chtChart =chtSpace.Charts(0) 'Specify the first (and only) series for the line graph Set chtSeries =chtChart.SeriesCollection(0) 'Change the number format to remove the decimal digits chtChart.Axes(1).NumberFormat ="$#,##0" 'Maintain the scale for all graphs chtChart.Scalings(chDimValues).Maximum =25000 chtChart.Scalings(chDimValues).Minimum =0 'Set the line weight to thick chtSeries.Line.Weight =owcLineWeightThick 'If there are no trend lines, add one If chtSeries.Trendlines.Count =0 Then Set chtTrendLine =chtSeries.Trendlines.Add() Else 'The first graph has a trend line Set chtTrendLine =chtSeries.Trendlines(0) End If With chtTrendLine 'Hide the equation and RSquared values .IsDisplayingEquation = False .IsDisplayingRSquared = False 'Change the color and weight .Line.Color = Red .Line.Weight = owcLineWeightThick End With End Sub