In this chapter, you’ve learned how to create a client report from scratch in Design view, quickly build simple client and web reports using the Report command, create a client report using the Report Wizard to get a jump start on your work, and use Layout view to modify an existing web report. Now, it’s time to learn how to create web reports completely from scratch in Layout view without the assistance of the Report command. In this section, you’ll create a new web report from scratch that lists the vendor address information in the Back Office Software System web database.
If you want to follow along in this section, open the BOSSDataCopy2.accdb web database. Click the Blank Report button in the Reports group on the Create tab. Access 2010 opens a new blank web report in Layout view with the field list displayed on the right, as shown in Figure 17-38.
The web report does not yet have a record source, so no fields are displayed in the field list. You can click Show All Tables in the field list to display a list of all tables in this web database, but you want to use the saved qryrptXmplWebAddressBook web query that contains all the fields you need from the tblVendors table. Click the Property Sheet button in the Tools group on the Design tab, click the All tab, and then select the qryrptXmplWebAddressBook web query for the Record Source property. Switch back to the field list by clicking the Add Existing Fields button in the Tools group on the Design tab to show only the eight fields in the web query, as shown in Figure 17-39.
You should start this new report by entering a title, so click the Title button in the Header/Footer group on the Design tab. Access places a label control near the upper-left corner of the report and enters the name of the report as Report1. Click inside this label, highlight the existing characters, and change the text to Vendor Address Book. Press Enter to save the new title in the control. Select the empty cell to the left of the title label and press Delete to remove it from the report grid.
Note
In web reports, you are not allowed to have any spaces in control names. Whenever you place a new control on the report grid, Access always gives the control a default name with no spaces. If you place a field from the Field list that includes a space in the name on the report grid, Access adds an underscore (_) in place of every space in the control name.
For this vendor web report, it would be useful to group the vendors by the first letter of the vendor name. You can accomplish this task by grouping on an expression instead of using a field name. Before you create the grouping specification, let’s first add the VendorName field to the report grid so you can see the effect on the record display of adding this grouping level. Click the VendorName field in the Field list, drag the field onto any part of the report grid below the title label, and release the mouse. Access creates a new Vendor Name label below the title and a VendorName text box control below the Vendor Name label. You’ll notice Access displays the vendor names in order of their VendorID field.
Click the Group & Sort button in the Grouping & Totals group on the Design tab to open the Group, Sort, And Total pane. Click Add A Group and then click Expression at the bottom of the Select Field box. Access opens the Expression Builder, where you can build an expression you want to use for the grouping level. Enter the expression Left([VendorName],1) in the Expression Builder and then click OK. This expression uses the Left function to determine the first letter of the vendor name. Access now displays the first letter once for every group of corresponding vendor records in the tblVendors table in a new control and pushes the VendorName field far to the right, as shown in Figure 17-40. If you click this new control and open the Property Sheet window, you’ll see that Access names this new control AccessIntervalControl and uses the expression you defined in the Expression Builder for the Control Source property. You’ll also notice that Access groups the vendor records by the first letter of their name.
Figure 17-40. After you add your expression as a group level, Access adds a new control to display the first letter of the vendor names to the report grid.
Access made the width of the new grouping expression text box much wider than it needs to be, which means the VendorName field is now too far to the right. Click the AccessIntervalControl text box, move your mouse pointer to the right edge of the highlighted control until it becomes a double-sided arrow, and then drag the control to the left until the control is about one quarter of an inch in width. After you reduce the width of this control, Access moves the VendorName field back towards the left side of the report layout.
In Layout view, it’s hard to see where the different report sections begin and end. When you create the expression group level, Access creates a header for this group, but because you see live data in Layout view, the actual design layout can sometimes be hard to visualize. Right now, it almost looks like the expression control is in the report’s Detail section. Although you can’t confirm this by switching to Design view with web reports, Access created a new grouping level for this expression and inserted the AccessIntervalControl text box into that Group Header section. In the grouping specification on the Group, Sort, And Total pane, verify that the following options are set for the specification you just created:
Group On Expression, Ascending, By Entire Value, With No Totals, With Title Click To Add, With A Header Section, Without A Footer Section
You also need to add a sort on the VendorName field because if you look closely at Figure 17-40, Access grouped the vendor records by the first letter, but the records inside each group are still ordered by their VendorID field. Click the Add A Sort button in the Group, Sort, And Total pane and set the following options for VendorName:
Sort By VendorName, With A On Top, By Entire Value, With No Totals, With Title Click To Add, Without A Header Section, Without A Footer Section
Inside Out: Save a Step by Choosing Add A Group Instead of Add A Sort
When you click Add A Group and use a field name for your grouping specification, Access automatically builds a Group Header section and adds the field to the report layout. If you instead first create a sorting specification by clicking Add A Sort on the same field, Access won’t add the field to the report layout. If you change a grouping specification to Without A Header Section, Access moves the field into the Detail section for you. This saves time having to find the field in the field list and add it yourself. In the example we just showed you, we used an expression as a grouping specification, but keep this tip in mind when you create a grouping level using a field name.
Now that you have all your grouping and sorting set up, you need to add the remaining fields onto the report. Close the Group, Sort, And Total pane so you can see more of your report layout. If necessary, click the Add Existing Fields button in the Tools group on the Design tab to open the field list again. Click the Vendor Name label control on the report grid before continuing with the next step. Double-click the Address field in the field list and Access places a new label control and text box control on the report grid, as shown in Figure 17-41.
Access places the Address label the right of the Vendor Name label. Access also places the Address field to the right of the VendorName field. We had you first select the Vendor Name label before double-clicking the Address field in the Field list because Access can potentially place your new controls on the grid in many different sections based on what control you currently have selected in the report layout. Here are the locations where Access places the new Address controls based on your starting focus point:
If you select the title label control, Access places both Address controls beneath the title label in the Report Header section.
If you select the empty cell to the left of the Vendor Name label, Access places both Address controls in the Page Header section.
If you select the Expression group control, Access places the Address label in the Page Header section and the Address field in the Detail section, but both controls are to the left of the Vendor Name controls.
If you select the empty cell to the right of the expression group control, Access places both Address controls in the Group Header section.
If you select the empty cell to the left of the VendorName field, Access places both Address controls into the Detail section and pushes all other controls farther to the right.
If you select the VendorName field, Access places both Address controls into the Detail section, but both controls are beneath the VendorName field.
As you can see, the locations and sections of where Access places your new controls can vary widely based on your starting position. Here again, it can be hard to tell in what sections of the report Access places these new controls when you’re viewing the web report in Layout view.
Inside Out: Add Color to Report Sections in Layout View to View Their Positions
If you find it difficult not seeing where each report or group section begins and ends while viewing reports in Layout view, we find it helpful to temporarily apply a background color to the different sections. By adding different background colors to the sections of the report, you’ll have an easier time understanding where Access lays out the controls in Layout view when you’re creating and moving controls. You can remove the temporary background colors from the sections after you complete your report design changes.
Now that you have the VendorName and Address fields in place, you should add the remaining fields to the report grid. (Skip the Address2 field for the moment.) Placing controls onto the web report grid when you have multiple report sections can be a little tricky, as you’ve just seen. If you find it a little too difficult using the double-click procedure to add fields to the report grid from the Field list, you can use the drag procedure instead. Click the City field in the Field list, drag it onto the report, and place it to the right of the Address text box. When you have it positioned correctly, Access displays an I-bar to the right of the Address text box control, as shown in Figure 17-42. Access places the City label in the Page Header section, the City field in the Detail section, and lines up the controls to match the VendorName and Address fields.
In Figure 17-43, you can see where Access places the City controls after you complete the drag procedure. You might find using the drag procedure for adding fields to the report grid in Layout view easier than using the double-click technique that we first showed you.
Using the technique you just learned, drag the fields from the Field list to the report grid to the right of the City field in the following order: State, PostalCode, Active, and VendorID. (Make sure you skip the Address2 field.) After completing these changes, you can close the Property Sheet window to see more of the report layout. After you finish, click the arrow below the View button in the Views group on the Design tab and click Print Preview to see the result, shown in Figure 17-44. Before you go on, you might want to save the report and name it rptVendorAddressBook. You can find this web report at this stage saved as rptXmplVendorAddressBook1 in the sample database.
Inside Out: Splitting and Merging Cells
In Chapter 14, you learned how to split cells vertically, split cells horizontally, and merge cells in web form control layouts. You can also use these options in web reports as well to further customize the size of your web report controls with one exception—you cannot split cells vertically in web reports. You’ll notice, in web reports, that Access dims the Split Vertically command in the Merge/Split group on the Arrange tab and on the shortcut menu when you right-click a control on the web report layout.
When you’re designing a web report in Layout view, Access 2010 automatically places any controls that you add to the report inside a control layout. As you learned in Chapter 14, control layouts help you to align and position controls on reports and forms. You can think of a control layout as being similar to a table in Microsoft Word or a spreadsheet in Microsoft Excel. When you widen or narrow one control in a column, you change the width of any other controls in that column that are part of that control layout. Likewise, when you increase or decrease the height of a control, you’re changing the height of all the controls on that row.
When you are designing client reports, using control layouts is optional; however, all controls in web reports must exist inside control layouts to be web-compatible. There are two kinds of control layouts in Access 2010—stacked and tabular. In a stacked control layout, Access “stacks” bound controls for different fields in a column and places all the labels down the left side. In client reports, you can have multiple sets of stacked controls within a section, but not in web reports. Any controls (including associated labels) in a stacked layout must all be in one section. In the web report you’ve built thus far, Access has placed the title label and the two empty cells controls to the right of the label in a stacked layout. When you manually drag fields from the Field list in a blank web report, Access creates stacked layouts.
In a tabular control layout, Access places bound controls horizontally with labels along the top as column headings—much like rows on a spreadsheet. A tabular control layout can include controls in different sections of a report—for example, the labels can appear in a Header section and the data controls in the Detail section. In client reports, you can convert the stacked layout for fields in the Detail section into a tabular layout, but in web reports, you have no control over changing layout styles. When you have a web report in Layout view, you’ll notice that both the Stacked and Tabular control layout buttons in the Table group on the Arrange tab are dimmed. In the web report you’ve built thus far, Access has placed the field labels, the expression group text box, and the field text boxes in a tabular layout across the Page Header, the Group Header, and the Detail sections.
When you are working with control layouts, you have to be careful when resizing controls because each change you make usually results in changing other controls. Before you begin resizing controls in the web report you’ve been building, let’s change the print margins. Switch back to Layout view and then click the Page Setup button in the Page Layout group on the Page Setup tab. Change the Top, Bottom, Left, and Right margins all to 0.5. Now that you’ve settled on the print margins, you can begin the process of resizing the controls in the control layouts and see if your controls cross over the print margins.
The text box controls for the VendorName, Address, and City fields are not wide enough at this point, and the State, PostalCode, Active, and VendorID fields are too wide. You can see that the VendorName and Address fields are not wide enough because these fields won’t allow the data to fit on one line for all records. Access expands the height on those records that have more characters. Start by clicking the VendorName field text box, move your mouse to the right edge until it becomes a double-sided arrow, and then click and drag the edge of the field to the right until the control is about 1.75 inches in width. You’ll notice that when you resize the VendorName text box in the Detail section, Access also resizes the other controls in the same column—the Vendor Name label in the Page Header section and the expression group control in the Group Header section. All these controls across the three sections are interconnected through the control layout. You can see the advantage of resizing controls when using control layouts; however, you have also have to be careful that you might resize controls in other sections.
Note
You can also resize controls in Layout view by opening the Property Sheet window and manually typing in new values for the Width and Height properties.
Select the Address field now and resize this control to about 2.5 inches in width. You need this control to be wider than all the other controls because you’ll use it to display not only the Address field data, but also the Address2 field data. While you still have the Address field selected, open the Property Sheet window, and then change the Control Source for this field to the expression =[Address] & “ ” & [Address2]. This expression concatenates both address fields into one control on the report. After you change the Control Source for this field, you’ll notice Access now displays a #Type! error in the field on the report grid, as shown in Figure 17-45. Access displays this error because it cannot figure out what data to display in the control. The Control Source uses the Address field in the report’s record source as part of the expression; however, the name of this text box control is also Address.
Access is confused at this point because it thinks you want to use the contents of this control in the expression. In essence, you’ve created a circular reference error. To correct this problem, change the Name property to something other than Address; for example, change it to txtAddress. After you change the Name property, Access displays the data in the control again. While you still have the Property Sheet open, left-align the data in the txtAddress control.
Figure 17-45. Access displays an error in the address control when it can’t resolve the Control Source expression.
The City field needs to be only a little wider than it currently is, so expand the width of this field to about 1.2 inches. If you scroll to the right of the report layout, you’ll see that Access pushed the PostalCode, Active, and VendorID controls past the print margin after you’ve expanded the width of the first three controls because all the controls are in a common control layout. You need to reduce the width of the remaining fields to fit everything back onto one page again. Start by clicking the State field text box, move your mouse to the right edge until it becomes a double-sided arrow, and then click and drag the edge of the field to the left until it is just wide enough to still display the text in the State label control. To make additional room for the remaining controls, change the caption of the Postal Code label control to Zip and reduce the width of the control to about 0.6 inches. We’ll be hiding the Active and VendorID fields, so we don’t need the labels for these two controls. Select the Active? and Vendor ID labels and press Delete to remove them from the control layouts. Now that you’ve removed the labels, reduce the width of both the Active and VendorID fields until all the controls fit inside the print margins. Finally, set the Visible property to No for both the Active and VendorID fields on the Property Sheet window.
After you finish, click the arrow below the View button in the Views group on the Design tab and click Print Preview to see the result, shown in Figure 17-46. You can find the web report at this stage saved as rptXmplVendorAddressBook2 in the sample database.
All your fields are in place, so now you can add a control for counting the number of vendors. Switch back to Layout view, select either the VendorName text box control or the Vendor Name label, click the Totals command in the Grouping & Totals group on the Design tab, and then click Count Records from the drop-down list, as shown in Figure 17-47.
Figure 17-47. Click the Count Records command to create controls to total the number of vendor records.
Access places a new control in the expression Group Footer section, in the same column as the VendorName field, and a new control in the Report Footer section, as shown in Figure 17-48. Note that we scrolled down to the bottom of the report layout in Figure 17-48 so you could see the control in the Report Footer section. (The Count Values option, shown in Figure 17-47, would also create the same controls and show the same results for text fields. Use Count Values when you need Access to count the individual values of number or currency fields.) You don’t need the extra control counting the number of vendors for each letter, so select that new control and press Delete to remove it from the report layout. (The control showing a number less than 16 is the one you want to remove.) After you remove the control, you’ll notice Access leaves the empty cell behind, which is now creating extra unneeded space between each group. Select the empty cell and press Delete to remove the empty cell and collapse the extra space separating the groups.
Figure 17-48. Access creates an expression to count the number of vendors for each letter and another to count the total number of vendors in the report.
Select the total number of records control in the Report Footer section now and then open the Property Sheet window. Access set the Control Source for this control to =Count(*). This expression totals the number of vendor records for the entire report. (If you chose the Count Values option, Access sets the Control Source to =Count([VendorName]) for this control.) Tab into or click in the Control Source property and change the expression to =“Number of vendors: ” & Count(*) to display a descriptive message explaining what the number represents. You’ll notice that Access now aligns the text in this control to the left because you’ve included a string concatenation in the expression.
Inside Out: Use a Filtered Web Query When Designing Web Reports to Reduce Scrolling
When you work in Layout view with web reports, you’ll probably find yourself scrolling up and down the report layout quite often to see controls in different sections. To help reduce the amount of scrolling, you can use the Property Sheet window to select the control you want to work with or you can temporarily base the web report on a web query that limits the number of records returned. When you have fewer records to look at, you’ll have to scroll less in the report layout to see controls in the different sections.
When the report is printed, it would be nice to have a page number at the bottom of the page. Click the Page Numbers button in the Header/Footer group on the Design tab to open the Page Numbers dialog box, discussed in Completing the Report. Select the following options in the Page Numbers dialog box—Page N Of M, Bottom Of Page [Footer], Alignment set to Center, and Show Number On First Page. Click OK to close the Page Numbers dialog box. Access places a new control in the Page Footer section that spans the width of the entire report.
Note
You won’t immediately see a difference in the report in Layout view after you add a page count to a report that spans multiple pages. If you scroll to the bottom of the report, you’ll see the control says Page 1 Of 1 even if you know the report spans more than one page. In Layout view, Access does not count the number of pages because it is not actually formatting the pages for printing. If you switch to Print Preview, you can see the correct page numbers displayed on each page.
Your web report is functional right now, but with a little formatting, you can make it look more professional and easier to read. Web reports do not support Line controls; however, you can simulate the look of lines by adding gridlines to web reports. Switch back to Layout view for your report, select any of the label controls for the fields in the Page Header section, and then click the Select Row button in the Rows & Columns group on the Arrange tab to select the entire row of controls. Now, click the Gridlines command in the Table group on the Arrange tab and click Horizontal from the drop-down list of options, as shown in Figure 17-49.
Access adds gridlines to the top and bottom of each label control and empty cell in the row of controls displayed in the Page Header section. Click the Gridlines command again, click Color (near the bottom of the drop-down list of options), and click the color called Dark Blue, Text 2. Access now changes the color of all those gridlines to a dark blue.
Note
Access does not always display the gridlines around your controls when you view the report in Layout view. We recommend you periodically switch to Print Preview or Report View when you’re creating or editing gridlines to make sure you are getting the look you want with your gridlines.
Scroll down to the bottom of the report and select the control displaying the total number of vendor records—the control named AccessTotalsVendorName1 in the Property Sheet window. Click the Select Row button in the Rows & Columns group on the Arrange tab to select the entire row of controls. Now, click the Gridlines command in the Table group on the Arrange tab and click Top from the drop-down list of options. Access creates a gridline across the top of all these controls. Next, click the Gridlines command again, click Color, and then click the same color as you did previously—Dark Blue, Text 2. Finally, click the Gridlines command one last time, click Width, and then click 1 Pt from the gridline width options. Save your report and then switch to Print Preview to see how your report will print on paper with these changes, as shown in Figure 17-50.
Note
In a previous section, we had you delete the Active and Vendor ID label controls from the Page Header section instead of just hiding the controls. We instructed you to do this because if you set their Visible property to No, you would not see any gridlines around those two controls when you view the report in Print Preview or Report view. Your gridlines across the top would stop after the Zip label control. By removing those controls, you can set gridlines around the empty cells that occupy those spaces.
Now that you’ve completed adding gridlines to your web report, let’s add a few finishing formatting touches to the web report. When the web report is printed, it would be nice to have the date and time the report was printed at the top of the page. Switch back to Layout view and then click the Date And Time button in the Header/Footer group on the Design tab to open the Date And Time dialog box. Select the Include Date and Include Time options in the Date And Time dialog box, and leave the default formatting options. Click OK to close the Date And Time dialog box. Access places two new controls to the right of the title label caption in the Report Header section, as shown in Figure 17-51. You’ll notice that Access reduces the width of the title label control to keep the new date and time controls from spanning over the page margin.
Figure 17-51. Access creates new date and time controls in the Report Header section when you use the Date And Time dialog box.
It would be nice to have the title caption centered in the report and the date and time controls on separate sides. You can make these changes easily by adding another row to the Report Header section. Select the title label caption and then click Insert Above in the Rows & Columns group on the Arrange tab. Access inserts a new row above the title label caption control. Click the new date control that Access created in the previous step, and drag the control into the new empty cell in the upper-left corner of the report layout. Left-align the text in this control after you finish moving the control. Now, click the time control and drag this control to the new empty cell in the upper-right corner of the report layout. Click the title label control, hold down the Shift key, and click the two empty cells where the date and time controls used to be. Click the Merge button in the Merge/Split group on the Arrange tab to merge all these controls into one large control that spans the entire width of the report. Center-align the text in the title label control by clicking the Center button in the Font group on the Format tab. Your Report Header section should now look like Figure 17-52.
Figure 17-52. Your report should now match this figure after moving the controls in the Report Header section.
Perform the following steps to adjust some of the formatting options on your web report:
By default, Access applied a background color to the Report Header section when you clicked the Title command. Let’s remove that color for this web report. Open the Property Sheet window, select ReportHeader from the Selection Type combo box, and then change the Back Color property to Background 1. Access changes the Report Header section to blend in with the rest of the report grid—in this case, the section is colored white.
Click the date control text box in the upper-left corner of the report layout, hold down the Shift key, and then click the time text box control. On the Property Sheet window, change the Font Size to 9 to reduce the font size of these two controls.
Change the Fore Color of all the field label controls in the Page Header section to Text 2.
By default, Access created alternating background colors for the detail records in this web report, which we don’t need. In the Property Sheet window, select Detail from the Selection Type combo box, click the drop-down list of color choices on the Alternate Back Color property, and then select No Color.
Select the expression group control that shows the first letter of the vendor names for this report—the AccessIntervalControl control in the Property Sheet window. Change the Fore Color to Text 2, set the Font Size to 16, and the Font Weight to Bold.
Click the VendorName text box control, hold down the Shift key, and then select each of the field controls except the Active field. In the Property Sheet window, change the Fore Color to Text 1 and the Font Size to 9.
Select the City, State, and PostalCode fields, and then left-align these controls.
Scroll down to the bottom of the report and then select the control that shows the total number of records for this report—the AccessTotalsVendorName1 control, in the Property Sheet window. Change the Fore Color to Text 1, and left-align the control.
In the Property Sheet window, select Report in the Selection Type combo box, and type Vendor Address Book for the Caption property.
Click the Save button on the Quick Access Toolbar to save the changes you made to this web report. Switch to Print Preview to see how your completed web report looks on paper, as shown in Figure 17-53. This web report now looks very close to the rptWebVendorAddressBook web report in the Back Office Software System web database. You can find this web report saved as rptXmplVendorAddressBook3 in the sample database. As you can see, Layout view allows you to quickly create a professional-looking web report in Access 2010.
