Examples of Using Data Access Page Events to Add Custom Functionality

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Meyyammai Subramanian
Microsoft Corporation

April 2001

Applies to:
   Microsoft® Access 2002

Summary: This article covers examples of attaching code to some data access page events. (7 printed pages)

Contents

Using the Focus Event Using the BeforeDelete and AfterDelete Events Using the BeforeUpdate Event Using the AfterUpdate and AfterInsert Events Using the BeforeInitialBind Event

Using the Focus Event

The Focus event fires when the focus shifts as a result of user action. For example, when the user clicks a record on the page, the selected record gets focus, and the Focus event fires.

The following illustration shows a page that uses the Focus event to change the background color of the current record.

Figure 1. A data access page that highlights the current record

Create a Page that Captures the Focus Event

  1. Create a data access page with the desired controls and group levels. Place the controls in tabular layout as show in Figure 1.

  2. Add code to change the background color of the current record and to restore the original background color of the record that was previously highlighted.

    The following sample Focus procedure sets the background color of the current record to ButtonFace.

    <SCRIPT language=vbs>
' oPreviousSection is used to remember the background color
' of the section before it was highlighted.
Dim oPreviousSection           ' as Section
Set oPreviousSection=Nothing

Sub msodsc_focus(dscei)

' If oPreviousSection points to a section, you know that 
' you need to restore that section's original background.
If Not oPreviousSection is nothing Then

    ' Find out if the bg color was white or the alternate color.
    If oPreviousSection.htmlcontainer.recordnumber mod 2 Then
        oPreviousSection.htmlcontainer.style.backgroundcolor=""
    Else
        oPreviousSection.htmlcontainer.style.backgroundcolor= _
               oPreviousSection.datapage.grouplevel.alternaterowcolor
    End If
End If
dscei.section.htmlcontainer.style.backgroundcolor="buttonface"

' Set oPreviousSection to the new section that just received the focus 
' so you can restore the background color when a different section 
' receives the focus.
Set oPreviousSection=dscei.section
End Sub
</SCRIPT>

Using the BeforeDelete and AfterDelete Events

The BeforeDelete event fires immediately after the user clicks the Delete button on the record navigation toolbar. The AfterDelete event fires after the user attempts to delete the record. For example, you can capture the BeforeDelete event to prompt the user to confirm record deletion. You can capture the AfterDelete event to inform the user about the status of the delete operation.

Figure 2 below shows a page that has a check box and a bound span control in addition to the data controls. The BeforeDelete event requests a confirmation if the check box is selected. The AfterDelete event displays a message in the bound span control, indicating whether or not the record was successfully deleted.

Figure 2. A page that captures BeforeDelete and AfterDelete events

Create a Page that Captures the BeforeDelete and AfterDelete Events

  1. Create a data access page with the desired controls and group levels.

  2. Add a check box and a bound span control to the header section and name them. For example, in the sample page code below, the check box is named DeleteConfirm and the bound span control is named DeleteStatus.

  3. Add code to the BeforeDelete event to request confirmation based on the status of the check box.

    The following is a sample BeforeDelete event procedure that prompts the user to confirm before deleting an order detail record.

    <SCRIPT language=vbscript event=BeforeDelete(dscEventInfo) for=MSODSC>
<!--
Dim c
Dim answer
Dim orderId

Set c = MSODSC.Constants
dscEventInfo.DisplayAlert = c.dscDataAlertContinue

If DeleteConfirm.checked Then
    orderId = dscEventInfo.DataPage.recordset.Fields("OrderID")
    answer = MsgBox ("Are you sure you want to delete Order Detail #" _
                   & orderId & "?", vbYesNo, "Delete " & orderId & "?")
    If answer = vbNo Then
        dscEventInfo.returnValue = False
    End If
End If
-->
</SCRIPT>

  4. Add code to the AfterDelete event to display delete status in the bound span control.

    The following is a sample AfterDelete event procedure.

    <SCRIPT language=vbscript event=AfterDelete(dscEventInfo) for=MSODSC>
<!--
Dim c

Set c = MSODSC.Constants

If dscEventInfo.status = c.dscDeleteOK Then
    DeleteStatus.innerText = "The order detail was deleted."
ElseIf dscEventInfo.status = c.dscDeleteCancel Then
    DeleteStatus.innerText = "Canceled through code."
ElseIf dscEventInfo.status = c.dscDeleteUserCancel Then
    DeleteStatus.innerText = "Canceled by the user."
Else
    DeleteStatus.innerText = "Unspecified."
End If 
-->
</SCRIPT>

Using the BeforeUpdate Event

The BeforeUpdate event fires before your changes to the record are saved. You can use this event to validate data that is entered in controls on the page. For example, you can check to see whether the user entered a valid country name in the Country text box, and display an error message accordingly.

Create a Page that Validates User Input

  1. Create a data access page with the desired controls and group levels.

  2. Add code to the BeforeUpdate event to validate the contents of one or more controls.

    The following is a sample BeforeUpdate event procedure that displays a message if the user enters a value other than "USA" or "UK" in the Country text box.

    <SCRIPT language=vbscript event=BeforeUpdate(dscEventInfo) for=MSODSC>
<!--
Dim cn

If IsNull(dscEventInfo.DataPage.Recordset.Fields("Country")) Then
    cn = ""
Else
    dscEventInfo.DataPage.Recordset.Fields("Country") = _   
        UCase(dscEventInfo.DataPage.Recordset.Fields("Country"))
    cn = dscEventInfo.DataPage.Recordset.Fields("Country")
End If

If Not (cn = "USA" OR cn = "UK" OR cn = "") Then
    Msgbox "Please specify the country as either 'USA' or 'UK'."
    dscEventInfo.returnValue = False
End If
-->
</SCRIPT>

Using the AfterUpdate and AfterInsert Events

The AfterUpdate and AfterInsert events fire after changes to a record have been saved or after a new record has been inserted. You can use these events to notify the user about the outcome of the update or insert operation. For example, you can maintain a log of changes made to the underlying recordset by capturing these events.

Create a Page that Maintains a Log of Changes Made to the Underlying Recordset

  1. Create a data access page with the desired controls and group levels.

  2. Add code to the AfterUpdate and AfterInsert events to create a log table, and update it each time the user makes a change.

    The following is a sample AfterUpdate event procedure.

    <SCRIPT language=vbscript event=AfterUpdate(dscEventInfo) for=MSODSC>
<!--

Dim strCmd
Dim time
Dim empID
strCmd = "CREATE TABLE UpdateInsertLog ([Time] DATETIME, _
                                [Type] CHAR, EmployeeID INTEGER);"

' Use On Error to suppress error messages resulting from trying to 
' create a table that already exists.
On Error Resume Next

' Insert log table into Northwind.
MSODSC.Connection.Execute strCmd

' Turn VBS error handling back on.
On Error Goto 0
time = Now()
empID = dscEventInfo.DataPage.Recordset.Fields("EmployeeID")

' Insert log information.
strCmd = "INSERT INTO UpdateInsertLog VALUES ('" & time & "', _
                                         'Update', " & empID & ");"

MSODSC.Connection.Execute strCmd
-->
</SCRIPT>

    The following is a sample AfterInsert event procedure.

    <SCRIPT language=vbscript event=AfterInsert(dscEventInfo) for=MSODSC>
<!--

Dim strCmd
Dim time
Dim empID

strCmd = "CREATE TABLE UpdateInsertLog ([Time] DATETIME, _
                                  [Type] CHAR, EmployeeID INTEGER);"

' Use On Error to suppress error messages resulting from trying to create 
a table that already exists.
On Error Resume Next
' Insert log table into Northwind.
MSODSC.Connection.Execute strCmd

' Turn VBS error handling back on.
On Error Goto 0

time = Now()
empID = dscEventInfo.DataPage.Recordset.Fields("EmployeeID")

' Insert log information.
strCmd = "INSERT INTO UpdateInsertLog VALUES ('" & time & "', _
                           'Insert', " & empID & ");"

MSODSC.Connection.Execute strCmd
-->
</SCRIPT>

Using the BeforeInitialBind Event

The BeforeInitalBind event fires before the data source control retrieves data. For example, you can use this event along with the ServerFilter property to limit the records that are retrieved from the server.

Create a Page that Prompts for Criteria and Retrieves Data Accordingly

  1. Create a data access page with the desired controls and group levels.

  2. Add code to the BeforeInitialBind event to set the ServerFilter property of the RecordsetDef object based on user input.

    <SCRIPT language=vbscript event=BeforeInitialBind(info) for=MSODSC>
<!--
Dim iCategoryID

iCategoryID = clng(inputbox("Enter a CategoryID (for example _
                                                  1):","ServerFilter"))
MSODSC.RecordsetDefs("Products").ServerFilter = _
                              "CategoryID = " & iCategoryID
-->
</SCRIPT>

Note

   

When you create Visual Basic® Scripting Edition (VBScript) blocks for Microsoft Office Data Source Control (MSODSC) events, you must add a parameter to the event name as follows:

<SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>

The oEventInfo parameter is used to return specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script won't work without it.