Exploring a Desktop Database-Housing Reservations

Now that you know something about the major objects that make up an Access database, a good next step is to spend some time exploring the Housing Reservations database (Housing.accdb) that comes with this book. First, follow the instructions at the beginning of this book for installing the sample files on your hard drive. When you start Access 2007, it displays the Getting Started window shown in Figure 3–2.

Figure 3–2: Access 2007 displays the Getting Started window every time you start the program.

Click the More link under Open Recent Database on the right side of the window to see the Open dialog box shown in Figure 3–3. In the Open dialog box, select the file Housing.accdb from the folder in which you installed the sample databases, and then click Open. You can also double-click the file name to open the database. (If you haven’t set options in Windows Explorer to show file name extensions for registered applications, you won’t see the .accdb extension for your database files.)

Figure 3–3: Use the Open dialog box to locate the database that you want to open.

When you open the Housing Reservations application, it displays a Not Trusted dialog box if you have not followed the instructions in the previous chapter to define the location of the sample files as trusted. If this happens, click the Close button to close the dialog box. The application also briefly displays a copyright information notice and then displays a message box instructing you to open the frmSplash form. Click OK to dismiss this message box, and then Access puts the focus on the frmSplash form in the Navigation Pane. (You can open the frmSplash form if you want to run the application.) Your Access window should look similar to Figure 3–4.

Figure 3–4: The Navigation Pane displays the objects defined in the Housing Reservations sample database.

For an existing database, the Navigation Pane is always the same width as it was when you last set it. The title bar of the window normally shows the name of the database that you have open. As you’ll learn later in this book, you can set options in the database to change the title bar of the main Access window to show the name of your application instead of Microsoft Access-we modified the sample database to display the title Housing Reservations on the title bar.

As we discussed in the previous chapter, the Ribbon has four main tabs that are displayed at all times. As you explore Access 2007, you’ll see that the Ribbon provides several contextual tabs that appear and disappear as you work with specific database objects and areas of the program. These contextual tabs make available commands that are useful only within the context of the object that has the focus and that object’s current view. For example, it wouldn’t make sense to show you table design commands when you have a table open to display its data (Datasheet view). Likewise, you don’t need datasheet commands when you have a query open in Design view. We’ll explain the various contextual tabs in more detail as we explore the database objects and other areas of Access in the following chapters.

Note 

You can rest your mouse pointer on any command or option on the various Ribbon tabs for a second (without clicking the button), and Access displays a ScreenTip to help you discover the purpose of the button.

In the previous chapter, you learned that you can change how Access displays the list of objects in the database by using one of the built-in navigation categories (Object Type, Tables And Related Views, Created Date, and Modified Date) or by defining your own custom navigation category. You also learned that you can filter each navigation category to limit what group Access displays within each category so that you don’t have to wade through a long list to find what you want.

In this chapter, we’ll be exploring each of the types of objects in the Housing Reservations database, so click the Navigation Pane menu at the top of the Navigation Pane and click Object Type under Navigate To Category. Open the menu again and be sure that you have clicked All Access Objects under Filter By Group, as shown in Figure 3–5. Your Navigation Pane should now look similar to Figure 3–4. You can collapse an entire group of objects by clicking on the group’s header bar. If you open the Navigation Pane menu, you can see the names of some custom groups we have defined under Navigate To Category to help organize your work. You’ll learn how to work with groups later in this chapter.

Figure 3–5: Select Object Type under Navigate To Category and then All Access Objects under Filter By Group to see all objects organized in groups by object type.

Tables

Click the menu bar at the top of the Navigation Pane and select Object Type under Navigate To Category. Open the menu again and select Tables under Filter By Group to display a list of tables available in the Housing Reservations database, as shown in Figure 3–6.

Figure 3–6: After filtering the Object Type category in the Navigation Pane, you can see only the tables in the Housing Reservations database.

You can open a table in Datasheet view to see the data in the table by double-clicking the table name in the Navigation Pane; or you can open the table in Design view by holding down the Ctrl key and double-clicking the table name. If you right-click a table name, Access displays a shortcut menu, as shown in Figure 3–7, that lets you perform a number of handy operations on the item you selected. Click one of the commands on the shortcut menu, or click anywhere else in the Access window to dismiss the menu.

Figure 3–7: You can access many commands from the shortcut menu for a table in the Navigation Pane.

Inside Out-Turning on Single-Click 

If you want to make it easier to open objects from the Navigation Pane, you can rightclick the menu bar at the top of the Navigation Pane and select Navigation Options on the shortcut menu. In the lower-right corner of the Navigation Options dialog box, select Single-Click under Open Objects With and click OK. The examples in this chapter assume you are using the default Double-Click setting.

Table Window in Design View

When you want to change the definition of a table (the structure or design of a table, as opposed to the data in a table), you must open the Table window in Design view. With the Housing Reservations database open, right-click the tblEmployees table and select Design View from the shortcut menu; this opens the tblEmployees table in Design view, as shown in Figure 3–8. (Collapse the Navigation Pane to be able to see the entire width of the design area.) You’ll learn about creating table definitions in Chapter 4, “Creating Your Database and Tables.”

Figure 3–8: Open a table in Design view to change its structure.

In Design view, each row in the top portion of the Table window defines a different field in the table. You can use the mouse to select any field that you want to modify. You can also use the Tab key to move from left to right across the screen, column to column, or Shift+Tab to move from right to left. Use the Up and Down Arrow keys to move from row to row in the field list. As you select a different row in the field list in the top portion of the window, you can see the property settings for the selected field in the bottom portion of the window. Press F6 to move between the field list and the field property settings portions of the Table window in Design view. Unlike previous versions of Access, pressing F6 again does not immediately move the focus back to the field list. If you press F6 repeatedly, the focus goes to the Navigation Pane, to the Ribbon, and then finally back to the field list.

Access has many convenient features. Wherever you can choose from a limited list of valid values, Access provides a list box to assist you in selecting the proper value. For example, when you tab to the Data Type column in the field list, a small arrow appears at the right of the column. Click the arrow or press Alt+Down Arrow to see the list of valid data types, as shown in Figure 3–9.

Figure 3–9: The Data Type list box shows you the available data types.

You can open as many as 254 tables (fewer if you are limited by your computer’s memory). If you have selected Overlapping Windows in the Access Options dialog box, you can minimize any of the windows to an icon along the bottom of the Access workspace window by clicking the Minimize button in the upper-right corner of the window. You can also maximize the window to fill the Access workspace to the right of the Navigation Pane by clicking the Maximize/Restore button in that same corner. If you don’t see a window you want, you can select it from the list of active windows in the Manage Windows command in the Window group on the Home tab on the Ribbon to bring the window to the front. Click the Close command from the Control Box in the upperleft corner or click the window’s Close button in the upper-right corner to close any window.

Troubleshooting

Why can’t I see the Maximize/Minimize buttons on my table? no

If you are using the tabbed documents interface (the setting used in the Housing Reservations sample database), each open object has its own tab to the right of the Navigation Pane. This option is the default for new databases you create in Access 2007. However, when you open older database files created in earlier versions of Access, the Document Window Options setting in the Access Options dialog box defaults to Overlapping Windows. With the Tabbed Documents setting, there is no need to constantly minimize and maximize object windows to switch views because each open object has an individual tab at the top of the Access workspace (the area below the Ribbon and to the right of the Navigation Pane). Clicking on these object tabs enables you to easily switch among any open objects, so Access 2007 does not provide the Maximize/Minimize buttons. To set your database to Overlapping Windows or Tabbed Documents, see “Using the SingleDocument vs. Multiple-Document Interface” on page 83.

Table Window in Datasheet View

To view, change, insert, or delete data in a table, you can use the table’s Datasheet view. A datasheet is a simple way to look at your data in rows and columns without any special formatting. You can open a table’s Datasheet view by double-clicking the name of the table you want in the Navigation Pane or by right-clicking on the table name and selecting Open from the shortcut menu. When you open a table in Design view, such as the tblEmployees table shown in Figure 3–8, you can switch to the Datasheet view of this table, shown in Figure 3–10, by clicking the arrow in the Views group on the Ribbon and clicking Datasheet View from the list of available views. Likewise, when you’re in Datasheet view, you can return to Design view by clicking the arrow in the Views group and clicking Design View from the available options. You can also switch views for the table by clicking the various view buttons on the status bar located in the lowerright corner of the Access window. You’ll read more about working with data in Datasheet view in Chapter 7, “Creating and Working with Simple Queries.”

Figure 3–10: Use the Views button on the Ribbon or the individual view buttons on the status bar to switch from Design to Datasheet view.

As in Design view, you can move from field to field in the Table window in Datasheet view by pressing Tab, and you can move up and down through the records using the arrow keys. You can also use the scroll bars along the bottom and on the right side of the window to move around in the table. To the left of the horizontal scroll bar, Access shows you the current record number and the total number of records in the currently selected set of data. You can select the record number with your mouse (or by pressing F5), type a new number, and then press Enter to go to that record. You can use the arrows on either side of this record number box to move up or down one record or to move to the first or last record in the table. You can start entering data in a new record by clicking the New (Blank) Record button on the right.

Queries

You probably noticed that the Datasheet view of the tblEmployees table gave you all the fields and all the records in the table. But what if you want to see only the employee names and addresses? Or maybe you would like to see in one view information about employees and all their confirmed room reservations. To fill these needs, you can create a query. Open the Navigation Pane menu, click Object Type under Navigate To Category if it isn’t already selected, and then click Queries under Filter By Group to display a list of queries available in the Housing Reservations database, as shown in Figure 3–11.

Figure 3–11: When you filter object types by queries in the Navigation Pane, Access displays a list of only the queries in the Housing Reservations database.

Troubleshooting

Why does my table have extra rows in the lower half of the sceen like a speadsheet?

You might notice in Figure 3–10 that there are extra rows beneath our existing records, and this grid very much resembles a spreadsheet This is a departure from previous versions of Access that displayed only one row for each record in that table plus one for a new record. For tables in Datasheet view in Access 2007, the remainder of the space in the application window is filled with dummy rows that you cannot click into. In essence, these extra rows are simply placeholders for possible future records. It might be confusing to think of this grid as a spreadsheet because of its appearance, but you must remember that Access is not a spreadsheet What you see is only a visual aid and does not denote actual records in the tables.

You can open a query in Datasheet view by double-clicking the query name, or you can open it in Design view by clicking on the query to select it, and then pressing Ctrl+Enter. You can also right-click a query and click the Open or Design View command on the shortcut menu.

Query Window in Design View

When you want to change the definition of a query (the structure or design, as opposed to the data represented in the query), you must open the query in Design view. Take a look at one of the more complex queries in the Housing Reservations query list by scrolling to the query named qryFacilityReservations. Select the query and then press Ctrl+Enter to display the query in Design view, as shown in Figure 3–12. Collapse the Navigation Pane to see more of the width of the query design.

Figure 3–12: The qryFacilityReservations query in Design view shows data from three tables being linked.

In the upper part of a Query window in Design view, you see the field lists of the tables or other queries that this query uses. The lines connecting the field lists show how Access links the tables to solve your query. If you define relationships between two tables in your database design, Access draws these lines automatically when you include both tables in a query design. See Chapter 4, for details. You can also define relationships when you build the query by dragging a field from one field list and dropping it on another field list.

In the lower part of the Query window, you see the design grid. The design grid shows fields that Access uses in this query, the tables or queries from which the fields come (when you select Table Names in the Show/Hide group on the Ribbon’s Design tab), any sorting criteria, whether fields show up in the result, and any selection criteria for the fields. You can use the horizontal scroll bar to bring other fields in this query into view. As in the Design view of tables, you can use F6 to move between the upper and lower portions of the Query window, but the F6 key also cycles through the Query window, the Navigation Pane, and the Ribbon.

You can learn how to build this type of complex multiple-table query in Chapter 8, “Building Complex Queries.” You can find this query used in the Housing Reservations database as the source of data for the fsubFacilityReservations form.

Query Window in Datasheet View

On the Design or Home tab on the Ribbon, click the View button to run the query and see the query results in Datasheet view, as shown in Figure 3–13. You can also rightclick the query tab and click Datasheet View on the shortcut menu.

Figure 3–13: The Datasheet view of the qryFacilityReservations query shows you fields from three related tables.

The Query window in Datasheet view is similar to a Table window in Datasheet view. Even though the fields in the query datasheet shown in Figure 3–13 are from three different tables, you can work with the fields as if they were in a single table. If you’re designing an Access application for other users, you can use queries to hide much of the complexity of the database and make the application simpler to use. Depending on how you designed the query, you might also be able to update some of the data in the underlying tables simply by typing new values in the Query window as you would in a Table window in Datasheet view.

Forms

Datasheets are useful for viewing and changing data in your database, but they’re not particularly attractive or simple to use. If you want to format your data in a special way or automate how your data is used and updated, you need to use a form. Forms provide a number of important capabilities.

• You can control and enhance the way your data looks on the screen. For example, you can add color and shading or add number formats. You can add controls such as list boxes and check boxes. You can display ActiveX objects such as pictures and graphs directly on the form. And you can calculate and display values based on data in a table or a query.

• You can perform extensive editing of data using macros or Visual Basic procedures.

• You can link multiple forms or reports by using macros or Visual Basic procedures that are run from buttons on a form.

Click the menu bar at the top of the Navigation Pane, click Object Type under Navigate To Category, and then click Forms under Filter By Group to display a list of forms available in the Housing Reservations database, as shown in Figure 3–14.

Figure 3–14: When you filter Object Type by Forms, Access displays a list of only the forms in the Housing Reservations database.

You can open a form in Form view by double-clicking the form name in the Navigation Pane. You can also open the form in Design view by clicking the form to highlight it, and then pressing Ctrl+Enter. Finally, you can right-click a form name and click a command on the shortcut menu. To create a new form, use the commands in the Forms group of the Create tab on the Ribbon.

Form Window in Design View

When you want to change the definition of a form (the structure or design, as opposed to the data represented in the form), you generally must open the form in Design view. As you’ll learn in Chapter 12, “Customizing a Form,” you can also set a form property to allow you to make changes in Layout view while you are designing the form. Take a look at the frmEmployeesPlain form in the Housing Reservations database. To open the form, scroll through the list of forms in the Navigation Pane to find the frmEmployees-Plain form, click the form to select it, then press Ctrl+Enter. This form, shown in Figure 3–15, is designed to display all data from the tblEmployees table. Don’t worry if what you see on your screen doesn’t exactly match Figure 3–15. In this figure, we opened the field list on the right so that you can see some of the main features of the Form window in Design view.

Figure 3–15: When you open the frmEmployeesPlain form in Design view, you can modify its design.

The large window in the center is the form design window where you create the design of the form. When you first open this form in Design view, you should see the Form Design Tools collection of two contextual tabs, Design and Arrange, on the Ribbon just to the right of Database Tools. These tabs are the action centers of form design-you’ll use the tools here to add and arrange the design elements of your form.

On the right side of the window shown in Figure 3–15, you can see a field list for this form. This form gets its information from a query called qryEmployees that selects all the fields in the tblEmployees table and then sorts the rows by last name and first name. If you don’t see the field list, click the Add Existing Fields command in the Tools group of the Design contextual tab. You can resize this window by clicking on the far left edge of the box and dragging it to a new width toward the left side of the screen. When your mouse pointer is positioned over the title bar, it changes to cross arrows. Click the title bar and drag it to the left and down to undock the window from the right side and position it where you would like. When you undock the Field List window, it becomes a window that floats on top of the design area. When you read about form design in Chapter 11, “Building a Form,” you’ll see that you can drag a field from the field list to place a control on the form that displays the contents of the field.

After you place all the controls on a form, you might want to customize some of them. You do this by opening the property sheet displayed in Figure 3–16. To see the property sheet, click the Property Sheet button in the Tools group of the Design tab. In Figure 3–16 we collapsed the Navigation Pane to show more of the property sheet.

Figure 3–16: The property sheet lets you set individual properties for a form, form sections, or controls on the form.

The property sheet always shows the property values for the control selected in the form design. (The property sheet can also display the properties for the form or any section on the form.) Click the tabs at the top of the property sheet to display all properties or to display only properties for formats, data, or events. In the example shown in Figure 3–16, we clicked the text box named EmployeeNumber, near the top of the form, to select it. If you click this text box and then scroll down the list of properties for this text box, you can see the wide range of properties you can set to customize this control. As you learn to build applications using Access, you’ll soon discover that you can customize the way your application works by simply setting form and control properties-you don’t have to write any code.

If you scroll to the bottom of the property list, or click the Event tab, you’ll see a number of properties that you can set to define the macros or Visual Basic procedures that Access runs whenever the associated event occurs on this control. For example, you can use the Before Update event property to define a macro or procedure that performs additional validation before Access saves any changes typed in this control. You can use the On Click or On Dbl Click event properties to perform actions when the user clicks the control. If you need to, you can even look at every individual character the user types in a control with the On Key event properties. As you’ll discover later, Access provides a rich set of events that you can detect for the form and for each control on the form.

You might have noticed that Access made available all the commands and options in the Font group of the Design tab when you selected the EmployeeNumber control. When you select a text box on a form in Design view, Access enables the list boxes in this group to make it easy to select a font and font size, and it also enables buttons that let you set the Bold, Italic, and Underline properties. Underneath these buttons are three buttons that let you set text alignment: Align Text Left, Center, and Align Text Right. You can also set the font and fill colors using buttons in this group.

Form Window in Layout View

Access 2007 introduces a new view for forms called Layout view. If you have the frmEmployeesPlain form open in Design view from the previous section, you can switch to Layout view by right-clicking the frmEmployeesPlain tab and clicking Layout View on the shortcut menu. You should now see the form in Layout view, as shown in Figure 3–17. This unique view for forms gives the developer a fast and easy way to create and modify form designs.

Figure 3–17: Layout view lets you see your data and also modify the design of the form.

Unlike Design view, Layout view enables you to work with the various control elements and form sections using existing live data. If, for example, you need to resize a text box to fit the available data, you do not have to continually switch back and forth between Form and Design view to see if your size change works effectively-you actually see data in the text box while resizing the control. This new What-You-See-Is-What-You-Get (WYSIWYG) form-authoring view provides the best of both worlds by combining the ability to change the structure of the data entry form at the same time you’re accessing actual data.

In Layout view, if you have grouped a set of controls you can move them around the form design grid together to maintain their proximity and orientation to one another. In this sample form, we grouped all the controls in the first column in a stacked layout.

In Figure 3–18, you can see that we’re dragging the Email Name field down below the Office Location field. A horizontal bar designates where Access will place the control after you release the mouse button. Because these controls are grouped, Access places the Email Name field and its label below the Office Location field and aligns them perfectly.

Figure 3–18: You can move a control within a group in Layout view, and Access keeps them perfectly aligned.

Form Window in Form View

To view, change, insert, or delete data via a form, you can use Form view. Depending on how you’ve designed the form, you can work with your data in an attractive and clear context, have the form validate the information you enter, or use the form to trigger other forms or reports based on actions you take while viewing the form. You can open a form in Form view by right-clicking the form’s name in the Navigation Pane and clicking Open on the shortcut menu. If you still have the frmEmployeesPlain form open in Layout view from the previous section, you can go directly to Form view by clicking the arrow in the Views group and then clicking Form View.

Figure 3–19 shows a complex form that brings together data from three tables and loads the related employee picture from a file on your hard drive onto a screen that’s easy to use and understand. This form includes all the fields from the tblEmployees table. You can tab or use the arrow keys to move through the fields. You can click the Personal Info tab to see additional information about the current employee. You can experiment with filtering by selection to see how easy it is to select only the records you want to see. For example, you can click in the Department field, select the department name, click the Selection button in the Sort & Filter group on the Home tab, and then click Equals “Selected Department” (where “Selected Department” is the department name you selected) to display records only for the current department.

Figure 3–19: The frmEmployeesPlain form in Form view lets you view and edit employee data.

There are four other ways to look at a form: Datasheet view, PivotTable view, PivotChart view, and Print Preview. You can select the Datasheet view by clicking the arrow in the Views group and clicking Datasheet View to see all the fields in the form arranged in a datasheet-similar to a datasheet for a table or a query. When a form has been designed to display data in a PivotTable (similar to a spreadsheet) or graphed in a PivotChart, you can also select these views with the View button. You can click the Microsoft Office Button, move your mouse pointer to Print, and then click Print Preview on the submenu to see what the form will look like on a printed page. You’ll read more about Print Preview in the next section.

Reports

If your primary need is to print data, you should use a report. Click the menu bar at the top of the Navigation Pane to open the Navigation Pane menu and click Object Type under Navigate To Category. Then open the menu again and click the Reports option under Filter By Group to display a list of reports available in the Housing Reservations database, as shown in Figure 3–20.

Figure 3–20: You can filter the Navigation Pane to show only a list of the reports in your database.

Although you can print information in a datasheet or a form, neither of these formats provides the flexibility that reports do when you need to produce complex printed output (such as invoices or summaries) that might include many calculations and subtotals. Formatting in datasheets is limited to sizing the rows and columns, specifying fonts, and setting the colors and gridline effects. You can do a lot of formatting in a form, but because forms are designed primarily for viewing and entering data on the screen, they are not suited for extensive calculations, grouping of data, or multiple totals and subtotals in print.

Report Window in Design View

When you want to change the definition of a report, you must open the report in Design view. In the report list for Housing Reservations, click on the rptEmployeesPlain report to select it, and then press Ctrl+Enter to see the design for the report, as shown in Figure 3–21. Don’t worry if what you see on your screen doesn’t exactly match Figure 3–21. We clicked the Add Existing Fields command on the Design tab under Report Design Tools to display the Field List window.

Figure 3–21: Open the rptEmployeesPlain report in Design view to modify its design.

The large window in the center is where you create the design of the report. This report is designed to display all the information about employees by department. Notice that Design view for reports is similar to Design view for forms. (For comparison, see Figure 3–15.) Reports provide additional flexibility, allowing you to group items and to total them (either across or down). You can also define header and footer information for the entire report, for each page, and for each subgroup on the report. When you first open this report in Design view, you should see three new contextual tabs appear on the Ribbon just to the right of Database Tools under Report Design Tools: Design, Arrange, and Page Setup. These contextual tabs are the action centers of report design-you’ll use the tools here to add the design elements you want.

On the right side of the window shown in Figure 3–21, you can see the field list for this report. This list shows all the fields returned by the record source for the report, qryRptEmployees-all the fields from the tblEmployees table and related fields from the tblDepartments table. If you don’t see the field list, click the Add Existing Fields command in the Tools group on the Design contextual tab. You can resize this window by clicking on the far left edge and dragging it to a new width toward the left side of the screen. When your mouse pointer is positioned over the title bar, it changes to cross arrows. Click the title bar and drag it to the left and down to undock the window from the right side and position it where you would like. When you undock the Field List window, it becomes a window that floats on top of the design area. When you read about report design in Chapter 15, “Constructing a Report,” you’ll see that you can drag a field from the field list to place a control on the report that displays the contents of the field.

After you place all the controls on a report, you might want to customize some of them. Do this by opening the property sheet, which you can see on the right side of the screen in Figure 3–22. To see the property sheet, click the Property Sheet command in the Tools group of the Design tab. In Figure 3–22 we collapsed the Navigation Pane so you can see more of the property sheet.

Figure 3–22: The property sheet lets you set individual properties for a report, report sections, or controls on the report.

The property sheet always shows the property settings for the control selected in the Report window. (The Property Sheet pane can also display the properties for the entire report or any section on the report.) In the example shown in Figure 3–22, we clicked the text box named EmployeeNumber to select it. If you click this text box, you can see that Access displays the EmployeeNumber field from the tblEmployees table as the control source (input data) for this control. You can also specify complex formulas that calculate additional data for report controls.

You might have noticed that Access made available some additional commands and options in the Font group of the Design tab when you selected the EmployeeNumber control. When you select a text box in a report in Design view, Access enables list boxes in the Font group that make it easy to select a font and font size. Access also enables buttons that let you set the Bold, Italic, and Underline properties. Underneath these buttons are three buttons that set text alignment: Align Text Left, Center, and Align Text Right. You can also set font and fill colors using buttons in this group.

Reports can be even more complex than forms, but building a simple report is really quite easy. Access provides report wizards that you can use to automatically generate a number of standard report layouts based on the table or query you choose. You’ll find it simple to customize a report to suit your needs after the report wizard has done most of the hard work. You’ll learn how to customize a report in Chapter 15 and Chapter 16, “Advanced Report Design.”

Report Window in Print Preview

To see what the finished report looks like, click the arrow in the Views group and then click Print Preview when you’re in the Report window in Design view. You can also right-click the report name in the Navigation Pane and then click Print Preview on the shortcut menu. Figure 3–23 shows a report in Print Preview.

Figure 3–23: When you open a report in Print Preview, Access shows you how the report will look when you print it.

Access initially shows you the upper-left corner of the report. To see the report centered in full-page view in Print Preview, click the Zoom control in the lower-right corner of the status bar where it says 100%. Clicking that button automatically adjusts the zoom level percent so that you can see a full page of the report. To see two pages side-by-side, click the Two Pages button in the Zoom group of the Print Preview contextual tab. This gives you a reduced picture of two pages, as shown in Figure 3–24, and an overall idea of how Access arranges major areas of data on the report. Unless you have a large monitor, however, you won’t be able to read the data. Click the More Pages button and then click an option (Four Pages, Eight Pages, or Twelve Pages) to see more than two pages. When you move the mouse pointer over the window in Print Preview, the pointer changes to a magnifying glass icon. To zoom in, click over an area that you want to see more closely. You can then use the scroll bars to move around in the magnified report. Use the Zoom control on the status bar to magnify or shrink your view. Access also provides several output options such as Word or Excel in the Data group of the Print Preview tab.

Figure 3–24: Click the Two Pages button to see two pages side-by-side in Print Preview.

Report Window in Layout View

Access 2007 introduces a new view for reports called Layout view. This unique view for reports gives the developer a fast and easy way to create and modify report designs. Unlike Design view, Layout view enables you to work with the various control elements and report sections using existing live data. Similar to Layout view for forms, this new WYSIWYG report-authoring view provides the best of both worlds by combining the ability to change the structure of the report at the same time you’re accessing the data.

To open the rptEmployeesPlain in Layout view, find the report in the Navigation Pane, right-click the report name, and click Layout View on the shortcut menu. Figure 3–25 shows the report in Layout view. In Figure 3–25 we collapsed the Navigation Pane so you can see more of the report design grid.

Figure 3–25: Similar to Layout view for forms, Layout view in reports lets you adjust design elements while looking at the data from your database.

Just like Layout view for forms, if you have grouped a set of controls, you can move them around the report grid together to maintain their proximity and orientation to one another. In Figure 3–26, you can see that we’re dragging the Birth Date field above the Email field. A horizontal bar designates where Access will place the control after you release the mouse button. Because these controls are grouped, Access places the Birth Date field and its label above the Email field. The two controls swap places and align perfectly.

Figure 3–26: Access makes it easy to move controls around within a group in Layout view.

Report Window in Report View

In addition to Layout view, Access 2007 includes another new view for reports called Report view, an interactive view for reports that can respond to control events, much like data entry forms. If you have the rptEmployeesPlain report open in Layout view from the previous section, you can switch to Report view by right-clicking the Employees tab and clicking Report View on the shortcut menu. You should now see the report no in Report view, as shown in Figure 3–27.

Figure 3–27: When a report is in Report view, you can program controls to respond to mouse clicks to open a related form.

Previous versions of Access treat reports on screen as static. After you open a report on the screen, you can only view the report or print it. Report view in Access 2007 gives you the ability to interact with the report through filters to drill down to specific records and then print only this smaller group of records. You can include command buttons on your reports with Access 2007 and program the buttons to respond to a mouse click in Report view. In the new Report view, you can designate controls that respond to events as hyperlinks to provide a visual cue that an event occurs when clicking that control. In Figure 3–27, for example, observe that the Employee Number field looks like a hyperlink with a blue line underneath the data. (In Figure 3–27 we have scrolled down the records to show John’s information.) Clicking the Employee Number field opens the frmEmployeesPlain form to display all information for that specific employee so that you can make any necessary changes. After closing the form and returning to the report, click the Refresh All command in the Records group of the Home tab on the Ribbon to see any changes you made to the data using the form reflected in the report. In Figure 3–27 you can see that the frmEmployeesPlain form opens on a new tab because we are using the tabbed interface.

Close the Form window and the Report window to return to the Navigation Pane.

Macros

You can make working with your data within forms and reports much easier by triggering a macro action. Office Access 2007 provides more than 70 actions that you can include in a macro. They perform tasks such as opening tables and forms, running queries, running other macros, selecting options from menus, and sizing open windows. You can also group multiple actions in a macro and specify conditions that determine when each set of actions will or will not be executed by Access.

Open the Navigation Pane menu and make sure Object Type is selected under Navigate To Category. Then open the menu again and click Macros under Filter By Group to display a list of macros available in the Housing Reservations database, as shown in Figure 3–28. You can run a macro by right-clicking the macro name in the Navigation Pane and clicking Run on the shortcut menu. To open a macro in Design view, right-click the macro name and click Design View on the shortcut menu. To create a brand new macro, click the New Object Macro button in the Other group of the Create tab on the Ribbon.

Figure 3–28: You can filter the Navigation Pane to show the Macros list in the Housing Reservations database.

Macros are a great way to learn about the basics of responding to events and automating actions in an Access database. However, for any application that you intend to distribute to others, you should use Visual Basic to handle events and automate actions. Nearly all the sample databases use Visual Basic exclusively. You can take a look at the design of a macro example in the Housing Reservations database by selecting the SampleMacro macro in the Navigation Pane, and then pressing Ctrl+Enter. Access opens the Macro window in Design view, as shown in Figure 3–29.

Figure 3–29: Open the SampleMacro macro object in the Housing Reservations database in Design view to examine and modify its definition.

You can design multiple macro actions within a single macro object and give each one a name in the first column. Any unnamed lines following a line with a name specified all belong to the named macro. In the second column, you can optionally specify a condition test that must be true for the macro command on that line to execute. You can use a continuation indicator (...) on subsequent lines to specify additional commands that should also execute when the condition is true. You select the action you want to run from a list in the Action column and set the arguments required for the action in the Action Arguments section in the lower part of the design window. Some of the limitations of macros include limited ability to branch to other actions and very limited ability to loop through a set of actions.

If you want to see what this macro does, click the Run button in the Tools group of the Design contextual tab to execute it. You should see a greeting message appropriate to the time of day appear on your screen. To learn more about events and the macro design facility, see Chapter 18, “Automating Your Application with Macros.” You can find one sample application on the companion CD that is automated entirely using macros-WeddingListMC.accdb.

Close the Macro window now to return to the Navigation Pane.

Modules

You might find that you keep coding the same complex formula over and over in some of your forms or reports. Although you can build a complete Access application using only forms, reports, and macros, some actions might be difficult or impossible to define in a macro. If that is the case, you can create a Visual Basic procedure that performs a series of calculations and then use that procedure in a form or report.

If your application is so complex that it needs to deal with errors (such as two users trying to update the same record at the same time), you must use Visual Basic. Because Visual Basic is a complete programming language with complex logic and the ability to link to other applications and files, you can solve unusual or difficult programming problems by using Visual Basic procedures.

Version 2, of Access introduced the ability to code Basic routines in special modules attached directly to the forms and reports that they support. You can create these procedures from Design view for forms or reports by requesting the Code Builder in any event property. You can edit this code behind forms and reports by clicking View Code in the Tools group on the Design contextual tab when you have a form or report open in Design view. See Chapters 19 and 20 for details. In fact, after you learn a little bit about Visual Basic, you might find that coding small event procedures for your forms and reports is much more efficient and convenient than trying to keep track of many macro objects. You’ll also soon learn that you can’t fully respond to some sophisticated events, such as KeyPress, in macros because macros can’t access special additional parameters (such as the value of the key pressed) generated by the event. You can fully handle these events only in Visual Basic.

Open the Navigation Pane menu and click Object Type under Navigate To Category. Open the menu again and click Modules under Filter By Group to display a list of modules available in the Housing Reservations database, as shown in Figure 3–30. The Housing Reservations database has several module objects that contain procedures that can be called from any query, form, report, or other procedure in the database. For example, the modMedian module contains a function to calculate the median value of a column in any table or query. The modUtility module contains several functions that you might find useful in your applications.

Figure 3–30: You can filter the Navigation Pane to display only the Visual Basic modules in the Housing Reservations database.

From the Navigation Pane, you can create a new module by clicking the arrow below Macro in the Other group of the Create tab on the Ribbon, or you can open the design of an existing module by double-clicking the name of the module in the Navigation Pane. In addition, you can right-click on the module name in the Navigation Pane and click Design View on the shortcut menu. In a module, you can define procedures that you can call from a macro, a form, or a report. You can also use some procedures (called functions) in expressions in queries and in validation rules that you create for a table or a form. You’ll learn how to create procedures in Chapter 19.

Right-click the modUtility module in the Navigation Pane and then click Design View to open the Visual Basic Editor window containing the Visual Basic code in the module. Use the Procedure list box (in the upper right of the Code window) to look at the procedure names available in the sample. One of the functions in this module, IsFormLoaded, checks all forms open in the current Access session to see whether the form name, passed as a parameter, is one of the open forms. This function is useful in macros or in other modules to direct the flow of an application based on which forms the user has open. You can see this function in Figure 3–31.

Figure 3–31: The Visual Basic Editor window displays the IsFormLoaded function in the modUtility module.

Note that the Visual Basic Editor runs in an entirely different application window from Access, and it still uses the classic menus and toolbars found in earlier versions of Access. Click the View Microsoft Office Access button on the far left of the toolbar to easily return to the Access window.

This completes the tour of the objects in the Housing Reservations sample database. Close the Visual Basic Editor window if you still have it open, return to the Access window, and close the database.