Read an Excerpt
Chapter 3: Navigating Within Access
Understanding Access Functions and Modes
Access, unlike word processing and spreadsheet applications, is a truly multifunctionalprogram. Although word processing applications, for example, have many sophisticatedcapabilities, their basic purpose is to support text entry, page layout, and formattedprinting. The primary functions and supporting features of all word processing applicationsare directed to these ends. You perform all word processing operations with viewsthat represent a sheet of paper--usually 8 1/2x11 inches. Most spreadsheet applicationsuse the row-column metaphor for all their functions. In contrast, Access consistsof a multitude of related tools for generating, organizing, segregating, displaying,printing, and publishing data. The following sections describe Access's basic functionsand operating modes.
Defining Access Functions
To qualify as a full-fledged relational database management system (RDBMS), anapplication must perform the following four basic but distinct functions, each withits own presentation (or view) to the user:
- Data organization involves creating and manipulating tables that contain data in conventional tabular (row-column or spreadsheet) format, called Datasheet view by Access.
- Table linking and data extraction link multiple tables by data relationships to create temporary tables, stored in your computer's memory or temporary disk files, that contain the data you choose. Access uses queries to link tables and to choose the data to be stored in a temporary table called a Recordset objecRecordset object consists of the data that results from running the query; Recordset objects are called virtual tables because they are stored in your computer's memory rather than in database files. The capability to link tables by relations distinguishes relational database systems from simple list-processing applications, called flat-file managers. Data extraction limits the presentation of Recordsets to specific groups of data that meet criteria that you establish. Expressions are used to calculate values from data (for example, you can calculate an extended amount by multiplying unit price and quantity) and to display the calculated values as though they were a field in one of the tables.
- Data entry and editing require design and implementation of data viewing, entry, and editing forms as an alternative to tabular presentation. A form enables you, rather than the application, to control how the data is presented. Most users find forms much easier to use for data entry than Recordsets in tabular format, especially when many fields are involved. Data entry in forms is a requirement for intranet- and Internet-based applications. The capability to print forms, such as sales orders and invoices, is definitely a benefit to users.
- Data presentation requires the creation of reports that can summarize the information in Recordsets that you can view, print, or publish on the Internet or an intranet (the last step in the process). The capability to provide meaningful reports is the ultimate purpose of any databa attractively formatted reports that contain charts or graphs. Charts and graphs summarize the data for those officials who take the "broad brush" approach.
The four basic functions of Access that are implemented as views are organizedinto the application structure shown in Figure 3.1. If you are creating a new database,you use the basic functions of Access in the top-down sequence shown in Figure 3.1.You choose a function by clicking a button in the Datasheet window (except for securityand printing operations, which are menu choices). In most views, you can displaythe Print Preview window that leads to printing operations by clicking the toolbar'sPrint Preview button.
- FIGURE 3.1 The basic and supporting functions of Access.
Five supporting functions apply to all the basic functions of Access:
- Macros are sequences of actions that automate repetitive database operations. You create a macro in Access by choosing from a list of available actions in the order in which you want Access to perform them. You can use a macro, for example, to open a report, print the report, and then close the report. (Later this section defines open and close as they are used in Access terminology.) In prior versions of Access, macros were the primary means of automating database operations. In Access 2000, macros are supported primarily for purposes of compatibility with databases created in earlier Access versions. For Access 2000 databases, you use Visual Basic for Applications (VBA) to automate database actions.
- Modules are f functions to make calculations that are more complex than those that can be expressed easily by a series of conventional mathematical symbols or to make calculations that require decisions to be made. VBA subprocedures are written to perform operations that exceed the capabilities of standard macro actions--one reason macro support is being dropped from Access. You run VBA subprocedures by attaching the subprocedure to particular events, such as clicking a command button with the mouse, that occur when a form or report is the active object. In Access 2000, you can also execute VBA procedures directly from their module.
- Security consists of functions available as menu choices and through VBA subprocedures. With security functions in a multiuser environment, you can let other people use your database. You can grant access to user groups and individuals, and you can restrict their ability to view or modify all or a portion of the tables in the database.
- Printing lets you print virtually anything you can view in Access's run mode. From the toolbar, you can print your VBA code, but not the macros that you write. (You can use the Documenter to print the content of your macros.)
- NEW 2000: Publishing features facilitate distribution of information over corporate intranets and the public Intranet as World Wide Web pages. Access 2000 adds Data Access Pages (DAP) that let you build applications for displaying and updating data in pages that take advantage of Dynamic HTML (DHTML) and Extensible Markup Language (XML).
The terms open and close than one basic function:
- Opening a database makes its content available to the application through the Database window. You can open only one database at a time during ordinary use of Access. Writing VBA code enables you to work with tables from more than one database. You can achieve the equivalent of multiple open Access databases by linking (Access 2000's term for attaching) tables from other databases.
- Opening a table displays a Datasheet view of its contents.
- Opening a query opens the tables involved but does not display them. Access then runs the query on these tables to create a tabular Recordset. Changes made to data in the Recordset cause corresponding changes to be made to the data in the tables associated with the query if the Recordset is updatable.
- Opening a form or report automatically opens the table or query that's associated with it. Forms and reports usually are associated with queries, but a query also can employ a single table.
- Closing a query closes the associated tables.
- Closing a form or report closes the associated query and its tables.
Defining Access Operating Modes
Access has three basic operating modes:
- Startup mode enables you to compress, convert, encrypt, decrypt, and repair a database by choosing commands from the Tools menu's Database Utilities and Security submenus before opening a database. These commands, some of which are discussed at the end of this chapter, are available only when you don't structure of tables and queries, develop forms to display and edit your data, and format reports for printing. Access calls design mode Design view.
- Run mode displays your table, form, and report designs in individual document windows (the default mode). You execute macros by choosing one and then selecting run mode. Run mode does not apply to VBA modules, because functions are executed when encountered as elements of queries, forms, and reports. Procedures in modules are run by macro commands or directly from events of forms and reports. Run mode is called Datasheet view for tables and queries, Form view for forms, Page view for Data Access Pages (DAP), and Print Preview for reports.
You can select design or run mode by choosing command buttons in the Datasheetwindow, buttons on the toolbar, or commands from the View menu.
TIP #17 FROM RJ: To change the default conditions under which Access displays and prints your tables, queries, forms, and reports, choose Options from the Tools menu. The section "Setting Default Options" later in this chapter describes options that apply to Access as a whole and those that apply only to tables.
Understanding Access's Table Display
You're probably familiar with the terms for many of the components that comprisethe basic window in which all conventional Windows 9x applications run. The presentationof Access windows varies with each basic function that Access performs. Because PartI, "Learning Access Fundamentals," of this book deals almost Table Datasheet view. Figure 3.2 showsAccess 2000's basic display for operations with tables; Table 3.1 describes the window'sAccess-related components.
- FIGURE 3.2 Access 2000's basic display for tables.
TABLE 3.1: Components of the Access Display for Tables
| Term | Description |
| Current Record button | A button that indicates a single selected record in the table. When you are editing the current record, the button icon displays a pencil rather than a triangular arrow. The Current Record button also is called the record pointer. |
| Current Record selection | Buttons that position the record pointer to the first, next, preceding, and last record number in the table and show the number of the currently selected record. If the table has a key field, the record number reflects the sequence of records in the primary key's sorting order; if there is no primary key in the table, the record number corresponds to the order in which records were physically added to the table. |
| Database window | The toolbar that enables you to open or design the currently toolbar selected database object, create a new object, delete an object, and control how icons display in the Database window. |
| Field scroll bar | The scroll bar that enables you to view table fields that are outside the bounds of the document window. Record scroll bars provide access to records located outside the document window. |
| Menubar | A bar containing Access main menu choices. The specific menubar choices and the commands in the menus themselves change depending on Access's status. Menubars and toolbars collectively are called command bars. |
| Mode buttons | Three buttons that determine the operating mode of Access. Open places Access in run mode. New or Design puts Access in design m object. |
| New record | A button with an asterisk that indicates the location of the next record to be added to a table. Typing data in the new record appends the record to the table and creates another new record. |
| NEW 2000: Objects bar | An Outlook-styled bar with shortcuts to lists of database objects. |
| NEW 2000: Open subdatasheet | A click on the plus sign in the square box opens subdatasheet(s) for each record if the table has subdatasheets. |
| Status bar | A bar, located at the bottom of the application window, that displays prompts and indicators, such as the status of the Num Lock key. |
| Toolbar | A bar containing command buttons that duplicate the more commonly used menu choices. The actual number and type of toolbar buttons depend on which basic function of Access you are using. |
Maximized Document Windows
< Maximize button, thedocument window takes the place of the application window and occupies the entiredisplay, except for the menubar and toolbar (see Figure 3.3). Most other Windowsapplications that display multiple documents, such as Word and Excel, have a similarcapability to expand a document to occupy the entire window.- FIGURE 3.3 An Access table in a maximized document window.
NOTE: Access is--and always has been--a multiple document interface (MDI) application. MDI applications have a main (parent) window, which contains multiple document (child) windows. Access 2000 windowing differs from Word 2000 and Excel 2000, both of which have migrated from their prior versions' MDI to the single document interface (SDI). SDI applications display each document in its own main window, which is represented by a document icon in the taskbar, and don't support child windows. Access 2000, by default, emulates SDI by displaying a document icon in the taskbar for each of its child windows.
If your first introduction to the components of Microsoft Office is Office 2000,you might not understand the difference between SDI and MDI components. Thus thisand the following section offer a detailed explanation of MDI windowing. If you'refamiliar with MDI, skip these two sections.
The document Control-menu box and the document Minimize, Restore, and Close buttonsmove to the menubar's extreme left and right, respectively. The document's titleis added to the application title bar at the top of the display. To return the documentwindow to its original size (establishe first active),click the document Restore button; alternatively, click the document Control-menubox and then choose Restore from the document Control menu. You can close the documentwindow by clicking the document Close button or by double-clicking the document Control-menubox. If you accidentally click the application Close button (or double-click theapplication Control-menu box just above the document Control-menu box), however,you close Access 2000. You receive no warning that you are about to exit Access unlessyou've changed the design of an object without saving those changes.
Document Windows Minimized to Icons
Working with several overlapping windows limits each to a size that enables youto select another by clicking its surface. This overlapping might overly restrictyour view of the data that the windows contain. You can minimize Access documentwindows and the Database window to icons that remain within the application window,as shown at the bottom of Figure 3.4. If you minimize a document window to an iconrather than close it, you can quickly return the window to its original size by double-clickingthe icon. If you single-click the icon, you can choose how the window reappears byusing the document Control menu, as shown for the Northwind Database window in Figure3.4. You can also restore, maximize, or close a minimized icon by clicking the correspondingbutton within the icon.
- FIGURE 3.4 Tables, a query, a form, and the Database window minimized to icons within the application window.
If you choose to display your document window in maximized form by choosing Maximizefrom the document Control menu that appea click the icon, the documenthides the icons at the bottom of the application window. In this case, choose thedocument that you want from the Window menu. If you size your document windows(like the window in Figure 3.4) by dragging their borders, you can avoid the substantialmouse movement and two-step menu-selection process to select the active document.
The Toolbars in Table Datasheet View
The buttons that appear in Access's toolbar, and the number of toolbars displayed,change according to the function that Access is currently performing. When you areworking with tables in run mode, Access 2000 displays the Table Datasheet and theDatasheet Formatting toolbars (see Figures 3.5 and 3.6). The next two sections describethe toolbars that appear in table run mode (Datasheet view). Click the Tables shortcutof the Database window and double click one of the table shortcuts--such as Customers--tofollow the text in the next few sections.
The Table Datasheet Toolbar
The Table Datasheet toolbar appears whenever you open an Access table in Datasheetview. Figure 3.5 shows the Table Datasheet toolbar, and Table 3.2 describes the buttonsthat appear on the toolbar.
- FIGURE 3.5 Access's Table Datasheet toolbar, displayed when a table is open in Datasheet view.
NOTE: Toolbar buttons provide shortcuts to traditional selection methods, such as choosing menu commands or selecting command or option buttons in a particular sequence. The Menu Sequence columns of Tables 3.2 and 3.3 list how you can duplicate the effect of clicking a toolbar button by using the menus or th the Database window.
TABLE 3.2 Appearance and Functions of Buttons and Other Elements ofthe Table Datasheet Toolbar
| Icon | Menu Button | Sequence | Function | |
| Design View** | View, Design View | Changes the table display to design mode, in which you specify the properties of each field of the table. | ||
| Spelling | Tools, Spelling | Starts the spelling checker | ||
| Format Painter | Copies a control's format to another control. Used only in Design view; it's enabled only when you select a control. | |||
| 16, "Working with Hyperlinks and HTML," describes Hyperlink fields in more detail. | ||||
| Sort Ascending | Records, Sort, Ascending | Sorts the records in ascending order, based on the current field. | ||
| Sort Descending | Records, Sort, Descending | Sorts the records in descending order, based on the current field. | ||
| Filter by Selection | Records, Filter,Filter by Selection | Filters records based on the selected text in a field. | ||
| Filter by Form | Records, Filter, Filter by Form | Enables you to type criteria in a table datasheet to establish how records are filtered. | ||
| Find | Edit, Find | Displays the Find dialog that locates records with specific characters in a single field or all fields. | ||
| New Record | Edit, Go To, New | Selects the tentative append record. | ||
| Delete Record | Edit, Delete Record | Deletes the active record. | ||
| Database | Window, 1 Window | Displays the Database window. | ||
| New Object | Displays a drop-down list from which you choose the type of new object that you want to create: tables, forms, reports, pages, queries, macros, modul ALIGN="LEFT">Activates the Microsoft Office Assistant, described later in the "The Office Assistant" section. | |||
| More Buttons | View, Toolbars Customize | Displays a drop-down list from which you can add or remove buttons from the toolbar. |
** You only see this icon if you click the drop down arrow next to the Designview icon or if you change to Design mode. This icon varies by object and mode andthe View drop down contains other mode possibilities for different objects.
NOTE: In Access 2000 and other applications in Microsoft Office 2000, the toolbar buttons appear as flat icons on the toolbar. The toolbar buttons have a raised buttonlike appearance only when the mouse pointer is over them. The exception to this rule is "toggle" buttons--that is, buttons that represent the on/off status of a feature such as the Gridlines button (refer to Table 3.3 and Figure 3.6). When a toggle button is "up," it appears as a flat icon on the toolbar until you move the mouse pointer over it; the button's "up" appearance indicates that the feature controlled by that button is off. Toggle buttons in the "down" positions are shaded so that they look as though they are below the surface of the toolbar; the "down" appearance indicates that the feature controlled by th Datasheet toolbar, you can display the Datasheet Formattingtoolbar whenever you view a table in Datasheet view. From the View menu, chooseToolbars, Formatting (Datasheet) to add the toolbar. The buttons in the DatasheetFormatting toolbar provide shortcuts to various text-formatting commands. In Datasheetview, the text-formatting commands apply to the entire table; you can't format individualcells in Datasheet view. Figure 3.6 shows the Datasheet Formatting toolbar, and Table3.3 summarizes the action of each button on the toolbar.
- FIGURE 3.6 The Datasheet Formatting toolbar in Datasheet view.
TABLE 3.3 Appearance and Functions of Buttons and Other Elements ofthe Datasheet Formatting Toolbar
Icon Menu Button Sequence Function Go To Field Displays a drop-down list from which you can jump quickly to any field in the table. Font Format, Font Lets you sele ALIGN="LEFT">Font Size Format, Font Lets you select the size of the text in a table. Bold Format, Font Turns bold text formatting on and off for the text in a table. Italic Format, Font Turns italic text formatting on and off for text in a table. Underline Format, Font Turns underlining on and off for text in a table. Fill/Back Color Format, Cells Displays a palette of colors from which to choose the background color for the table's data cells. VALIGN="TOP"> Line/Border Color Format, Cells Displays a color palette from which to choose the color of the gridlines that indicate rows and columns in the table. Gridlines Format, Cells Displays four buttons that enable you to choose which gridlines are shown: horizontal and vertical, vertical only, horizontal only, or none. Special Effect Format, Cells Displays three buttons that let you choose the cell display style: flat, raised, or sunken.
NOTE: This chapter concentrates on toolbars for Table and Query Datasheet and Table Design views. Chapter 12, "Creating and Using Forms," describes the toolbars for Form and Form Design views. Chapter 14, "Printing Basic Reports and Mailing Labels," explains the elements of the Report Design and Print Preview toolbars.Toolbar Customization
Access 2000 uses the resizable command bar, and share many features. The primary characteristic that distinguishesa menubar from a toolbar in Access 2000 (and other Office 2000 applications) is thatevery application has at least one menubar, and the menubar may not be hidden. Inall other respects, menubars and toolbars are the same.
The View menu's Toolbars command lets you select which toolbarsare currently visible. The Toolbars submenu lists those toolbars pertinentto Access's current operating mode. Figure 3.7 shows the Toolbars submenufor Table Datasheet view. A mark at the left of a menu choice indicates that thatspecific toolbar is now displayed. To display or hide a toolbar, click its name inthe submenu.
- FIGURE 3.7 Displaying or hiding toolbars with the View menu's Toolbars choice.
NOTE: In Figure 3.7 and other figures throughout this book, you might notice a menu that isn't discussed in the text--Show Me. This menu appears on the menubar only when the Northwind.mdb database is open. The Show Me menu isn't part of Access 2000; instead, it's displayed by Northwind.mdb. If you're interested in viewing help topics relating to the VBA code that is part of the Northwind.mdb sample database, open the Show Me menu.The Customize choice on the Toolbars submenu opens the Customizedialog (see Figure 3.8), which lets you display as many toolbars at once as willfit in your display or hide toolbars that Access would otherwise display automatically.To display a toolbar, click the Tool page (if necessary)and then click the box to the left of the toolbar name so that the check box is marked.To hide a toolbar, click the box again to clear it.
- FIGURE 3.8 Selecting the toolbars to be displayed in the Customize dialog.
TIP # 18 FROM RJ: For help in using the Customize dialog, click the Office Assistant button at the lower-left area of the Customize dialog to activate the Office Assistant if it isn't already active (see "Using the Office Assistant" later in this chapter).
TIP # 19 FROM RJ: When an Access toolbar is in its docked position, it has a fixed width, anchored at its left edge. If you reduce the width of Access's application window by dragging either vertical border inward, the buttons at the docked toolbar's extreme right begin to disappear beyond the application window's right edge. Operating Access in a maximized window with docked toolbars is usually best because you can then easily access all the toolbar buttons when you use the default inline horizontal toolbar.You also can use the Customize dialog to change the viewing options for toolbars.The Options page enables you to select various toolbar viewing options (see Figure3.9). If you're using XGA 1,024x768 screen resolution, you might want to mark theLarge Icons check box to cause the toolbar button icons to approximately double insize, making them easier to discern and easier to click. The Show ScreenTips on Toolbarscheck box governs whether Access displays ScreenTips (formerly known as ToolTips),that is, hints on the Keys inScreenTips check box determines whether Access displays the keyboard shortcut (ifthere is one) as part of the ScreenTip text.
NEW 2000: The List Font Names in Their Font check box, if checked, displayseach font name as a sample of the font in lists of fonts. If you mark the check box,font lists use the standard font and display faster. The Show Recently Used CommandsFirst check box, if marked, displays only the menu choices that you use on a regularbasis, a feature Microsoft calls Intellimenus. The Show Full Menus After a ShortDelay check box governs whether the full Access menu is displayed after you hoveryour mouse on the menu. The Reset My Usage Data button resets menu and menu usageand toolbar settings.
Finally, the Menu Animations drop-down list lets you select how Access draws menusonscreen. You may select None (for no special effects when drawing menus), Random(Access randomly chooses an animation effect each time you open a menu), Unfold (themenu unfolds like a fan), or Slide (the menu opens like a roller-shade) as the techniquefor displaying Access's menus.
- FIGURE 3.9 Selecting viewing options for toolbars and menus in the Options page of the Customize dialog.
In addition to displaying multiple toolbars, you can reshape or reposition thetoolbars to suit your own taste. Click a blank area of the toolbar and hold downthe left mouse button to drag the toolbar to a new location. The toolbar turns intoa pop-up floating toolbar, similar to the toolbox that you use to add control objectsto forms and reports. Pop-up toolbars always appear on top of any other windows openin your application.
Figure 3.10 show floating command bars: the Table Datasheet toolbar, theFormatting Datasheet toolbar, and the Menu Bar. (These are the toolbars discussedin the preceding section that Access displays in Datasheet View mode. The Menu Bar--Access'smain menubar--was moved from its position at the top of the Access application windowto demonstrate that it's a command bar.) Command bars in their fixed position arecalled docked command bars, whereas command bars in their pop-up window arereferred to as floating command bars. Floating command bars display the MoreButtons button as part of the title bar. After you change a command bar to a floatingcommand bar (or dock it), Access always displays the command bar in that locationuntil you reposition it.
- FIGURE 3.10 Access's Table Datasheet toolbar, Datasheet Formatting toolbar, and main menubar dragged from their default positions at the top of the application window.
TIP #20 FROM RJ: You can also dock command bars (menubars and toolbars) at the bottom of the Access application window or at the left or right edge of the application window.Right-Click Shortcut Menus
Another feature that Access 2000 shares with other Microsoft applications, aswell as with Windows 9.x and Windows NT 4.0, is the shortcut menu that appears whenyou right-click an Access database object. Shortcut menus (also called pop-upor context menus) present choices that vary depending on the type of objectthat you click. Figure 3.11 shows the shortcut menu for a field of a table selectedby clicking the field name header.
- F shortcut menu for a selected column of a table.
TIP # 21 FROM RJ: Shortcut menus are quite useful and provide shortcuts to many common tasks. If you're not sure what you can do with an object onscreen, try right-clicking it to see what shortcut menu commands are available.Using the Function Keys
Access assigns specific purposes to all 12 function keys of the 101-key extendedkeyboard. Some keys, such as Shift+F4 (which you press to find the next occurrenceof a match with the Find dialog), derive from other Microsoft applications--in thiscase, Word. You combine function keys with the Shift, Alt, and Ctrl keys to enableusers to perform as many as 96 functions.
Global Function Keys
Windows, rather than Access, uses global function-key assignments, except forF11 and Alt+F1, to perform identical functions in all Windows applications. Table3.4 lists the global function-key assignments.
TABLE 3.4 Global Function-Key Assignments
Key Function F1 Displays context-sensitive help related to the present basic function and status of Access. If a context-sensitive help topic isn't available, F1 starts the Office Assistant (described later in this chapter). Ctrl+F4 Closes the active window. Alt+F4 Exits Access or closes a dialog if one is open. Ctrl+F6 Selects each open window in sequence as the active window. F11 or Alt+F1 Selects the Database window as the active window. F12 or Alt+F2 Opens the File Save As dialog. Shift+F12 or Alt+Shift+F2 Saves your open database; the equivalent of the File menu's Save command. Function-Key Assignments for Fields, Grids, and Text Boxes
Access assigns function-key combinations that aren't reserved for global operationsto actions specific to the basic function that you are performing at the moment.Table 3.5 lists the fu tables.)
For an extensive list of Access shortcut key assignments, see "UsingKeyboard Operations for Entering and Editing Data," p. 186.
TABLE 3.5 Function Keys for Fields, Grids, and Text Boxes
Key Function F2 Toggles between displaying the caret for editing and selecting the entire field. Shift+F2 Opens the Zoom box to make typing expressions and other text easier. F4 Opens a drop-down combo list or list box. Shift+F4 Finds the next occurrence of a match of the text typed in the Find or Replace dialog, if the dialog is closed. F5 Moves the caret to the record-number box. Type the number of the record that you want to display. Shift+F6 In Form Design view, cycles through the footer, body (detail section), and header, moving backward. F7 Starts the spelling checker. F8 Turns on extend mode. Press F8 again to extend the selection to a word, the entire field, the whole record, and then all records. Shift+F8 Reverses the F8 selection process. Ctrl+F Opens the Find dialog. Ctrl+H Opens the Replace dialog. Ctrl++ (plus sign) Adds a new record to the database. Ctrl+- (minus sign) Esc Undoes changes in the current record or field. By pressing Esc twice, you can undo changes in the current field and record. Also cancels extend mode. Setting Default Options
You can set about 100 options that establish the default settings for Access.(But you aren't likely to change default options until you are more familiar withAccess.) This book is a reference as well as a tutorial guide, and options are abasic element of Access's overall structure, so this section explains how to changethese settings.
NOTE: The Options dialog discussed in this chapter corresponds to the options available using standard Access (Jet 4.0) databases and not the Microsoft Data Engine (MSDE) used by Access Data Projects (ADP). See Chapter 25, "Creating Access Data Projects," for more information on ADP. . .