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.

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.

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.

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.

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

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.

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.

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

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.

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
     
    
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

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.

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.

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:

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.

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

After using a report, you (or the user) can close it. To close a report, you can click its system Close button or . 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!).

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.

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.

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.

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.