Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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 partit
simply takes advantage of the DataSource component's
built-in functionality. To test it out with our sample database,
follow these steps:

1. 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

2. 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

3. Close the query.

4. 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.

5. 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

6. 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:

1. The sample won't work if you run it from within
   Access. Switch to Internet Explorer.

2. Open

   Param OrdersByDate 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 in a browser window

3. Enter start and end dates and click the OK command button. The
   

   OrdersByDate DAP will open in the browser.
   You'll see only orders between the dates you
   specified.

13.8.3 Discussion

Param OrdersByDate and

OrdersByDate 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:

1. In Access, open the

   Param
   

   OrdersByDate data access page. Note that the
   two text box controls are named txtStartDate and
   txtEndDate. The command button is named
   cmdOK.

2. 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.

3. 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")
   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 .

4. Close the Microsoft Script Editor and the

   Param
   OrdersByDate data access page.

5. Open the

   OrdersByDate data access page in
   design view.

6. Select View HTML Source to launch the Microsoft Script
   Editor. Search for the word
   "script".

7. 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.

8. 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.