Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Passing Parameters to a Data Access Page

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)

Contents

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.

Aa140012.odc_passparam1(en-us,office.10).gif

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.

Aa140012.odc_passparam2(en-us,office.10).gif

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

  1. Open a new data access page in Design view.
  2. 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.
  3. 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.
  4. 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>
    
    
  5. 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

  1. Open a new data access page in Design view.
  2. Add the desired fields to the page.
  3. 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>
    
    
  4. 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
    
    
Show:
© 2015 Microsoft