Saving the Inventory by Category Report to a Page
The Inventory by Category report (rptInventoryByCategory) that you created in Chapter 16, "Working with Simple
and Mailing Labels," is a candidate for conversion to a page. This report illustrates problems that you encounter with aggregate values in
error message shown in Figure 25.24 appears during the transformation process. It's a common practice to use text boxes with aggregate functions in report group footers, so you're likely to see this message often. Loosely translated, the error message means that RPT2DAP.xsl doesn't translate
expressions into the required GroupOf
function for section aggregates.
Figure 25.24. This error message appears when the report that you save as a page has an aggregate expression in a group footer.
When you click OK to
the message, the page opens with #Name? as values of aggregate text boxes. Clicking the Expand button to display rows of the detail section opens an "Operation is not allowed when the object is closed" message (see Figure 25.25). The Category
is missing because pages don't display lookup field values. You can't put aggregates in the outermost footer, so there's no Grand Total value. To create a page that you can modify to
with page design rules, you must delete the text box expressions and then save the modified report as a page.
Figure 25.25. After acknowledging the error shown in Figure 25.24, the page opens with errors and missing elements. Another error message appears when you click the Expand control.
Modifying the Source Query to Supply Required Field Values
For this example, substituting CategoryName and supplier CompanyName values for lookup fields requires
the source query—qryInventory, for this example. You must also supply fields to replace the calculated Cost and Value
because the page section Value aggregates depend on field values. The once-simple qryInventory query requires a total makeover. Fortunately, the required changes to the query don't affect the design of the original rptInventoryByCategory report.
The SQL statement for the modified qryInventory query is:
[View full width]
[View full width]
SELECT Products.ProductID, Products.ProductName, Products.
Products.UnitPrice, CCur(0.667*[UnitPrice]) AS Cost, Products.
CCur([UnitsInStock]*[Cost]) AS [Value], Categories.CategoryID,
Categories.CategoryName, Suppliers.SupplierID, Suppliers.
FROM Suppliers INNER JOIN (Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
ON Suppliers.SupplierID = Products.SupplierID
The modified qryInventory query is in the \Seua11\Chaptr25\Data25.mdb file.
Creating and Modifying the Source Report for the Page
To permit saving the sample report as a page and to simplify the page redesign process, do the following:
Save a copy of rptInventoryByCategory as rptInventory.
rptInventory in Report Design view. In the Detail Section, right-click the SupplierID drop-down list and choose C
ange To, T
xt Box. Replace SupplierID with
as the Control Source value.
Delete the expression in the Cost field, and type
as the field name. Do the same for the Value text box, but type
In the CategoryID footer section, change the CategoryID list to a text box, and delete the CategoryID field name. Delete the expressions in the remaining four text boxes in the CategoryID footer. All text boxes display Unbound as their value.
Delete the two text boxes in the Page Footer section, and move the Report Footer section up. Your redesigned source report appears as shown in Figure 25.26.
Figure 25.26. The changes shown here are required to eliminate errors when transforming the report to a page and to specify the columns to display from the modified source query.
Change to Print Preview to check your work. Close rptInventory and save your changes.
Right-click rptInventory in the Database window, choose Save
s, and save the report as the Inventory page with Inventory.htm as the file name.
Click an Expand control to display the initial version of the Inventory.htm page (see Figure 25.27). Click OK to acknowledge "The DefaultSort property is invalid" message.
Figure 25.27. Deleting the expressions in the source report's text box and changing the data source of the text boxes results in this initial page design.
Removing the Navigation Controls and Adding a Category Caption
The Inventory page has only eight categories and a few products per category, so you don't need navigation controls on the form. If you retain the Header: GroupLevel0 section and the Expand controls for the group, instead of expanding all groups by default, a CategoryName caption adjacent to the Expand control aids readability.
To make the initial design changes to the page, do this:
Change to Page Design view. Right-click Header: GroupLevel0, and clear the Record
avigation check box. Do the same for the Header: qryInventory section.
Double-click the Expand control, click the Other tab, and change the
property value to
Open the Toolbox, select the Bound Span control, and draw the control adjacent to the right of the Expand control. Delete the added label.
Double-click the control, click the Data tab, open the
list, and select
property value changes to
If you want to retain the Category label and
text box in the Footer: GroupLevel0 section, double-click the text box and set its
. In this case, the property value becomes
If you change the
property value of the
text box or delete it, you must change the value of the
property of the
group properties. For this example, the original value is
ASC, but this field no longer exists in the Field List. Right-click the section, choose
roup Level Properties, and set the
property value to
Be sure to change the
property when deleting or changing transformed text boxes on which group sorting depends. If you don't, you receive "The DefaultSort property is invalid" messages when changing to Page view. The
property value must match a GroupOf
item in the Field List.
property value that raises the error on expanding a group is
with no field name for GroupLevel: Query Inventory. Change
to fix the problem.
If the font name for the new controls that you've added isn't the same as the existing controls, conform the fonts in other than the Caption: GroupLevel0 section. Tahoma is the font used for most of the examples in this book.
Change to Page view to check your work so far (see Figure 25.28).
Figure 25.28. Removing navigation controls, adding a CategoryName caption, and making other minor changes improve the usability of the form.
Adding Category Subtotals and Grand Totals to the Page
Adding subtotals and grand totals to pages
considerably from the method that you use for reports. The AutoSum feature makes replacing the missing group subtotals and grand totals easy.
To replace the report's missing subtotals and grand totals, do the following:
Select the UnitsInStock text box in the Header: qryInventory section, and click AutoSum to add a new label and
text box in the Footer: GroupLevel0 section. Delete the label and the original text box, and drag the
text box to the right of the Sum: label.
Repeat step 1 for the text box under the Value label. Move the SumOfValue text box to the right of the Cat. Value label.
Delete the % Cat. and Value label and text boxes in the Footer: GroupLevel0 section. Programming calculated text box values is beyond the scope of this chapter. Drag the Category label and text box to the right.
text box, and click AutoSum to add a Footer: qryInventory-SumOfUnitsInStock section and a
text box. Delete the associated label. Align the left and right edges of the Footer section with the sections above it.
Repeat step 3 for the
text box, and then adjust the
of the text boxes under the
Select the Grand Total: label, and press Ctrl+X to cut it to the Clipboard. Delete the unnamed footer section where the Grand Total: label was located.
Select the Footer: qryInventory-SumOfUnitsInStock section, and press Ctrl+V to paste the label. Figure 25.29 shows the final design in Page Design view.
Figure 25.29. The final design of the Inventory.htm page includes units and value subtotals by category, as well as grand totals for all categories.
Open the report in We
Page Preview to display the summary version of the page (see Figure 25.30).
Figure 25.30. Web Page Preview displays the eight subtotals and grand totals for units in stock and inventory value.
The Inventory link to the Inventory.htm page is in the Data25.mdb file located in the \Seua11\Chaptr25 folder of the