Saving Queries as Read-Only Pages
| Saving query datasheets as DAP is an alternative to creating the live Web reports described in Chapter 23. If the query is updateable, you can edit the result set by the methods described in the preceding section. In most cases, however, pages based on queries are read-only, even if the result set is updateable. Datasheets with special formatting preserve the format by changing the Cascading Style Sheet (CSS) style for text boxes. For an example, save the Customers and Suppliers by City UNION query to a page that closely resembles the query's Datasheet view. To make the entire text box visible, you must increase the depth of the Header section from 17 to about 21 pixels and then expand the depth of text boxes, which appear as command buttons in Page Design view. On the whole, special effects applied to datasheets are less than special in pages. Designing the Query Layout for DAPIf you intend to save a query as a page, it's faster to change the column captions, when possible, and the column widths in Datasheet view. Changing the default Medium Date to Short Date format makes date columns consistent with other pages that use MM/DD/YYYY format. Figure 25.9 shows a sample Jet qryOrdersPage query in Datasheet view; column widths are minimized to ensure full display within IE 6+ maximized 800 x 600 monitor resolution. Applying a descending sort on the OrderID column displays latest orders first. Figure 25.9. Adjusting column widths, captions, and formatting of queries before you save the page minimizes redesign effort. For orders, it's usually preferable to set the default sort order to descending. When you save the query as a page, RPT2DAP.xsl might transform the design incorrectly. In this case, the Order ID caption doesn't fit the allotted space, so the caption and all rows have a two-line depth (see Figure 25.10). Figure 25.10. RPT2DAP.xsl interprets the insufficient width for the Order ID caption as requiring an increase in the depth of the rows. The solution to this problem is to change Order ID to ID in the caption, and delete the Order ID text in the first text box of the header. (Field names aren't necessary in Page Design view of text boxes.) You don't need record-selector buttons for a read-only query, so right-click the page, choose Group Level Properties, and set the RecordSelector property value to False. After making design changes similar to those recommended in the preceding two sections, your page appears as shown in Figure 25.11. The New, Save, Undo, and Help buttons are deleted because the query result set is read-only. Figure 25.11. Design changes for table-based pages described earlier have been applied to the query-based page shown here. When you remove the record-selector buttons, the left border of the text box for the first column is missing. Tip As an alternative to setting all margins at once by changing the <BODY style= "MARGIN: 0px attribute value, you can change individual margins on the Format page of the Page: PageName window. Set the MarginLeft and MarginTop property values to 17px (or more). To add a left border to the text boxes in the Header section, double-click the text box with the missing border in this case, ID to open its properties window. Delete the last #000000 value of the BorderColor property and delete the last none value of BorderStyle. Tip To minimize active connections to the database, open the Page: PageName properties sheet, click the Data tab, and use the RecordsetType property's list box to change the default dscUpdateableSnapshot to dscSnapshot.
Working with Parameterized QueriesUsers opening pages based on queries download the entire query result set to a local Recordset, unless you add a TOP n modifier or a WHERE clause to limit the number of rows. Alternatively, you can add a parameter to the query to limit the Recordset size. For this example, the parameter is the earliest order date to view. To avoid errors that result from not typing the parameter value in the exact date format, add a hidden OrderDate column and type >=[Enter Earliest Order Date (M/D/YYYY)] or the like in the Criteria row of the query. (Keep your parameter captions short for pages.)
Supplying Parameter Values in the Enter Parameters DialogWhen you open the page in Page View or Web Page Preview, a nonstandard Enter Parameters dialog appears first. The dialog can accommodate about 12 parameter values, but the Name list doesn't expand to accommodate fully descriptive parameter captions. Figure 25.12 shows the Enter Parameters dialog for the CurrentOrdersParam.htm page. Figure 25.12. A single parameters dialog replaces one or more Access Enter Parameter Value dialogs. The first Microsoft mystery of this chapter is why the parameter names are centered instead of left-justified in the list. Typing the required value and clicking OK (or pressing Enter twice) sends the parameter value(s) to Jet and opens the page. If you don't enter a value and click OK, no rows appear. If you click Cancel, the page opens with #Name? values in a single row. The parameter entry procedure is the same for SQL Server parameterized stored procedures and table-returning functions. Tip If you copy the parameterized query with a different name such as qryOrdersParam and use Save As to save the link and page with a different link and .htm file name, you must change the RecordSource property value on the Data page of the Section: NavigationQueryName window to point to the parameterized query. Open the RecordSource list, and scroll to the Procedure: items not Query: items to find the new parametized query (see Figure 25.13). The Procedure: prefix applies to Jet parameterized queries and SQL Server stored procedures with parameters. Figure 25.13. When you change the RecordSource property from a conventional query to a parameterized query, select the query name from the items with the Procedure: prefix.
Passing Filter Criteria from One Page to Another with CookiesThe Enter Parameters dialog isn't user-friendly, and it limits the explanation of the required parameter entry to a few words. A better alternative is an initial page in which users type one or more parameter values in text box(es), and then click a button or a label formatted as a hyperlink to open a second page that displays the data. The onClick event handler of the button or label writes a cookie with the parameter names and values, and then opens the other page. The second page retrieves the cookie and passes the values as query parameter values or the criterion value for the DataSourceControl's ServerFilter property. One of the advantages of using an initial page is that you can provide a default value for the parameter in a text box. Note The DataSourceControl's ServerFilter property is similar to the Filter property of a table or query. The Filter property downloads the entire Recordset and then applies the filter on the client. Applying a ServerFilter downloads only those records that meet the filter criterion, which minimizes network traffic and speeds response time. Cookies are a means for passing variable values between conventional HTML forms. Cookies are Name= Value pairs called crumbs separated by semicolons. Cookies can contain up to 20 crumbs. Unless you add an expires= datetime crumb, a cookie ordinarily stays in memory only while the browser is open. In Access, the cookie stays in memory while the page is open in Page or Page Design view. Cookies are properties of the browser's document object; you create a cookie with a VBScript document.cookie= " Name=Value" statement and retrieve it with a strVariable = document.cookie statement. Obtaining the value of a crumb requires parsing the cookie with VBScript string-manipulation functions. VBScript doesn't support all Jet/VBA string-manipulation functions; those that VBScript does support with a few exceptions use the same syntax as their Jet/VBA counterpart. Note Like VBA, VBScript isn't case sensitive. Web programmers use a variety of capitalization styles when writing VBScript code. A common style is to use lower case for VBScript key words and object, property, and method names defined by the Document Object Model. Mixed case is used for variable names and other object names such as MSODSC.RecordsetDefs for the RecordsetDefs collection of the Microsoft Office Data Source Control. This book uses mixed case for VBScript. VBScript has only one data type Variant. The VBScript examples of this chapter use variable-type prefixes such as dat for date and str for character variables for consistency with the VBA code that you encounter in Part VII, "Programming and Converting Access Applications." You can create the initial page in the Access page designer or any other HTML editor, such as FrontPage 2003. The initial page might include navigation buttons or labels for several related pages. Figure 25.14 shows a simple navigation form with links to the CurrentOrders.htm and CurrentOrdersCookie.htm pages. Figure 25.14. OrderStatusPages.htm is an unbound navigation page that has a link to CurrentOrders.htm and a text box for entering the earliest order parameter required when clicking the link to the CurrentOrdersCookie.htm page. Tip When you deploy a navigation page with links to other pages, create a virtual directory for it and all related pages in Internet Information Server (IIS). Rename the navigation page to Default.htm. Users enter http://servername/directoryname/ to open Default.htm; filename.htm isn't required. The hyperlinks of the navigation page of Figure 25.14 are formatted labels (blue color, underlined). The Show All Orders in a Form link's onClick event handler contains the following VBScript code to open the CurrentOrders.htm page: <SCRIPT language=vbscript event=onclick for=lnkAllOrders> <!-- dim strURL strURL = "CurrentOrders.htm" window.navigate(strURL) --> </SCRIPT> Note The preceding event-handler code is typical for links or buttons on pages that perform navigation functions similar to that of Northwind.mdb's Main Switchboard form. The Show Orders Up To and Including... link (lnkLatestOrders) points to CurrentOrdersCookie.htm and has a text box (txtEndDate) that contains a default value appropriate for the range of the latest orders in the Northwind.mdb Orders table. The following code illustrates how to create the parameter cookie (LastOrder=11000) before opening the CurrentOrdersCookie.htm page. <SCRIPT language=vbscript event=onclick for=lnkLatestOrders> <!-- Dim strURL Dim lngLastORder Const strParam1 = "LastOrder" strURL = "CurrentOrdersCookie.htm" lngLastOrder = txtLastOrder.value document.cookie = strParam1 & "=" & lngLastOrder window.navigate(strURL) --> </SCRIPT> The following VBScript added to the CurrentOrdersCookie.htm page reads the cookie, extracts the date from the cookie text, and adds a ParameterValues object to the MSODSC.RecordsetDefs collection. <SCRIPT language=vbscript event=BeforeInitialBind(varParams) for=MSODSC> <!-- Dim lngLastOrder Dim strCookie strCookie = document.cookie If Len(strCookie) > 0 Then 'Extract the date from the cookie lngLastOrder = Mid(strCookie, InStr(strCookie, "LastOrder=") +10) If InStr(lngLastOrder, ";") Then 'Remove trailing cookie name-value pairs lngLastOrder = Left(lngLastOrder, InStr(lngLastOrder, ";") - 1) End If MSODSC.RecordsetDefs("qryOrdersPage").ServerFilter = "OrderID >= "& lngLastOrder Else MSODSC.RecordsetDefs("qryOrdersPage").ServerFilter = "OrderID >= 11050" End If --> </SCRIPT>
Tip Don't attempt to pass a cookie value to another page in Page view; both pages must run in IE to share the cookie. The preceding code substitutes a constant OrderID value (11050) to prevent an error or downloading the entire Recordset.
|
To review the syntax of the Mid and InStr functions, which is the same in VBA and VBScript, 
EAN: 2147483647
Pages: 417