Microsoft Access Quick Tutorial

Web based School

Previous Next

Chapter Seven
Producing Output

Access provides several methods of producing output for your data. After you preview your data and adjust the page setup options as necessary, you can send the data directly to a printer. You can print a table, query, form, or report, or portions thereof. You can create a special type of report, mailing labels, and you can merge your data to Microsoft Word.

In addition, you can fax or e-mail your data from Access, or publish your Access forms or reports on the Web.

Mail Labels: Create in Report

A mailing label is a special type of report formatted to repeat across or down a page. You have control over the contents of your mailing label through the placement of bound controls displaying your field data, text labels, graphics, and so on. You can also control the size of the labels and their repetition pattern.

Through the use of queries, you can specify which records will have labels printed for them. The Label Wizard makes it particularly easy to create mailing labels.


1. Click the Reports tab in the Database window; then choose New. Click Label Wizard in the New Report dialog box. Select the table or query that will supply the data for the labels in the Select Table or Query drop-down list box; then choose OK.
2. Select the standard Avery label you want, or choose Customize and specify the label size and type; then choose Next.
To create a custom size label, choose Customize and enter the size and page setup specification.
3. Select the font, size, and color of your text, then choose Next.
4. Create a prototype label by double-clicking any desired fields from the Available Fields list box to the Prototype Label text box. Type any text you want to appear on the label (spaces, commas, and so on), then press Enter to start a new line. To delete a field, select it and press Delete. When your prototype label is complete, choose Next.
5. Select the fields you want to sort your records, with the top field being the primary sort key.
6. ame your label report in the last step of the wizard, then preview it and save it to disk.
If you select the Modify the Label Design option button, then your label opens up in Report Design View.

TIP: You can sort by any field in your underlying table or query, even those that do not appear on your prototype label.

Mail Merge to Word

If one of your tables or queries contains client data, you might want to send a letter to everyone in the list. If you have Microsoft Word installed, you can personalize each letter.

TIP: Create a query to be the source of your mail merges. You can then modify the criteria every time you send a letter (when an address changes, you have a new client, or your printer mangled the first copy). See "Query: Create with Design View" in the Queries and Filters part of the guide.


1. Select the query (or table) in the Database window and choose Merge It with MS Word from the Office Links button's drop-down list.

2. In the first step of the wizard, you can link to an existing document or create a new Word document. If you choose link to an existing document, Access will ask you for the document's location.

3. After you make your choices for the Word document, you will enter Microsoft Word, and the Mail Merge toolbar appears. Move to the location where you want an Access field, click the Insert Merge Field button, and choose the Access field. Type spaces, commas, and any additional text in your letter.

4. Save the letter and click the Merge to New Document (to preview all letters) or Merge to Printer buttons on the Mail Merge toolbar.

When finished, close the Word application.

NOTE: If you Merge to New Document, Word creates a temporary document titled FormLetters1 with the results of the merge. You do not need to save this document when you exit Word. You should, however, save your document with the merge field codes.

Mail or Sending Data

You can send Access tables, queries, or reports through electronic mail instead of printing and mailing through the post office. To send Access data through electronic mail, you need to use either Microsoft Exchange (or another mail system compatible with MAPI--Messaging Application Program Interface) or Lotus cc:Mail (or another mail system compatible with VIM--Vendor Independent Messaging). For additional information, search on "electronic mail" in Microsoft Access Help.

NOTE: To perform this procedure, you must have already installed electronic mail software.


1. Select the object you want to send: a table, query, form, or report in the Database window, or select any portion of a datasheet in Datasheet View.

2. Choose File, Send.

In the Send dialog box, select the format of the data that will be attached to your message; then choose OK. Access opens a mail form with your data attached.

4. Fill in your message form, and send your message.

NOTE: Depending on your Access object (table, query, form, or report), Access allows data to be sent as HTML, ActiveX (.ASP), Excel (.XLS), IIS (.HTX or .IDC), MS-DOS text (.TXT), or Rich Text Format (.RTF) files.

Page Break: Insert in Report

If you want your page to break in the middle of a section, you can insert a page break control on the report. For example, if you have two subreports, you might want to insert a page break between them.

NOTE: You can also use this same procedure to insert a page break in Form Design View if you print your forms.


1. Open the report in Design View.
2. Click the Page Break button on the Toolbox.
3. Click the location on the report design to set the page break. The page break control appears as a small dotted line in your document.

To remove the page break, select the page break control and press Delete.

NOTE: You can also set page breaks with the properties of the sections on your report. Open the report in Design View and double-click the gray bars indicating the detail section or a group header or footer. In the Property sheet, move to the Force New Page property (on the Format tab) and choose to create a page break Before Section, After Section, Before & After, or None to let pages break at the end of a full page.

To try to avoid a page break within sections, change the Keep Together property to Yes. To try to keep all sections (header, footer, and detail) relating to one group together on a page, click the Sorting and Grouping button and change the Keep Together Grouping property to Yes. Access will try to place page breaks to keep a group together. However, if the group is larger than one page, it won't be able to. In this case, choose With First Detail to at least keep the group header with the first record.

Page Numbers: Insert in Report

For long reports, you will want Access to place page numbers on your report. This is especially true if you are handing out the report to various people and everyone needs to refer to specific pages.


1. Open the report in Design View and choose Insert, Page Numbers.
2. In the Format section, click Page N to add the word Page followed by a page number. If you want to include the total number of pages, click Page N of M.
3. In the Position section indicate if you want to put the page number in the header or footer, and in the Alignment section indicate if you want the page numbers left, centered, or right aligned. You can also choose inside or outside pages (for facing page bound documents).
4. If you want to include the page number on the first page, check Show Number on First Page. When finished with your selections, choose OK.

Access places a text box with the appropriate expression on your report. The [Page] code indicates the page number. [Pages] indicates total number of pages. Additional text is enclosed in quotes such as "Page". The Control Source property of the text box shows this expression (and you can press Shift+F2 to see a long expression). To remove the page numbers, click the text box and press Delete.

Page Setup: Print Data Only

If you have a pre-printed form, you can design an Access form to print in the fill-in areas. When you print, you can choose to have Access print only the data.


1. Open the form in Design View.

2. Choose File, Page Setup, and click the Margins tab in the Page Setup dialog box.

3. Check the Print Data Onl_y check box and choose OK.

Now when you print all the records or one record (see "Print Forms" and "Print One Record from Form"), Access will only print the data and not the labels for the fields.

Page Setup: Setting Margins

By default, Access prints with one-inch margins at the top and bottom, and left and right sides of the document. You can use the Page Setup dialog box to change these margins, if necessary. If your document is small, for example, you can increase the margins. If the document is slightly bigger than the page, you might want to decrease the margins.


1. From Design View in a table, query, form, or report, choose File, Page Setup; then click the Margins tab.

In the Top, Left, Bottom, and Right text boxes, specify the margins you want in inches. Then choose OK.

Access saves the page setup information with the design of the object. You might also need to change the size of columns in a table or query to fit everything on a page. (See also "Width of Column" in the Database Essentials part of this guide.) In a form or report, you might need to drag the right edge of the work area in Design View to change the printed width area.

Page Setup: Setting the Orientation

If the document you want to print is wider than it is tall, you can switch to a landscape orientation when you print. Choose Landscape orientation to print the document across the long edge of the page. Use Portrait orientation (the default) to print across the short edge of the page.


1. Choose File, Page Setup; then click the Page tab.

In the Orientation area, select Portrait or Landscape.

If you need to change the size of the paper (to Legal, for example) select the new size from the Size drop-down list.

4. If you need to select another paper tray, select it from the Source drop-down list. When finished adjusting settings, choose OK.

Print Forms

Although reports are the primary object for printing, you can also print information on forms. You can print one record (see "Print One Record From Form") or the entire data source. Depending on your starting location, you can print the Form or Datasheet View.


1. From the Database window, right-click a form name and choose Print to print the form or Print Preview to go to preview first.

From Print Preview or Form View, click the Print button to print the form.

From Datasheet view of the form, click the Print button to print the datasheet.

Print Labels Using Dot Matrix Printers

Some people print labels to dot matrix printers using tractor-feed stock. The best way to do this is to create a printer file that contains the page setup appropriate for this task. If the paper is a predefined label stock, an easier alternative is to use the mailing labels feature. (See "Mail Labels: Create in Report.")


1. Click the Start button on the Taskbar, then choose Settings, Printers.

Right-click the printer you will use, then select the Properties command from the shortcut menu.

Click the Paper tab, and in the Paper Size section click the Custom icon.

Enter the size of your label in the User Defined Size dialog box. Double-click OK. The width measurement extends from the left edge of the leftmost label to the right edge of the rightmost label. The length is measured from the top of the first label to the top of the second.

A label layout is a report layout that has no header or footer, only a detail part. If you are creating a label from scratch, then choose File, Page Setup to open the Page Setup dialog box to set the following:

  • Set the Page tab to the Use Specific Printer; the printer Source to User-Defined Size; and the source for label stock to Tractor, Cassette, AutoSelect tray, or whatever is appropriate.
  • Set the Columns tab to Same as Detail in the Column Size section.
  • For a layout with more than one label across, on the Columns tab, enter the following: the number of labels across in the Number of Columns text box, the amount of space between the bottom of one label and the top of another in the Row Spacing text box, the space between the right edge of one label and the left edge of the next in the Column Spacing text box, and specify whether the Column Layout is Down, Then Across or Across, Then Down.

Print One Record from Form

Sometimes you only want to get the details from one form's record. Perhaps you want to find missing information on a client or use the form for directions to a meeting. If you want to print only one record, use the record selector at the left edge of the form.

NOTE: If the record selector is not available, you need to set the Form's Record Selectors property to Yes. To set this property, double click the Form/Report Selector in the upper-left corner of the form to open the Form property sheet, go to Record Selectors (on the Format tab) and choose Yes.


1. Open the form in Form View.

2. Click the record selector on the left side of the form.
Choose File, Print, Selected Record(s) and choose OK.

Print One Record from Form: Create Command Button

If you often print one record, consider adding a button on the form intead of using the procedure mentioned in "Print one Record from Form."


1. Open the form in Design View. Make sure the Control Wizards button is selected.

2. Click the Command Button tool in the Toolbox and then click where you want the button to appear on the form.

In the Categories list choose Record Operations, and in the Actions list choose Print Record.

Follow the instructions on the next two steps on the Command Button Wizard dialog box to identify the button; then choose Finish.

Now when you are in Form View, you can click the command button you added to print just the current record.

Print Preview

The Print Preview mode provides a view of your datasheet, form, query, or report as it would print to your current printer. It is always a good idea to preview your printed output before printing to find if the report contains the wrong information or is not the desired format. Especially for large reports, you'll want to use Print Preview first to avoid killing a tree.


1. Open a table, query, form, or report in either Design, Form, or Datasheet View.

Click the Print Preview button on the toolbar.

3. Use the toolbar buttons to switch your preview view, and click the page to switch between multiple or zoomed view and single page 100 percent view.

If you preview a form from Design or Form View, your preview is in Form View. If you preview a form from the Datasheet View, then your preview is in Datasheet View. If you preview a form selected in the Database window, then the Default View property controls the view you see in Preview mode. To change the default view of the form, double-click the Form/Report Selector in Design View and choose an option in the Default View property.

The following table shows you how to perform various tasks in the Print Preview view.

Print Preview Tasks

To Do This Press This
To open the Print dialog box P or Ctrl+P
To open the Page Setup dialog box S
To zoom in or out on a part of the page Z
To cancel Print Preview or Layout Preview C or Esc
To move to the page number box; F5 then type the page number and press Enter
To view the next page (when Fit To Window is selected) Page Down or down arrow
To view the previous page (when Fit To Window is selected) Page Up or up arrow
To scroll down in small increments Down arrow
To scroll down one full screen Page Down
To move to the bottom of the page Ctrl+down arrow
To scroll up in small increments Up arrow
To scroll up one full screen Page Up
To move to the top of the page Ctrl+up arrow
To scroll to the right in small Right arrow
To move to the right edge of the page End or Ctrl+Right arrow
To move to the lower-right corner of the page Ctrl+End
To scroll to the left in small increments Left arrow
To move to the left edge of the page Home or Ctrl+left arrow
To move to the upper-left corner of the page Ctrl+Home

The Layout Preview command on the View menu enables you to see some representative data (See also "Print Preview: Layout.") From the preview window, you can merge data with Word, publish to a Word document, or analyze data with Excel. These three options are available as toolbar buttons.

Print Preview: Layout

Access 97 offers you two different types of previews: Print Preview and Layout Preview. In the former instance, you see everything that will print to your printer, each page and all of the data contained therein. For long print jobs, it can take a while for your computer to process this information. If you want to view a small group of your records in preview, you can see an example layout in the Layout Preview mode. In this mode, you see just enough data to get a feeling for all of the sections of a report.


1. With your report in Design View, click the View button on the toolbar.

2. Select the La_yout Preview command.

CAUTION: Layout Preview can be misleading because you don't get a view of all of your data. If you are using a query that contains para-meters, for example, Layout Preview will not detect this and show you a truly representative data set. For reports that don't take a long time to process, you are better off using the Print Preview view to see your reports.

Print Reports

Reports are the best way to get data outputted to a printer. Although you can print tables, queries, and forms, none of the other objects offer the formatting and printing options that reports do. For example, only in reports can you get grouped data with subtotals and the detail data as well. (See "Reports: Grouping" in the Forms and Reports part in this guide.) You can also print reports from various locations.


1. If you are in the Database window, right-click the report name and choose Print.

To select specific pages, choose the number of copies, make your choices on the Print dialog box, and choose OK.

NOTE: To print a report without specifying print options, in Report Design View or Print Preview, you can click the Print button on the toolbar.

If you want to interrupt printing and you are fast, click the Cancel button that appears in the Printing dialog box. You also might be able to double-click the Printer icon in the taskbar and cancel the print job by choosing Document, Cancel Printing in the Printer window.

Print Selected Pages

You can print multiple pages of a report or form. On a form or columnar report, if you want to print different records, add a page break at the bottom of Design View. (See "Page Break: Insert on Report.") Then the page numbers correspond to the record numbers. An alternative is to go to each record you want to print in Form View and print that record. (See "Print One Record From Form.")


1. Open a form or report in Design View.

2. Choose File, Print.

3. In the Pages area, type the first page in From and the last page in To; choose OK.

Print Table or Query

Instead of printing a report, you can print the Datasheet View of a table or query for quick results. Although you have little control over the formatting (see "Datasheet: Appearance Change" in the Table and Database Design part of this guide), all you might need is the grid produced from Datasheet View.


1. Open the table or query in Datasheet View.

Click the Print button.

TIP: You can also print from the Database window. Right-click a table or query and choose Print.

NOTE: Access automatically adds a header with the table or query name centered, the date right justified, and the page number centered in a footer. If you don't want to print the header and footer, choose File, Page Setup; click the Margins tab; and uncheck the Print Headings check box. Then follow the steps to print your table or query. You cannot edit the header and footer on a table or query.

Printing: Multiple Copies

Before you begin printing your document, you can specify how many copies you want to print. If you want to print multiple copies of a multi-page document, be sure to preview before you print.

You also can choose whether or not to collate the documents as they are printed. Normally, you will want to choose the Collate option, which prints all pages of a document before it prints the document again.

(See "Print Preview" before you complete this task.)


1. Choose File, Print.

2. Type the number of copies you want to print in the Number of Copies text box.

Be sure that the Collate check box is selected; or, if you don't want your printouts to collate, deselect the Collate check box. Click OK to begin printing.

Programming Events: Print

You can control printing to a fine degree by adding programming to the print events for a report. You add programming to print or not print certain sections or controls on your report, depending on choices you make on dialog boxes or the underlying values of the data.

One of the report print events is On No Data. If the report doesn't have any data to print, there is no sense printing a blank report. The following steps show you how to display a dialog box and cancel the printing (or previewing) of a report.


1. Open the report in Design View and double-click the Form/Report Selector to open up the Property sheet.

Scroll down to the On No Data property (on the Event tab), click the build (...) button, and double-click Code Builder in the Choose Builder dialog box. The insertion point is between a Sub and End Sub statement.

3. Type MsgBox "No data for report. Report will not print." and press Enter. This will display a message for the user. You can type any text you want within the quotes.

4. Type Cancel = True. If you look at the first line of your code, you will see (Cancel as Integer). The procedure is looking for a variable called Cancel; this statement is telling Access to not print the report.

Close the window and try testing the procedure. This works best if you have a query underlying the report and you go to the query and add some criteria that wouldn't return any records.

The other print event associated with the report is On Page which will trigger before the page is printed but after the Format events for report sections. The Report and Group Header and Footer and Detail sections also have print events. To see help on the events, open a Property sheet, click the Events tab, select one of these sections, and move to one of the properties (On Format, On Print, or On Retreat). Then press F1.

NOTE: For an example of the On Format property in the sample Northwind database, open the Sales by Year report and look at the On Format property for the Detail section. The programming includes
If Forms![Sales by Year Dialog]!ShowDetails = False Then Cancel = True
When you open this report in Print Preview or attempt to print it, a form (Sales by Year Dialog) opens with a check box named ShowDetails. If the user unchecks the ShowDetails box, the detail section will not print.

Web Page: Publish

Some people will begin their foray into database publishing to the Web by visiting the new Web Publishing Wizard. This wizard guides you through the process of Web page and site creation. You start the Web Publishing Wizard by selecting the Save As HTML command from the File menu. The Web Publishing Wizard can output datasheets, forms, and reports as static or dynamic Web documents using template files. It can create a home page that has links to your other documents. You can use the Web Publishing Wizard to copy the files and folder created to your Web server.

TIP: Microsoft Office contains a Web Fast Find Search page that you can use to search for files on an intranet. You can locate files by keywords. Consider including this page in any site you create with the Web Publishing Wizard. Consult your administrator to get a copy, or learn about the location of this page on your intranet.


1. Choose File, Save As HTML with a datasheet, form, or report open; click the I Want to Use a Web Publication Profile check box; and select that publication if you have already created a Web page or set of pages in the format you want using the wizard (if you don't have any Web pages already created, this option will be grayed out). Choose Next.

2. Click either Select or Select All in the check box to select the table(s), query(s), form(s), and report(s) you want to publish. Choose Next.

3. Select the HTML template document you want to use, then choose Next. You can select the type of document you want to create: Static HTML, Dynamic HTX/IDC, or Dy_namic ASP.

On the next one or two screens (depending on your choice in Step 3), enter the location of the folder (and server if prompted) that you want to save your files to.

5. Click the Yes, I Want to Create a Home Page check box if you want that feature; name that page (Default is the default); then choose Next.

On the final page of the wizard, select the Yes, I Want to Save the Answers to the Wizard check box if you want to create a Web publication profile; enter the name in Profile Name; then choose Finish.

Access creates the pages you specified from each object. It also creates, if specified, the publication profile.


Previous Next