Responding to Data Events

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 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, rather than setting field-level or table-level ValidationRule property values, is that VBA provides a much more flexible method of assuring 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 may be located in other databases.

◂◂ See Validating Data Entry

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 does not occur.

Code Listing 28.3. A VBA Validation Subprocedure for 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

Data events of combo boxes commonly are used to select a specific record or set of records for display in a form or subform. The EditProducts form of Solutions.mdb, shown in Figure 28.31, displays product and supplier data for selections made consecutively in the Select Category and Select Product drop-down lists. When you make a selection in the Select Category list, the action populates the Select Product list with products. Selecting a product in the Select product list displays product and supplier data in the text boxes of the form.

The VBA code of Listing 28.4, from the class module for the EditProducts form of the Developer Solutions database, illustrates extensive use of data events of the Form object and combo box control objects. Making a selection in the Select Category list executes the SelectCategory_AfterUpdate event handler, which requeries the Select Product list to display the products for the selected category. Making a selection in the updated Select Product list executes the SelectProduct_AfterUpdate event handler, which applies a filter to the Recordset of the form to display the product data. In turn, changing the record pointer of the form's Recordset executes the Form_Current event handler, which tests for low product inventory.

Code Listing 28.4. Data Event Handlers in the Class Module for the EditProducts Form of Solutions.mdb

						Option Compare Database   'Use database order for string comparisons.
Option Explicit

Private Sub Form_AfterUpdate()
   'Requery SelectProduct combo box.
   Me!SelectProduct.Requery
End Sub
						Private Sub Form_Current()
'If value in UnitsInStock text box is less than value in ReorderLevel
'text box and value in UnitsOnOrder text box is 0, display
'value in UnitsInStock in red and display LLowStock label.

   If (Me!UnitsInStock + Me!UnitsOnOrder) <= Me!ReorderLevel Then
						Me!UnitsInStock.ForeColor = 255
      Me!LLowStock.Visible = True
						Else
     'Otherwise, display value in UnitsInStock text box in black
     'and hide LLowStock label.
      Me!UnitsInStock.ForeColor = 0
      Me!LLowStock.Visible = False
						End If
						End Sub
						Private Sub GoToSupplierID_Enter()
'Go to SupplierID combo box to prevent advancing
'to another record.

   Me!SupplierID.SetFocus
End Sub
						Private Sub ReorderLevel_AfterUpdate()
'Run Form_Current procedure.

    Form_Current
End Sub
						Private Sub SelectCategory_AfterUpdate()
'Enable and requery SelectProduct combo box.
'Disable controls in detail section.

   Me!SelectProduct.Enabled = True
						Me!SelectProduct.Requery
   EnableControls Me, acDetail, False
						End Sub
						Private Sub SelectProduct_AfterUpdate()
'Find record for product selected in SelectProduct combo box.
'Enable controls in detail section and disable ProductID text box.
'Go to SupplierID combo box.

    DoCmd.ApplyFilter , "ProductID = Forms!EditProducts!SelectProduct"
    EnableControls Me, acDetail, True
						Me!ProductID.Enabled = False
    Me!SupplierID.SetFocus
End Sub
						Private Sub SelectProduct_BeforeUpdate(Cancel As Integer)
   If IsNull([SelectProduct]) Then
						MsgBox "You must select a product."
      Cancel = True
						End If
						End Sub
						Private Sub UnitsInStock_AfterUpdate()
'Run Form_Current procedure.

    Form_Current
End Sub

Private Sub UnitsOnOrder_AfterUpdate()
'Run Form_Current procedure.

    Form_Current
End Sub

The VBA code examples in this chapter only cover the basics of the use of VBA 5.0 for responding to events triggered by forms, controls, and Recordsets bound to forms or reports. A full course in VBA programming exceeds both the scope and the publishing limitations of this book. Many of the examples of this chapter are drawn from the sample databases supplied with Access 97. You can adapt many of the techniques illustrated in the event-handling subprocedures of the sample databases to custom applications you create. To become an expert in VBA programming requires study, experimentation, and perseverance. Periodicals, books, and Web sites, such as those listed in the "Bibliography" section of the introduction to this book, are likely to satisfy the studious reader. There's no substitute, however, for experimentation. Writing and testing code is the only sure way to become proficient in VBA programming.