Access Cookbook - Ken Getz [287]
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.
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