Export (0) Print
Expand All
Expand Minimize

Examples of Using Data Access Page Events to Add Custom Functionality

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.

Aa139978.odc_exampleofpageevents1(en-us,office.10).gif

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.

Aa139978.odc_exampleofpageevents2(en-us,office.10).gif

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.
Show:
© 2014 Microsoft