Home

Printing

 

Table, Query, and Form Printing

 

Introduction to Printing

Printing consists of writing or drawing, on a piece of paper, the values held by an object of a database. This means that you can print from a table, a query, or a form. Before doing this, of course the object must exist and it must have records.

To print from a table, a query, or a form, in the appropriate section of the database window, right-click the object and click Print... When you do this, the document is sent directly to the printer.

 

Printing Records

The tables, queries, and forms provide a fast means of printing records without having to do any particular design. Instead of directly sending a document to a printing, you can choose what records to print. To do this, you must first open the table, query, or form.

To print a particular record, if you are using a table or a query, click any cell in that record. To print a record from a table, first navigate to that record. In both cases, on the main menu, click File -> Print... This would display the Print dialog box. From it, you can click the Selected Record(s) radio button:

To print more than one record, first select them and then proceed in the same way.

Report Fundamentals

 

Introduction

While the table, the query, and the form provide a fast means of printing, their result may not be particularly appealing. The most convenient object to print from a database is a report. A report is an object in its own right. It is created and saved like one of the objects we have saved so far. Like the other Microsoft Access objects, which excludes views and stored procedures, the reports have their own section in the Database window, and it is labeled Reports.

Creating a New Report

Like a table or a form, there are various ways you create a report. From the main menu, you can click Insert -> Report. From the Reports section of the Database window, you can click the New button or, on the Database toolbar, you can click the arrow of the New Object button and click Report. Any of these actions would display the New Report dialog box:

You can create a report that would be used to print simple text that is not related to any data source. To create such a report, when in the New Report dialog box, you can click Design View, make sure the combo box is empty, and click OK. Otherwise, the New Report displays the same options as the New Form dialog box.

After creating a report, as we will see in the next few sections, to keep it in your database, you must save it by giving it a name. The name of a report follows the rules and suggestions we have applied to tables, queries, and forms so far.

Practical LearningPractical Learning: Using Different Options to Create Reports

  1. Start Microsoft Access.
    Create a blank database and name it Yugo National Bank
  2. In the Tables section of the Database window, Click the New button
  3. In the New Table dialog box, double-click Table Wizard
  4. In the first page of the wizard, click Personal
  5. In the Sample Fields list, double-click AccountTypeID, AccountType, and Description
  6. Click Next
  7. In the second page of the wizard, change the name of the table to AccountTypes and click Next
  8. Click Finish
  9. Enter a few records as follows:
     
    Account Type ID Account Type Description
    1 Checking  
    2 Saving  
    3 Certificate of Deposit  
  10. Close the table and, in the Tables section of the Database window, click AccountTypes to make sure it is selected
  11. On the Database toolbar, click the arrow of the New Object button and click AutoForm
  12. Save the form as AccountTypes
  13. Right-click the form and click Form Header/Footer
  14. Using the Command Button Wizard to add a button to the Form Footer section that would be able to close the form. Set the button's name to cmdClose
  15. Adjust the design of the form as you see fit. Here is an example:
     
  16. Close the form
  17. When asked whether you want to save it, click Yes
  18. In the Database window, click AccountTypes to make sure it is selected.
    To create a new report, on the Database toolbar, click the arrow of the New Object button and click AutoReport
  19. After viewing it, close the report
  20. When asked whether you want to save the report, click Yes and click OK to accept the suggested name of the report
  21. In the Tables section of the Database window, Click the New button
  22. In the New Table dialog box, double-click Table Wizard
  23. In the first page of the wizard, click Personal
  24. In the Sample Fields list, double-click AccountTypeID, AccountType, and Notes
  25. In the Fields In My New Table list, click AccountTypeID and click Rename Field
  26. Type DepositTypeID and click OK
  27. In the Fields In My New Table list, click AccountType and click Rename Field
  28. Type DepositType and click OK
  29. In the Fields In My New Table list, click Notes and click Rename Field
  30. Type Description and click OK
  31. Click Next
  32. In the second page of the wizard, change the name of the table to DepositTypes and click Finish
  33. Switch the table to Design View
  34. Change the captions of the DespositTypeID and DepositType to Deposit Type ID and Deposit Type respectively
  35. Close the table and, when asked whether you want to save it, click Yes
  36. To create a new form, on the main menu, click Insert -> Form
  37. In the New Table dialog box, click Form Wizard
  38. In the combo box, select DepositTypes and click OK
  39. In the first page of the wizard, click the button that selects all records and click Next
  40. In the second page of the wizard, accept the Columnar option and click Next
  41. In the third page of the wizard, accept the Standard option and click Next
  42. In the fourth page, accept the suggested name of the form as DepositTypes and click Finish
  43. Enter a few records as follows:
     
    Deposit Type ID Deposit Type Description
    1 Cash  
    2 ATM Deposit made at the Automated Teller Machine
    3 Personal Check Check written by the customer or written to the customer
    4 Business Check Examples include payroll
    5 Direct Deposit From the customer's employer or a customer's customer
    6 Transfer Money transferred to this account from another account
    7 New Account  
  44. Close the form
  45. To create a new report, on the main menu, click Insert -> Report
  46. In the New Report dialog box, click Report Wizard
  47. In the combo box, select DepositTypes and click OK
  48. In the first page of the wizard, click the button that selects all records and click Next
  49. In the second page, make sure no grouping is selected and click Next
  50. In the third page, click make sure the combo boxes are empty and click Next
  51. In the fourth page, click Columnar and click Next
  52. In the fifth page of the wizard, click Formal and click Next
  53. Accept the name of the report as AccountTypes and click Finish
  54. After viewing the report, close it
  55. While the Tables section of the Database window is displaying, right-click AccountTypes and click Save As...
  56. Set the name of the table to ChargeReasons and click OK

  57. Right-click ChargeReasons and click Design View
  58. Rename the fields as follows:
     
    Old Name New Name New Property
    AccountTypeID ChargeReasonID Caption: Charge Reason ID
    AccountType ChargeReason Caption: Charge Reason
    Description   Data Type: Memo
  59. Close the table
  60. When asked whether you want to save it, click Yes
  61. To create a new form, on the main menu, click Insert -> Form
  62. In the New Form dialog box, click AutoForm: Columnar and, in the combo box, select ChargeReasons
     
  63. Click OK
  64. On the Form View toolbar, click the Save button
  65. Accept the suggested name of the form and click OK
  66. change the values of the records as follows
     
    Charge Reason ID Charge Reason Description
    1 Monthly Charge Applied every month to all accounts
    2 Overdraft Applied if a customer's account remains negative for 72 hours
    3 Miscellaneous Charge This charge could be applied for any reason the management judges necessary. If/When applied, an application should be noted and the customer must be notified.
  67. Close the form
  68. To create a new report, on the main menu, click Insert -> Report
  69. In the New Report dialog box, click AutoReport: Columnar
  70. In the combo box, select ChargeReasons and click OK
  71. Close the Report
  72. When asked whether you want to save it, click Yes
  73. Accept the suggested name of the report and click OK
  74. Close the report

Programmatically Creating a Report

To programmatically create a report, you can call the CreateReport() method of the Application object. The syntax of this method is:

CreateReport([database[, reporttemplate]])

Both arguments of this method are optional. Here is an example of calling it:

Private Sub cmdReport_Click()
    Dim rptMaintenance As Report

    Set rptMaintenance = CreateReport
End Sub

If you call the method like this, it generates a temporary report named Report1 but it doesn't save it. After the report has been generated, it appears in Microsoft Access. If you want to keep it, you can save it. If fact, if you try closing it, you would be asked whether you want to save it. If yes, you would be asked to give it a name.

The first argument of this method is the name of the database that will receive the report. If the report will be added to the current database, you can omit this argument. The second argument is the name of an existing report that you want to use as template. If you specify this argument, you must make sure that you provide the name of a report. Otherwise, you can omit it.

Designing a Report

 

Introduction

At first glance, the design of a report follows the same approach as that of a form. Both use the same Windows controls either from the Toolbox or from the More Controls button. The techniques of selecting, copying, moving, and resizing controls are the same.

Practical LearningPractical Learning: Designing a Report

  1. Open the Solas Property Management database you started in Lesson 15
  2. In the Database window, click Tables and click Invoices
  3. To start a new report, on the main menu, click Insert -> Report
  4. In the New Report dialog box, make sure Design View is selected and Invoices is selected in the combo box box.
    Click OK
  5. Save the report as Invoices
  6. Using the Field List, the Toolbox, the Properties window, and toolbars, design the report as follows:
     

     
  7. Save the report and preview it
     

     
  8. Close the report

Features of a Report

As compared to a form, a report has some custom properties to accomplish its role. For example, while a form can be equipped with one Form Footer and one Form Header sections, a report can have as many sections as you judge necessary. This is because a report uses a characteristic known as grouping. Imagine that you want to print a list of students with selected information such as their names. To better organize the list, you can divide the group by gender.

To create a grouping in a report, if you are using the Report Wizard, after selecting the columns, in the second page of the wizard, select a column in the left list and click the button that selects one item . The third page of the wizard will allow you to specify how to display the columns.

If you are designing the report, to create a grouping, right-click it and click Sorting and Grouping. This would display the Sorting and Grouping window. Using the combo box under Field/Expression, select a column and, using the corresponding combo box under Sort Order, specify how to sort the records, in ascending or in descending order. After selecting a grouping, set the Group Header property to Yes to create the section on the report. If you also want the new section to close with its own footer bar, set the Group Footer property to Yes.

When a report with grouping comes up, it display the title of the grouping followed by all records that follow the rule(s) of the grouping. At the end of those records, it immediately starts with the second list of records that follow the next rule of the grouping. As an alternative, you can start each group of records on its own page. To do this, in the Sorting and Grouping window, after selecting the column, set the Keep Together property to Whole Group.

Practical LearningPractical Learning: Grouping Records on a Report

  1. Open the ROSH database
  2. To create a new report, on the main menu, click Insert -> Report
  3. In the New Report dialog box, click Report Wizard
  4. In the bottom combo box, select Students and click OK
  5. In the first page of the wizard, double-click Gender, StudentNumber, FirstName, LastName, and DOB
  6. Click Next
  7. In the left list of the page, double-click Gender
     
  8. Click Next twice
  9. In the fourth page of the wizard, accept the Stepped radio button and click Next
  10. In the fifth page, click Formal and click Next
     
    Report Wizard
  11. In the sixth page, change the Title of the report to Students Names and click Finish
  12. Switch the report to Design View and arrange it as you see fit
     
  13. Right-click the report and click Sorting and Grouping
  14. In the Sorting and Grouping window, click Keep Together and click the arrow of its combo box to select Whole Group
     
  15. Close the Sorting and Grouping window
  16. Preview the report
     

     
  17. Save and close the report

Using a Report

 

Selecting a Report

To perform an operation on a report, you may need to select it first. To do this, in the Reports section of the Database window, you can simply click it, once. 

To programmatically select a report, you can use the DoCmd object that is equipped with the SelectObject() method. The syntax to use would be:

DoCmd.SelectObject acReport, [objectname][, indatabasewindow]

The first argument must be acReport to indicate that the object you are selecting is a report. The second argument is the name of the report you want to select. To select but only highlight the report in the Database window, you can pass the third argument as True.

If the report is already opened and it is displaying, and if you omit the third argument or pass it as False, the report would be displayed in the foreground. If the report is not opened and you omit the third argument or pass it as False, you would receive an error.

Visually Opening a Report

When you start Microsoft Access and open a database, if it has some reports, obviously they would be closed. To use a report, you can open it first. A  report can be opened in Design View or in Print Preview. If you (or the user) double-click(s) a report in the Reports section of the Database window, it opens in Print Preview. This views allows the user to review the document before printing. By default, the view may appear blurred to show as much of its area as possible. To be able to read it, you can click the body of the report to zoom. To print it, you can click the Print button on the toolbar to send the document to the printer.

A report can also display in Design View. To show it, if the report is currently closed, you can right-click it and click Design View. You can also select it first, then click the Design button under the title bar of the Database window. If the report is already opened, to display it in Design View, as done for the form, you can click the View button on the toolbar. You can also click View -> Design on the main menu.

Programmatically Opening a Report

To programmatically open a report, you can call the OpenReport() method of the DoCmd object. Its syntax is:

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

The first argument of this method is the name of the report that you want to open.

The second argument is a constant value that can be one of the following:

View Name Result
acViewDesign The report will display in Design View
acViewNormal The report will be sent directly to the printer
acViewPreview The report will display in Print Preview

This third argument, optional, is the name of a query in the database.

The fourth argument, also optional, allows you to specify what record would be printed. If you omit this argument, all records of the Record Source value of the report would be printed. If you want to print only one or a few selected records, you can create a WHERE statement and pass it as this argument.

The fifth argument specifies how the report should be displayed. It is a constant value that can be acDialog, acHidden, acIcon, or acWindowNormal. This argument is almost never used as it has little to no effect.

In most cases, instead of writing the code manually, you can use the Command Button Wizard to select the report to print and how you want the printing to be done.

Practical LearningPractical Learning: Designing a Report

  1. Open the Solas Property Management database for which you created a report earlier
  2. In the Database window, click Forms and double-click Invoices
  3. Switch it to Design View
  4. In the Toolbox make sure the Control Wizard button is down.
    Click Command Button and click the left side of the Form Footer section
  5. In the first page of the wizard, click Report Operations
  6. In the right list, click Preview Report and click Next
     
  7. In the second page, make sure Invoices (it should be the only one) is selected and click Next
  8. In the third page, change the string in the text box with Preview This Invoice
     
  9. Click Next
  10. In the fourth page, change the Name of the control to cmdPreviewThisInvoice and click Finish
  11. Once again, in the Toolbox click Command Button and click on the right side of the previously added button
  12. In the first page of the wizard, click Report Operations and click Next
  13. In the second page click Next
  14. In the third page, change the string in the text box with Preview All Invoices and click Next
  15. In the fourth page, change the Name of the control to cmdPreviewAllInvoices and click Finish
  16. In the Footer section of the form, right-click the Preview This Invoice button and click Build Event...
  17. Change the event as follows:
     
    Private Sub cmdPreviewThisInvoice_Click()
    On Error GoTo Err_cmdPreviewThisInvoice_Click
    
        Dim stDocName As String
        Dim strWHERECondition As String
    
        stDocName = "Invoices"
        strWHERECondition = "InvoiceID = " & InvoiceID
        DoCmd.OpenReport stDocName, acPreview, , strWHERECondition
    
    Exit_cmdPreviewThisInvoice_Click:
        Exit Sub
    
    Err_cmdPreviewThisInvoice_Click:
        MsgBox Err.Description
        Resume Exit_cmdPreviewThisInvoice_Click
        
    End Sub
  18. Close Microsoft Visual Basic and return to Microsoft Access
  19. Switch the form to Form View
  20. Navigate to the third record and click Preview This Invoice
  21. Notice that only that invoice is available.
    If you have a printer, on the toolbar, click the Print button
  22. Close the report
  23. Click Preview All Invoices and notice that you can navigate to all invoices
  24. Close the report
  25. Close the form
  26. When asked whether you want to save the changes, click Yes

Closing a Report

After using a report, you (or the user) can close it. To close a report, you can click its system Close button Close or Close (Windows XP). You can also double-click its System button on the left side of its title bar. You can also press Ctrl + F4. When you closes a report, if its design has been changed since it was opened, you would be prompted whether to save it or ignore the changes.

To programmatically close a report, you can call the Close() method of the DoCmd object whose syntax is the same we saw for a form. Here is an example:

Private Sub cmdCloseStafMembers_Click()
    DoCmd.Close acReport, "StaffMembers", acSavePrompt
End Sub

When this code runs, if a report named StaffMembers is opened, it would be closed. If there is no report opened by that name, nothing would happen (Nice!).

Report Maintenance

 

Introduction

As done for tables, part of your job as a database developer consists of maintaining your reports. This include renaming, copying, or deleting the reports. Microsoft Access supports all of the necessary operations. As mentioned for a report, make sure that you need to perform the maintenance operation. If you perform an operation by mistake but have completed it, you cannot reverse it at will. You may have to recreate the object.

Renaming a Report

You can rename a report if you judge this necessary. As mentioned for a table, you cannot rename a report if it is opened: you would receive an error.

To rename a report in the Database window, first click the Reports button that leads to its section. Once in the appropriate section, you can right-click the report and click Rename. This would put the name in edit mode, allowing you to type the new name and press Enter.

To programmatically rename a report, you can call the Rename() method of the DoCmd object. The syntax to use is:

DoCmd.Rename(NewName, acReport, OldName)

The first argument is the name that the new or other report will have. The second argument must be acReport. The third argument is the name of the existing report that you want to rename. The object must exist in the Database window's section as specified by the second argument.

Copying a Report

Instead of renaming a report, you can copy it and keep the original. To copy an existing report using the Microsoft Windows Save As routine, in the Reports section of the Database window, you can right-click the report and click Save As... This would open the Save As dialog box that allows you to enter the desired name of the copied report. Alternatively, you can right-click the report, click Copy, then right-click an empty area of the same section of the Database window and click Paste. This would open the Paste Report As dialog box in which you can enter the new name of the copied object.

To programmatically copy a report, you can call the CopyObject() method of the DoCmd object using the following syntax:

DoCmd.CopyObject [destinationdatabase][, newname], acReport, sourceobjectname]

The destinationdatabase argument is the name or path of the database where the copied report would be sent to. If you are copying the report in the same database, you can omit this argument. The newname argument is the name that you want the new report to hold. The third argument must be acReport. The last argument is the name of the existing report.

Deleting a Report

If you find out that you don't need a particular report anymore, you can delete it from the database. As mentioned already, when in doubt, don't delete it.

To visually delete an object, in the Reports section of the Database window, right-click the report and click Delete. You would receive a warning before the report is actually deleted.

To programmatically delete a report, you can call the DeleteObject() method of the DoCmd object using the following syntax:

DoCmd.DeleteObject acReport, [objectname]

The first argument must be acReport. When this method is called, if the report is already selected in the Database window, you can omit the second argument and the selected report would be deleted. Otherwise, to specify the report you want to delete, pass its name as the second argument of the method.

 

 


Previous Copyright 2005 Yevol Next