Special Edition Using Microsoft Office Access 2003

RPT2DAP.xsl does a better job saving simple reports than saving forms as pages. Grouping levels in reports transform to GroupLevelx page sections with a navigation control; detail sections also gain a navigation control. Grouped sections are collapsed by default.

Following is a list of the most commonly used features of reports that don't survive the Save As process:

• Subreports like subforms are ignored. You must modify the resulting page such as by adding an embedded inline frame containing a page to emulate subreports.

• VBA code in the report's Class Module appears in the script as a comment block.

• You can't group by expressions that aren't supported by the Group On property of the report's group. The Alphabetic List of Products report handles the report's =Left([ProductName],1) grouping expression correctly because the report's Group On property value is set to Prefix Characters in the Sorting and Grouping dialog.

• Aggregate values in the outermost grouping section move below the navigation control in the navigation section. Text boxes lose their ControlSource property value when moved to this section.

• Aggregate value expressions in section footers don't work. You receive an error message if your report includes text boxes to display the result of aggregate expressions, such as =Sum([FieldName]).

• Expressions in the report's group headers and footers aren't supported.

• You can't add bound controls to a caption.

• The Can Grow and Can Shrink properties are ignored.

The restriction on subreports and loss of VBA code in Class Modules makes it difficult or impossible to save complex reports as useful DAP. Only 3 of the 11 sample Northwind reports Alphabetical List of Products, Invoice, and Products By Category save as readable pages.

Modifying the Alphabetical List of Products Page

The Alphabetical List of Products report is simple enough for RPT2DAP.xsl to convert without modifying the report before the transformation. The resulting page, however, requires many modifications to improve its appearance and usefulness. The design changes that you make to this report are typical for many simple reports saved as DAP.

To save the Alphabetical List of Products to a page, do the following:

1. In the Database window's Reports page, right-click Alphabetical List of Products and choose Save As to open the Save As dialog.

2. Delete Copy Of in the text box, select Data Access Page in the As list, and click OK to open the New Data Access Page dialog.

3. Shorten the file name of the page to ProductList.htm, click Yes to enable scripts, and click OK to open Page view of the transformed report. Expand one or more of the sections to verify the presence of detail records (see Figure 25.18).

   Figure 25.18. The Alphabetical List of Products report creates a page with a navigation control for each group. It's obvious that this page needs a makeover for viewing in a browser.

4. Change to Page Design view, and start redesign by dragging the empty text box below the title to the right of the title. Drag the caption labels from the Header:

   GroupLevel0 to the Caption: GroupLevel0 section to prevent repetition in every group level, and delete the colon suffix. Drag the line to under the captions, and drag the bottom of the Caption section to just below the line. Look ahead to Figure 25.19 for control positioning.

   Figure 25.19. The page design changes shown here make the transformed report appear more like a conventional page.

   Tip

   As you make changes to the page, switch to Web Page Preview to check your work. Opening the page in IE 6+ doesn't clear the undo stack when you save changes.

5. Navigation controls aren't appropriate unless the page has many rows in each section, which isn't the case with this page. Right-click in the Header: GroupLevel0 section, and clear the Record Navigation check box. Do the same for the Header: Alphabetical List of Products section.

   Tip

   Use Ctrl+ArrowKey or Ctrl+Shift+ArrowKey to make fine adjustments to the position of the controls.

6. In the Header: GroupLevel0 section, move the text box to the right of the Expand control. Double-click the Expand control, click the Other tab, and change the Src property value to Black Arrow. Drag the bottom of the section to the bottom of the text box.

7. Drag the bottom of the Header: Alphabetical List of Products to the bottom of the text boxes.

8. In the Footer: GroupLevel0 section, move the line up a few pixels, delete the empty text box in the footer, and drag the bottom of the section up.

9. Optionally, change the font of the labels and text boxes to match the font used in other DAP Tahoma, for the examples in this book. The design at this point appears as shown in Figure 25.19.

10. To add a date to the empty text box in the Caption section, double-click the text box, click the Data tab of its properties window, and type =Date() in the DefaultValue text box. Right-align the text, add the bold, and remove the italic attribute.

    Note

    Setting the Format property value has no effect on the date display in Page view.

11. The width of the QuantityPerUnit field is larger than required, so you can add the Unit Price field to the left of the Units in Stock field. Decrease the width of the text box to two grid dots wider than the Quantity per Unit label. Open the Field List and the Toolbox, select the Bound Span control, and drag the UnitPrice field to the right of the shortened text box. Move the Price label to above the new field.

    Tip

    Use bound span controls instead of text boxes wherever possible to improve page performance. If you add a text box, you must change the format of the text box to prevent its borders from appearing on the page.

12. Change to Web Page Preview to display the redesigned report in IE (see Figure 25.20).

Using a ServerFilter to Display a Single Invoice Page

The Invoice report is an example of a report that you can save to a page without a major design change. An Invoice page also is a candidate for addition to the OrderStatusPages.htm page with the order number the same as the invoice number passed by a cookie.

Creating and Modifying the Invoice Page Design

To transform the Invoice report to an Invoice.htm page and modify its design, do this:

1. Right-click the Invoice report item in the Database window, choose Save As, and change the name to Invoice. Then select Save As Data Access Page, click OK, and click Yes to dismiss the warning message, if it appears, to save the page as Invoice.htm.

2. Click the Expand control above the Ship To label to display the Order Details items. Verify that the Subtotal and Total amounts calculate correctly.

3. Change to Page Design view, and double-click the DatePrinted text box. Click the Data tab of the properties window, and type =Date() as the temporary DefaultValue property value.

4. You don't need navigation controls for this page, so right-click Header: GroupLevel0 and clear the Record Navigation check box. Do the same for the Header: Invoices Filter.

5. Right-click Header: GroupLevel0 again and select Group Level Properties. Double-click the ExpandByDefault property to change its value from False to True. Delete the Expand control under the Header: GroupLevel0. Save your changes.

6. Open the page in Web Page Preview to verify the design changes in IE (see Figure 25.21) and then close IE.

   Figure 25.21. The modified Invoice page has the Expand and record navigation controls removed and the missing Date value added.

Adding the VBScript Code to Accept the Invoice Number Cookie

If you created the OrderStatusPages.htm navigation page in the earlier "Passing Filter Criteria from One Page to Another with Cookies" section, you can add a line to the page to navigate to a specific invoice. This section demonstrates how to reuse VBScript code that you added to the CurrentOrdersCookie.htm page in the Invoices.htm page.

To add the VBScript required to accept a cookie value from the OrderStatusPages.htm page, do the following:

1. In the Database window, select the Invoice page and save it as Invoice (Cookie) with InvoiceCookie.htm as the page file name.

2. Select the Current Orders (Cookie) item and click the Microsoft Script Editor button to open the Script Editor for the CurrentOrdersCookie.htm page.

3. Scroll to and select the <SCRIPT>...</SCRIPT> block for the BeforeInitialBind event, copy it to the Clipboard, close the Script Editor, and close the page.

4. In Page Design view of the InvoiceCookie.htm page, click the Microsoft Script Editor button, go to the same location in page (after the <![endif]--> tag, press Enter, and paste the script block.

5. Change all instances of lngLastOrder to lngInvoiceNum,+ 10 to +11, two instances of qryOrdersPage to Invoices and, >= to =(see Figure 25.22).

   Figure 25.22. Reusing VBScript code from another page saves time. Only minor modifications are required to CurrentOrdersCookie.htm code to display a single invoice.

6. Save your script changes.

You can verify that the ServerFilter works by opening InvoiceCookie.htm in IE and verifying display of the default order 11077.

Changing the DatePrinted Value to the Shipping Date

The Invoice report displays the system date in the DatePrinted span. Ordinarily, the invoice date is the date shipped, although some firms have been known to issue invoices for products not shipped or services not rendered. To comply with generally accepted accounting practices (GAAP), the invoice date should be blank if the goods haven't been shipped. Failure to use the shipping date as the invoice date is another Microsoft mystery.

The solution to this problem is to set the innerText property of the page's DatePrinted span to the innerText property of the ShippedDate text box. The VBScript code to make the change is simple and illustrates how to write event handlers for the Date Source Control. MSODSC has many more events than the ActiveX Data Object (ADO) Recordset; most of the events have equivalents in bound Access forms.

To make the change to the ShippedDate span, do this:

1. With Script Editor open with the Invoice.htm page, navigate to a position above or below the cookie script that you added in the preceding section. Add an empty line between the </SCRIPT> and </BODY> tags.

2. With the insertion point on the empty line, choose MSODC in the Object (Client Object & Events) list, open the Event list, and select DataPageComplete to add an event-handling stub.

3. MSODSC events require a parameter placeholder, even if the event doesn't return a value to the parameter. Add (varParam) to the event=DataPageComplete argument to make the event handler work.

4. Add a DatePrinted.value = ShippedDate.value statement. The complete event handler code is

   <SCRIPT language=vbscript event=DataPageComplete(varParam) for=MSODSC> <!-- DatePrinted.value = ShippedDate.value --> </SCRIPT>

5. Save your changes, close the Script Editor, and close the page.

You can't test your addition to the script until you complete the next section.

Adding a Link to Open the Invoice Page

Adding a link for the Invoice.htm page to the OrderStatusPages.htm page is similar to adding the link to the CurrentOrdersCookie.htm page in the earlier "Passing Filter Criteria from One Page to Another with Cookies" section. You copy the existing link, text box, and label, change their Id values, and add a modified copy of the VBScript code for the new link's onClick event.

To add the link to the Invoice.htm page, do this:

1. Open Order Status Pages in Page Design view, and select the link, text box, and label. Press Ctrl+C and Ctrl+V to create a copy.

2. Change the text of the link and label, and reduce the width of the text box. Type a default order number in the text box (see Figure 25.23).

   Figure 25.23. Add links to other pages in the Order Status Pages page by copying and editing preceding links.

3. Double-click the link, click the Other tab of the properties window, and change the Id property value to lnkInvoice. Select the text box and change its Id to txtInvoice, and change the Id of the label to lblExplainInvoice, as shown in Figure 25.23.

4. Open the Script Editor, and copy and paste the VBScript block for lnkLatestOrder. Edit the event handler code as follows:

   <SCRIPT language=vbscript event=onclick for=lnkInvoice> <!-- dim strURL dim lngInvoice const strParam1 = "InvoiceNum" strURL = "InvoiceCookie.htm" lngInvoice = txtInvoice.value document.cookie = strParam1 & "=" & lngInvoice window.navigate(strURL) --> </SCRIPT>

5. Save your changes, close the Script Editor, and open the page in Web Page Preview. Click the three links to test the code that you added in this section and the preceding section.