Learning Microsoft Access 2007 - Macros

Learning Microsoft Access 2007 By Greg Bowden

Chapter 9

Using Macros

Guided Computer Tutorials www.gct.com.au

PUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, Victoria, 3216, Australia www.gct.com.au © Greg Bowden This product is available in Single or Multi User versions. Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version. Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided.

First published 2008 ISBN: 1 921217 52 9 (Module 1) 1 921217 53 7 (Module 2) PDF document on CD-ROM

Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.

Chapter

9

Using Macros Macros are a time-saving feature within Microsoft Access. Many steps that are frequently used can be stored in a MACRO and the macro can be reused as often as required. You can also set a button to run a macro. Your task for this activity is to create some macros for a simplified database for a real estate company. The database is similar to the one you used in Chapter 8, however, the queries to separate the rental, sale and auction properties have been removed. Macros will be used to separate data within the database.

Loading the Sample File 1

Load Microsoft Access and click on the OPEN icon in the OFFICE BUTTON, or close the current file and click on the OPEN icon in the OFFICE BUTTON.

2

Access the CHAPTER 9 folder of the ACCESS 2007 SUPPORT FILES and open the CHAPTER 9 file as an OPEN READ-ONLY file.

3

Click on the OFFICE BUTTON again, highlight SAVE AS and select ACCESS 2007 DATABASE.

4

Access your ACCESS STORAGE folder and save the file as CHAPTER 91.

Looking at the Database 1 The database consists of two tables. The first table stores data about the owner of the property. The second table stores data about the property itself. A query is used to link the two tables. 2 There are 2 forms, a MAIN SWITCHBOARD form and a DATA ENTRY form. 3 One report prints the RENTAL details and the second report prints either the properties for SALE or AUCTION.



© Guided Computer Tutorials 2008

9-1

Learning Microsoft Access 2007

Creating the Properties Macros We need macros that print the reports for each of the three different types of properties (rental, auction and sale) that the company deals with. A separate macro can be created for each, however, to save space and make it easier to keep track of macros, similar macros can be saved as a group.

A Creating the Rental Properties Macro For this macro we need to separate the properties for rent.

1 Open the CREATE tab of the RIBBON and click on the NEW OBJECT MACRO icon to start a new macro.

2 Click on the MACRO NAMES icon in the DESIGN tab to add the MACRO NAME column to the macro screen.

NOTE:

i The MACRO NAME column is used to name the different macros.



ii The ACTION column is used to enter the commands of the macro. Those commands are executed in the order that you enter them.



iii The ARGUMENTS column stores the options that control the way the action operates.



iv The COMMENTS column is used to enter a description of the command. It helps you to remember the purpose of the command at a later date.

9-2

© Guided Computer Tutorials 2008

Using Macros

9

3 In the first MACRO NAME cell enter the first macro name: Rental Properties 4 The columns in a macro can be widened in the same way as in tables and queries. Simply drag the intersection between headings to the right.

5 Tab to the first ACTION cell and insert the HOURGLASS command by clicking on the arrow marker and selecting it from the list of actions.

NOTE:

You can also press the H key to insert the action. The HOURGLASS command places the HOURGLASS cursor on the screen as the macro is operating. It lets the user know that the computer is doing something.

6 Press the down arrow key to place the cursor in the second ACTION cell and set it to OPENREPORT action.



© Guided Computer Tutorials 2008

9-3

Learning Microsoft Access 2007

7 In the ACTION ARGUMENTS section, set the REPORT NAME box to RENTAL PROPERTIES REPORT, the VIEW box to PRINT PREVIEW and the WHERE CONDITION box to: [Property Details] ! [Sale Type] = “Rent”

NOTE:

i The REPORT NAME box is used to select the required report.



ii The VIEW box indicates whether the report is to be printed directly, previewed or set to design view.



iii The WHERE CONDITION box is telling the macro to look in the PROPERTY DETAILS table and find all the records where the SALE TYPE field contains the word RENT. The table name and field name must be separated by an exclamation mark (!).



iv You can use the EXPRESSION BUILDER icon at the right of the WHERE CONDITION box to build the condition if you prefer.



v The ARGUMENTS that you set are stored in the ARGUMENTS column.

8 Tab to the COMMENT cell and enter the comment: Preview the Rental Properties Report

9-4

© Guided Computer Tutorials 2008

Using Macros

9

9 Set the third ACTION cell to the MAXIMISE command. This will cause the preview screen to be maximised once it is opened.

B Saving the Macro As will tables, queries, forms and reports’; macros must be saved manually. 1 Click on the SAVE icon in the QUICK ACCESS TOOLBAR. 2 Call the macro: Property Report Macros and select OK.

C Running the Macro There are two ways to run the macro. You can run it directly from the MACROS window that is currently open and showing the macro steps, or you can run it from the RUN MACRO icon in the DATABASE TOOLS tab of the RIBBON.

1 Click on the RUN icon in the DESIGN tab of the RIBBON.



© Guided Computer Tutorials 2008

9-5

Learning Microsoft Access 2007

2 The RENTAL PROPERTIES report should be opened and maximised.

3

Close the preview to return to the MACROS window.

4 Open the DATA ENTRY FORM, then open the DATABASE TOOLS tab of the RIBBON. Click on the RUN MACRO icon and select the PROPERTY REPORT MACROS: RENTAL PROPERTIES from the MACRO NAME box.

5

When you select OK the RENTAL PROPERTIES report should be opened and maximised. Close the preview to return to the form.

NOTE: i A button can be set to run a macro as you will see shortly.

9-6

ii You can also use macros to open tables or queries.

© Guided Computer Tutorials 2008

Using Macros

9

D Creating the Sale Properties Macro For this macro we need to separate the properties for sale. We could create a whole new macro but as it is very similar to the previous macro we can GROUP it with the first macro.

1 Return to the PROPERTY REPORT MACROS window. You can open it if it is closed by RIGHT+CLICKING on the macro name in the MACROS section of the NAVIGATION pane and selecting DESIGN VIEW.

2 In the fifth MACRO NAME cell enter the name of the second macro group member: Sale Properties

3 For this macro we will use the MESSAGE BOX command rather than the HOURGLASS command. Tab to the adjacent ACTION cell and set it to the MSGBOX command.



© Guided Computer Tutorials 2008

9-7

Learning Microsoft Access 2007

NOTE:

The MESSAGE BOX command causes a dialogue box to be placed on the screen before the rest of the macro is executed.

4 In the ACTION ARGUMENTS section at the bottom of the window click in the MESSAGE box and enter: Open the Sale Properties Report This is the message that will be placed in a dialogue box. 5 Leave the BEEP box as YES (the program will ‘beep’ when the message box is displayed). Set the TYPE box to INFORMATION. This sets the type of graphic to be displayed in the dialogue box. In the TITLE box enter PREVIEW REPORT. This text will be displayed in the title bar of the dialogue box. 6 Set the second ACTION cell for this macro to OPENREPORT.

7 In the ACTION ARGUMENTS section, set the REPORT NAME box to SALE PROPERTIES REPORT, the VIEW box to PRINT PREVIEW and the WHERE CONDITION box to: [Property Details] ! [Sale Type] = “Sale”

9-8

© Guided Computer Tutorials 2008

Using Macros

9

8 Set the COMMENT cell for the OPEN REPORT command to: Preview the Sale Properties Report

9 Set the third ACTION cell to the MAXIMISE command and resave the macro group.

E Creating the Auction Properties Macro 1 In the ninth MACRO NAME cell enter the macro name: Auction Properties then set its first ACTION to MESSAGE BOX. 2 In the ACTION ARGUMENTS set the MESSAGE to: Open the Auction Properties Report the BEEP to YES, the TYPE to INFORMATION and the TITLE to PREVIEW REPORT.



© Guided Computer Tutorials 2008

9-9

Learning Microsoft Access 2007

3 Set the cell under MSGBOX to OPENREPORT.

4 The macro needs to open the SALE PROPERTIES REPORT in PRINT PREVIEW mode and the WHERE CONDITION box should be set to: [Property Details] ! [Sale Type] = “Auction”

5 Set the COMMENT cell: Preview the Auction Properties Report

Set the cell under OPENREPORT to MAXIMISE and resave the macro group.

6 Resave the macro then close it by clicking on its CLOSE WINDOW icon.

9-10

© Guided Computer Tutorials 2008