Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 13.8 Use Parameters Set in One DAP to Open Another

13.8.1 Problem

In Access, every report your users run starts with a dialog prompting them for input parameters, such as the requested timeframe for the report. How can you do the same thing with DAPs? You'd like the user to fill in start and end dates in the browser, and then open the page using those dates as a "where condition."

13.8.2 Solution

There are several ways to handle this issue; we'll show you two. Both solutions discussed here require that you base your DAP on a query that uses input parameters in the criteria. The first solution allows the DataSource control to do the work for you, much like allowing Access to display the Input Parameter dialog when you run a query that requires parameters. The second solution requires you to create another page that asks the user to enter the criteria, much like using a form to feed the query on which a report is based. This solution provides more flexibility but requires you to write some code in VBScript. The VBScript code uses cookies to pass information between the two pages.

The first solution requires no extra work on your part—it simply takes advantage of the DataSource component's built-in functionality. To test it out with our sample database, follow these steps:

Open our sample query from 13-08.MDB, qryOrdersByDate, in design view. The query is shown in Figure 13-23. Note the input parameters, [Start Date] and [End Date], used as criteria.

Figure 13-23. The data source for our pages uses parameters in the criteria

Run the sample query. You'll be prompted for start and end dates with the built-in Input Parameter dialog shown in Figure 13-24. Enter any dates between July 1996 and May 1998 to see the query result.

Figure 13-24. The Input Parameter dialog

Close the query.
Create a DAP based on the query. Add whatever fields you'd like. You can also use our sample page, Sample with No Code, if you'd prefer.
Run the page. Before the page is displayed, you'll see the Enter Parameters dialog shown in Figure 13-25.

Figure 13-25. The Enter Parameters dialog

Enter start and end dates and click the OK button. You'll see the page, filtered to show only orders between those dates.

The second solution allows you to show the user your own parameter request dialog as an HTML page. To do this, you'll need to create a page to collect the parameters and then add code to both that page and the data page to use the values entered in the parameter page as the parameters of the query.

First, try out our sample by following these steps:

The sample won't work if you run it from within Access. Switch to Internet Explorer.
Open Param OrdersByDate.htm in the sample folder. The page is shown in Figure 13-26. It simply prompts for the criteria to be used in another DAP.

Figure 13-26. Param OrdersByDate.htm in a browser window

Enter start and end dates and click the OK command button. The OrdersByDate.htm DAP will open in the browser. You'll see only orders between the dates you specified.

13.8.3 Discussion

Param OrdersByDate.htm and OrdersByDate.htm work by using VBScript code to read and write information to a cookie that stays available for only one browser session.

Cookies, as you probably know, are bits of text that store information about what you are doing during a browser session. They are sometimes written out to disk so that the code used on a web site "remembers" what you were doing from one browser session to the next. In our case, the cookie will be available only in memory; it won't be written out to disk, and it will be deleted once the data page is displayed.

If your background is in database development, VBScript may be new to you. You can use scripts written in VBScript to enhance your DAPs, just as you can use VBA to enhance your forms and reports. If you already know VBA, you won't find VBScript particularly difficult to write. We won't attempt to teach you about VBScript or the document object model you'll use to control your page; we'll just touch on the key concepts for this sample. There are two key differences between VBA and VBScript that you should be aware of before we review the code:

You won't be working in the VB Editor when you write VBScript. You'll probably use the Microsoft Script Editor, but you can use any text or HTML editor, including Notepad.
Variables cannot be typed in VBScript. All variables are variants.

To get started with VBScript, take a look at the code we've written for this sample. Follow these steps to look at the code:

In Access, open the Param OrdersByDate.htm data access page. Note that the two text box controls are named txtStartDate and txtEndDate. The command button is named cmdOK.
Select View HTML Source from the menu. The Microsoft Script Editor will be launched, and you'll see the HTML code the browser uses to display the page.
Press Ctrl-F to do a search. Search for the string "script". The cursor should land on the script containing the event procedure for the cmdOK button's onclick event:
```
<SCRIPT language=vbscript>
Sub cmdOK_onclick( )

    Document.cookie = "startdate=" & txtStartDate.value 
    Document.cookie = "enddate=" & txtEndDate.value 

    window.navigate("OrdersByDate.htm")

End Sub
</SCRIPT>
```
The first two lines of code use the document's Cookie property to record the parameters entered in the text boxes. Each time the code sets the cookie to a new variable = value, that string is appended to whatever the string already contains, with a semicolon separating the variable = value pairs. That is, if the start date is 6/1/97 and the end date is 6/30/97, the cookie will look like this:
```
startdate=6/1/97;enddate=6/30/97
```
The third line of code causes the browser to open OrdersByDate.htm.
Close the Microsoft Script Editor and the Param OrdersByDate.htm data access page.
Open the OrdersByDate.htm data access page in design view.
Select View HTML Source to launch the Microsoft Script Editor. Search for the word "script".
There are two custom scripts in this data access page. The first contains a general-use function named ReadVarInCookie. The code looks like this:
```
<SCRIPT language=vbscript>

    Function ReadVarInCookie(strVariable)
    
    Dim varSplit
    Dim intCount
    Dim intFind

    varSplit = split(document.cookie,"; ")
    for intCount = lbound(varSplit) to ubound(varSplit)
       if left(varSplit(intCount),len(strVariable)) = strVariable then
          ' Figure out what's on the other side of the equals sign.
                
          intFind = instr(varSplit(intCount),"=")
          ReadVarInCookie = mid(varSplit(intCount),intFind + 1)
          exit function
       end if
    next
        
    ReadVarInCookie = "NOT_FOUND"
        
    End Function

</SCRIPT>
```
The function takes an argument of the variable names for which we're searching (startdate and enddate, in our case). It returns the value associated with that variable name. Remember, it's the cookie that is being searched for the variable and value, and the cookie looks like this:
```
startdate=6/1/97;enddate=6/30/97
```
The first line following the variable declarations uses the built-in Split function to parse the document's cookie into an array of variable = value pairs. That is, it looks for semicolons and creates an array element for each string between the semicolons:
```
varSplit = split(document.cookie,"; ")
```
The for loop iterates through each element in the resulting array and checks the first part of the element to see if the string matches the name of the variable sent:
```
for intCount = lbound(varSplit) to ubound(varSplit)
    if left(varSplit(intCount),len(strVariable)) = strVariable then
```
If the if statement evaluates to True, the code looks for the value on the other side of the equals sign and returns that value:
```
intFind = instr(varSplit(intCount),"=")
ReadVarInCookie = mid(varSplit(intCount),intFind + 1)
```
If the variable name is not found, the function returns the value NOT_FOUND.

Scroll down to the second script. This script is not tied to an event, nor is it even contained in a procedure. Rather, the script runs when the page loads:

<SCRIPT language=vbscript>

dim strStart
dim strEnd

strStart = ReadVarInCookie("startdate")

strEnd = ReadVarInCookie("enddate")

MSODSC.RecordsetDefs("qryOrdersByDate").parametervalues.Add "[Start Date]", 
strStart
MSODSC.RecordsetDefs("qryOrdersByDate").parametervalues.Add "[End Date]", strEnd

   document.cookie = "startdate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT" 
   document.cookie = "enddate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT" 
</SCRIPT>

The script calls the ReadVarInCookie function to find the values of startdate and enddate:

strStart = ReadVarInCookie("startdate")
strEnd = ReadVa1fp found, the code uses the DataSource component's object model
to set parameter values for the query on which the page is based:
MSODSC.RecordsetDefs("qryOrdersByDate").parametervalues.Add "[Start Date]", 
strStart
MSODSC.RecordsetDefs("qryOrdersByDate").parametervalues.Add "[End Date]", strEnd

Finally, the code clears the cookie by setting the variable values to Null and providing an expiration date in the past:

document.cookie = "startdate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT" 
document.cookie = "enddate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT"

We've only just touched the surface of coding DAPs. To go farther, you'll need to learn more about the document object model that Internet Explorer supports, and also about the Microsoft Office Data Source Control (MSODSC), the object model used in DAPs for retrieving and updating data.

[ Team LiB ]