Passing Parameters to a Data Access Page

Article
06/14/2014

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 explains how to create a page that accepts parameters and retrieves records based on those values. (7 printed pages)

Introduction Create a Page that Prompts for Parameters Create a Page that Shows Records Based on Specific Parameters

Introduction

A parameter query enables you to prompt for information, such as criteria for retrieving records in a form or report that is based on the query. You can also pass parameters from one data access page to another by using cookies.

The following illustration shows the Parameters page, which prompts for start and end dates.

Figure 1. A page that accepts parameters

When the user clicks the Navigate URL button after entering valid start and end dates, the Parameters Result page opens and displays all records that fall within the start and end dates.

Figure 2. Records for specific start and end dates

The data access page shows records that fall between 07/01/1996 and 07/15/1996.

Create a Page that Prompts for Parameters

Open a new data access page in Design view.
Add text boxes to the page to accept parameters from the user, and then name them. For example, the text boxes are named txtStart and txtEnd in the Parameters page.
Add a command button to navigate to the result page, and then name it. For example, the button is named btnNavigate in the Parameters page.

Add code to the OnClick event of the command button to write the cookies and navigate to the result page.

The following is a sample OnClick event for the command button that navigates to the result page.

<SCRIPT language=VBS>
Sub btnNavigate_onClick
Dim pStartingDate
Dim pEndingDate 
Dim URL

Const strParm_1 = "StartingDate"
Const strParm_2 = "EndingDate"

URL = "Parameters_Results.htm"
' Values in the text box are stored in the value property, 
' while bound span controls store the value in InnerText.
pStartingDate = txtStart.value
pEndingDate = txtEnd.value

setVariable strParm_1, pStartingDate
setVariable strParm_2, pEndingDate 

window.navigate(URL)
End Sub

</SCRIPT>

Add the functions to write, read, and delete cookies in a separate global script block.

<SCRIPT language=VBS>
OPTION EXPLICIT
Dim NOT_FOUND
NOT_FOUND = "NOT_FOUND"

Sub SetVariable(strVariableName, varVariableValue)
    Document.Cookie = strVariableName & "=" & varVariableValue
End Sub

Sub KillVariable(strVariableName)
    SetVariable strVariableName, "NULL;expires=Monday, _
                                  01-Jan-95 12:00:00 GMT"
End Sub

Function ReadVariable(strVariableName)
' These five variables are used in the string manipulation
' code that finds the variable in the cookie.
Dim intLocation
Dim intNameLength
Dim intValueLength
Dim intNextSemicolon
Dim strTemp

' Calculate the length and location of the variable name.
intNameLength = Len(strVariableName)
intLocation = Instr(Document.Cookie, strVariableName)

' Check for existence of the variable name.
If intLocation = 0 Then
    ' Variable not found, so it can't be read.
    ReadVariable = NOT_FOUND
Else
    ' Get a smaller substring to work with.
    strTemp = Right(Document.Cookie, Len(Document.Cookie) _
                                           - intLocation + 1)

    ' Check to make sure you found the full string, not just a 
    ' substring.
    If Mid(strTemp, intNameLength + 1, 1) <> "=" Then
        'Oops, only found substring, not good enough.
        ReadVariable = NOT_FOUND

        ' Note that this will incorrectly give a "not found" result if 
        ' and only if a search for a variable whose name is a substring 
        ' of a preceding variable is undertaken. For example, this will 
        ' fail:
        ' Search for: MyVar
        ' Cookie contains: MyVariable=2;MyVar=1

    Else
        ' Found full string.
        intNextSemicolon = Instr(strTemp, ";")

        ' If not found, get the last element of the cookie.
        If intNextSemicolon = 0 Then intNextSemicolon = Len(strTemp) _
                                                                  + 1

        ' Check for an empty variable (Var1=;).
        If intNextSemicolon = (intNameLength + 2) Then
            ' Variable is empty.
            ReadVariable = ""
        Else
            ' Calculate the value normally.
            intValueLength = intNextSemicolon - intNameLength – 2
            ReadVariable = Mid(strTemp, intNameLength + 2, _
                                                  intValueLength)
        End If
    End If
End If
End Function
</SCRIPT>

Create a Page that Shows Records Based on Specific Parameters

Open a new data access page in Design view.
Add the desired fields to the page.

Add code just before the closing <HEAD> tag to read cookies and retrieve records that match the specified parameters.

The following is a sample script used by the Parameters Result page. This script retrieves records that fall within the start and end dates from the Employee Sales by Country query.

<SCRIPT language=VBScript>

Dim pStartingDate
Dim pEndingDate
Dim errNot_Found
errNot_Found = "NOT_FOUND" 
pStartingDate = ReadVariable("StartingDate")
pEndingDate = ReadVariable("EndingDate")

If ((pStartingDate = errNot_Found) or _
                   (pEndingDate = errNot_Found)) Then 
    pStartingDate = inputbox("Please enter a start date:", _
                   "Starting Date")
    pEndingDate = inputbox("Please enter an end date:","End Date")
End If 

' This is the Jet syntax for adding the parameter values to the data 
' source control.
MSODSC.RecordsetDefs("Employee Sales by Country").parametervalues _
                               .Add "[Starting Date]", pStartingDate
MSODSC.RecordsetDefs("Employee Sales by Country").parametervalues _
                               .Add "[Ending Date]", pEndingDate

' Delete the cookie.
document.cookie = ""

' This is the SQL Server syntax for adding the parameter values to the 
' data source control. 
' document.msodsc.recordsetdefs(0).parametervalues.Add "&ParmName", _ 
' parameter_value

</SCRIPT>

Add the functions to write, read, and delete cookies to the page script.

<SCRIPT language=VBS>
OPTION EXPLICIT
Dim NOT_FOUND
NOT_FOUND = "NOT_FOUND"

Sub SetVariable(strVariableName, varVariableValue)
    Document.Cookie = strVariableName & "=" & varVariableValue
End Sub

Sub KillVariable(strVariableName)
    SetVariable strVariableName, "NULL;expires=Monday, 01-Jan-95 _ 
                                                 12:00:00 GMT"
End Sub

Function ReadVariable(strVariableName)
' These five variables are used in the string manipulation
' code that finds the variable in the cookie.
Dim intLocation
Dim intNameLength
Dim intValueLength
Dim intNextSemicolon
Dim strTemp

' Calculate the length and location of the variable name.
intNameLength = Len(strVariableName)
intLocation = Instr(Document.Cookie, strVariableName)

' Check for existence of the variable name. 
If intLocation = 0 Then
    ' Variable not found, so it can't be read.
    ReadVariable = NOT_FOUND
Else
    ' Get a smaller substring to work with.
    strTemp = Right(Document.Cookie, Len(Document.Cookie) _
                                           - intLocation + 1)

    ' Check to make sure we found the full string, not just a 
    ' substring.
    If Mid(strTemp, intNameLength + 1, 1) <> "=" Then
        ' Oops, only found substring, not good enough.
        ReadVariable = NOT_FOUND

        ' Note that this will incorrectly give a "not found" result if 
        ' and only if a search for a variable whose name is a substring 
        ' of a preceding variable is undertaken. For example, this will 
        ' fail:
        ' Search for: MyVar
        ' Cookie contains: MyVariable=2;MyVar=1

    Else
        ' Found full string.
        intNextSemicolon = Instr(strTemp, ";")

        ' If not found, get the last element of the cookie.
        If intNextSemicolon = 0 Then intNextSemicolon = Len(strTemp) _
                                                                   + 1
        ' Check for empty variable (Var1=;)
        If intNextSemicolon = (intNameLength + 2) Then
            ' Variable is empty.
            ReadVariable = ""
        Else
            ' Calculate value normally.
            intValueLength = intNextSemicolon - intNameLength – 2
            ReadVariable = Mid(strTemp, intNameLength + 2, _
                                                 intValueLength)
        End If
   End If
End If
End Function

Share via

Passing Parameters to a Data Access Page

Contents

Introduction

Create a Page that Prompts for Parameters

Create a Page that Shows Records Based on Specific Parameters

Additional resources