Opens the New File task pane, where you can choose a blank, empty database to start from, or choose one of Access’s built-in templates (Up to Speed: Database Templates). The pane also has links to more templates on Microsoft’s Web site, as discussed in Appendix B. Keyboard shortcut: Ctrl+N.

Opens Windows’ standard Open dialog box, where you can choose an existing database file to open from anywhere on your computer or network. Keyboard shortcut: Ctrl+O.

This oddly named command reveals two different options on its submenu:

Closes the current Access window. To close Access completely, use File → Exit. Access asks if you want to save before closing.

The Save command in Access works a little differently than in most other programs, in that you don’t have to worry about saving records. Access saves each record automatically as soon as you leave it and go on to the next one. The Save command instead saves design changes that you make to database objects. That’s why, when you switch from Design view to another view, or close a design window, Access asks if you want to save your changes. When you’re using a form, entering records, don’t bother with this command. Keyboard shortcut: Ctrl+S.

In Access, Save As is multitalented. Instead of just letting you save the current database file under a new name, it lets you do the same thing to whatever individual object is in the front-most window—like a form. Remember that an Access database file is like a container containing tables, forms, and other database objects, so the program lets you save and copy them individually.

To save your entire Access database file (.mdb), see the next command.

Opens a typical Windows Save As dialog box, to create an exact copy of your entire database file. Access suggests adding today’s date to the file name, but you can use any naming system you choose. Since it creates a completely normal database file, you can use it any time you need a duplicate, not just as a backup.

Starts the process of exporting the current table or other database object. The Export dialog box lets you choose a name, location, and file type for the exported material. For instance, you can save a table as another Access table, or as an Excel or text document, so you can use its information in another program.

Opens the task pane to the Basic File Search panel. If you can’t find the database yourself in the Open dialog box, you can tell Windows to go look for it by typing some text that you know is in the desired file. The pane also lets you limit your search to certain folders, drives, and file types, and to a handy Search Tips link if you need assistance.

Usually used before you print a report or other Access document. The Page Setup dialog box lets you choose margins, paper size, column layout, and so on. (As with the Open dialog box, the options you see here are pretty standard to Windows.)

Opens the standard Windows Print dialog box, where you can select a printer, choose which pages to print and how many, and so on. In Access, you usually print a report of your database’s information (Printing a Report). Keyboard shortcut: Ctrl+P.

The Send To command is Access’s way of making it easy to email your database to someone. The submenu has two options, but the second one, Mail Recipient (as Attachment), is usually the only one available. Choosing it creates an attachment from the current window or selection, and then opens your email program with a new message ready to address and send.

(The first option, Mail Recipient, places selected information right in the body of an email. Most Access windows don’t lend themselves to that treatment.)

Opens the properties sheet for the database file, where you can view some statistical information, type a title, author, and so on. Windows and other programs, and some technical-type people, use this information to learn about your file.

At the bottom of every File menu is a list of the last few databases you’ve had open. Instead of using the Open dialog box, scooting down here to choose one is a much faster way to pick up where you left off in Access. You can change the number of files in this list, or eliminate the list entirely, in the Tools → Options → General panel.

Closes the Access window and everything in it. If you’ve made any database changes and not yet saved them, Access gives you a chance to do so before exiting. If you’re not sure, click Cancel to stop the whole exit process and go right back to your database.

Takes back the last thing that you did. The wording of the command varies depending on what you just did and what window you’re in. Undo can toss out the last change you made to a record, or put something you dragged in Design view back where it started. Keyboard shortcut: Ctrl+Z.

When you’re entering information into a table, datasheet, or form, this command lets you back out of any changes you’ve made to that record—that is, until you go on to another record. When you do, this command changes to Saved Record, giving you one last chance to return to the previous record before you made those changes. As soon as you start typing in the new record, however, the Undo command erases any memory of the previous record and turns back into a normal Undo command. Keyboard shortcut for all these options: still Ctrl+Z.

Once you’ve undone something, the Redo command becomes available, in case you change your mind. Again. Keyboard shortcut: Ctrl+Y.

Cuts the selected text or object out of the document and puts it on the Clipboard, ready for pasting into a different window or program. Works on whatever you’ve selected—text, records, or (in the Database window) objects. Keyboard shortcut: Ctrl+X.

Copies the selected text or object and puts it on the Clipboard, ready for pasting into a different window or program. It works just like Cut, but duplicates instead of deleting what you’ve selected. Keyboard shortcut: Ctrl+C.

Opens the Office Clipboard in the task pane, which lists the last 24 things you’ve cut and copied in Access (and any other Office programs you have open). You can go back and reuse any of these clippings until you exit Access, which clears the clipboard. Keyboard shortcut: Press Ctrl+C twice.

Places whatever you’ve cut or copied where the cursor is blinking. Keyboard shortcut: Ctrl+V.

Opens the Paste Special dialog box, where you can change the format of what you’re pasting. For example, you can paste the contents of a record you’ve copied as plain text, without all the formatting that makes Access recognize it as a record. (Expand the Edit menu if you don’t see the Paste Special command.)

Pastes the selected item as a link you can click to go to another file (Hyperlink).

Places the cut or copied fields in a new record at the end of the table. This highly useful, but little-known, command is designed especially for pasting record information. If you try to paste that information using the standard Paste command, you get an error message.

(Form and Report Design views only.) Copies the selected item and puts it right there on the screen next to the original.

(Database window only.) Makes a shortcut icon of whatever you’ve selected in the Database window, just like a shortcut on the Windows desktop.

Completely removes the selected text, record, or object without copying it to the clipboard. Before deleting an entire record or object, Access asks for your confirmation. Keyboard shortcut: Del.

(Database window only.) Lets you type a new name for a table or other database object.

(Database window only.) Lets you create groups of tables, forms, and other objects in your database. Great for reducing Database window clutter as you add objects to your database. For instance, you can create a separate group for the reports you use only at tax time, and you don’t have to look at them the rest of the year. Just click the group’s folder in the Objects bar when April 15 looms.

A submenu lets you Rename and Delete groups just like other database objects.

(Database window only.) Opens a submenu listing any groups you’ve created, plus a built-in group called favorites. Choose one to add the selected object to that group.

Below the Delete command, the menu may show more specific options.

Selects whatever record you’re currently in, a real convenience in Form view.

In Design and Query views, selects all the labels and fields in the current window, so you can cut or copy them.

Selects all records in the current table. Keyboard shortcut: Ctrl+A.

In Design view, lets you select an entire form to paste (in a new design window, say).

Opens the Find and Replace dialog box, as discussed in detail in Finding Find. Keyboard shortcuts: Ctrl+F to open to the Find tab, Ctrl+H to open to the Replace tab.

Opens a submenu where you can jump directly to the First, Last, Next, and Previous records. It’s the menu equivalent of the buttons at the bottom of form and table windows, but may be even faster if you memorize the Alt key combinations (see the box in Up to Speed: Keep Them Fingers Movin’). At the bottom of the submenu is New Record, which has its own nifty shortcut: Ctrl++.

Opens a window where you can see all the links in the current database. This feature is available only if you’ve created linked objects (Cut), or in huge, complex database systems created by network administrators or Web designers.

Switches the current form to Design view, as discussed in detail in Chapter 3.

(Query window only.) Shows the underlying SQL statement for a query (Learning the Lingo: What’s a Dynaset? What’s SQL?).

Switches Design view back to Form view.

Takes you to the Datasheet view (Figure 1-17) for whatever table you’re currently working in. In other words, it lets you see your table as a table.

Opens the Pivot Table view for the current form, or, if there isn’t one, opens a blank pivot table screen and the Pivot Table Field List, so you can drag and drop fields to create the pivot table.

Similar to Pivot Table view, it lets you drag fields onto a graph to display your information in one of those charts that make such a good impression at company meetings.

(Query window only.) Shows (or hides) the names of the tables that each query field comes from. You may want to hide names if they’re all in the same table.

(Query window only.) Adds a Total row to your query form (Figure 7-12).

(Design view only.) Lets you add a subform to your form, as discussed in Creating Subforms.

(Database window only.) Lists the same things as in the Objects bar: Tables, Queries, Forms, Reports, Pages, Macros, Modules, and any Group folders (Up to Speed: Database Objects Defined). Choose one to go to that panel of the Database window.

(Database window only.) Lets you choose the size of the icons displayed in the Database window.

(Database window only.) Switches the Database window to a list view.

(Database window only.) Similar to list view, has columns that display information about each object in addition to the name, like a description, and the date you last modified it.

(Database window only.) Sort would be a better name for this command. It lets you choose how to sort the icons in the Database window—alphabetically by name, chronologically by the date you created them, and so on. Turning on AutoArrange keeps them in that order automatically whenever you move, add, or delete objects.

(Database window only.) If you’ve dragged objects around (which you can do in an icon view), this command puts them back into neat rows.

Opens the Properties sheet (Properties and the Property Sheet) for the current window or selected object. Keyboard shortcut: Alt+Enter.

(Design view only.) Opens a separate window listing all the fields in the current table, so you can drag them onto a form or report you’re creating.

(Design view only.) Opens the current form in Microsoft Visual Basic Editor. In this window, you can create and modify macros using the Visual Basic programming language. The tools in this window make Visual Basic easier to use than most programming languages, but you’ll need a little instruction first. (If you end up in Code view by mistake, in the upper-left corner, click the little Access icon to return to Access.)

Displays these elements of the Design window (Creating Forms in Design View).

Opens the panel of Design tools (Jump Start: Using the Toolbox).

(Design view only.) Adds these elements to your layout (Report header and footer).

Opens the Office task pane at the right of your screen, to whatever pane you were last looking at. Keyboard shortcut: Ctrl+F1.

Opens the Object Dependencies task pane, where you can see what tables, forms, queries, and reports in some way rely on what you’re looking at in the current window (or vice versa). For instance, if you’re looking at a form that displays customer information, you’ll see the table that contains your customer records in the dependencies list. Because your form gets its information from that table, Access says the form has a dependency to the table.

What this dependency means to you is, if you delete the table, you’re going to render the form useless. But you can delete the form without harming the table at all. It’s a good idea to consult the Object Dependencies pane before you delete a table or any major object in your database.

(Table window only.) Lets you index fields in your database (Figure 2-9).

(Query and Relationship windows only.) Opens the Join Properties dialog box, where you can create links between tables (Power Users’ Clinic: More Ways to Join Up).

Opens a submenu that lists all available toolbars in Access. You have complete control over which toolbars are open, but Access usually keeps one open for you depending on the view you’re in: Table Datasheet, Form view, or Form Design. This main toolbar contains the Save, Cut and Paste, Undo and Redo, and other commands you’re most likely to use. Other options are:

Lets you choose how big to see the page on your screen, from 1000% all the way down to 10%. You can also Fit to Window. Zoom is usually available only in Report view.

Lets you choose how many pages to display onscreen at the same time, and is mostly useful for getting an overview of a report.

Access lets multiple people use a database simultaneously on a network. Choosing Refresh tells Access to update your window with changes others have made.

Adds a new record to your database, ready to fill in. Keyboard shortcut: Ctrl++.

Adds a new field to the current table. In Datasheet view, a new field is a column. In Table Design view (Figure 1-18), it’s called a row, since fields in that window are arranged in rows. Fortunately, you don’t have to worry about choosing the right one—Access shows only the appropriate command for the view you’re in.

Starts the Lookup Wizard, which lets you add a lookup field to your table. You can create a column of new values, or utilize information in a field that actually belongs to another table (11). It’s a way of keeping these values separate from your main table, without creating a relational database as discussed in Chapter 4.

Opens the Insert Subdatasheet dialog box, where you can choose a table or query to become a subdatasheet to the datasheet you’re currently working in (Figure 4-2).

(Design view only.) Lets you add page numbers and the current date and time to the header or footer of a form, query, or report.

(Design view only.) Launches the Chart Wizard, where you can choose information from any table in your database and format it as a chart. The chart form updates automatically as you add and edit information in your database. Charts in Access work just like charts in Microsoft Excel, and you can learn all about them in Excel: The Missing Manual by Matthew MacDonald.

In Design view, lets you add an Office object to your form, which can be an Excel chart or worksheet, a Word document, a picture, and so on. It gives you a way of using, say, Word’s powerful text formatting tools in your database.

(Design view only.) Places an ActiveX control on your form. ActiveX controls are part of Microsoft’s Visual Basic scripting system (Code). For one very simple example, they let database gurus create buttons that launch a script when clicked. To learn more, Access Database Design and Programming, 3rd Edition, by Steven Roman (O’Reilly) is a great reference.

(Design view only.) Opens the Insert Hyperlink dialog box, where you can navigate to and select the thing you want to link to, be it a Web page, another part of your database, a document on your computer, or an email address.

(Table window only.) Adds a new column to your table that’s specially formatted to link to records in another table (Up to Speed: That Tricky Autonumber Data Type).

(Design view only.) This command adds a new tab page like the one you can see in Figure 1-3 to your form. Once you’ve got the tab, you can drag fields onto it just like any other form.

Opens the Font dialog box, where you can choose any of the fonts installed on your PC, as well as size, bold or italic, and other effects.

Opens the Datasheet Formatting dialog box, where you can choose colors for the sheet’s background and its cell borders. You can also add sophisticated-looking effects like a 3-D raised or sunken look to each cell.

Opens a small dialog box where you can type the desired dimensions in points.

Controls the clutter factor by choosing which columns to show in Datasheet view. (This command doesn’t actually delete columns.)

Lets you freeze a column (usually the left-most one, containing record numbers or names), so that it stays in place as you scroll to the right to view the rest of the columns.

This submenu lets you control the display of a subdatasheet: You can expand all the child items or remove the subdatasheet. (This command is available only after you’ve inserted a subdatasheet.)

(Form/Report design views.) Lets you choose one of Access’s built-in themes, like Blends or Ricepaper for forms, or Casual or Corporate for reports. When you’re in a hurry, they’re fine, if a little bland. When you choose one in the AutoFormat window and click OK, Access instantly applies it to your entire layout.

(Form/Report design views.) When you choose this command after clicking one of the fields or controls on your form, it opens the Conditional Formatting dialog box. This window lets you select a different appearance for the field when certain conditions are met—like making negative numbers appear “in the red.”

(Form/Report design views.) When you’re designing your own form, this submenu helps you get the fields and/or labels lined up. You can line them up by their left edges, or choose Right, Top, or Bottom. The To Grid option neatens up your form by sticking your fields or labels to the nearest gridline (Phase 2: Choosing a Data Source).

(Form/Report design views.) Oddly, this submenu doesn’t let you specify size, as you would when formatting text. Instead, it’s a smoother-outer, like the Align submenu above. You can choose:

(Form/Report design views.) Available only when you’ve selected multiple fields, this command offers three options for quickly regularizing their arrangement. To use it, select only fields across from each other, not one beneath the other, and don’t select labels. Otherwise, the command doesn’t work properly. Then select one of the following options from the submenu:

(Form/Report design views.) Does the same thing as Horizontal Spacing, but works on selected fields stacked on top of one another. The Increase and Decrease options keep the top-most field in place and realign all the others.

(Form/Report design views.) When you select multiple fields and/or labels and then choose this command, Access considers them as a group and shows a thick black border all the way around them to indicate as much. Dragging, cutting, pasting, and aligning all work on the group as a whole.

(Form/Report design views.) Removes the groupness from the selected group, returning objects to their individual state.

(Form/Report design views.) When layout objects overlap (intentionally), you can choose which one goes in the foreground or background.

This submenu gives you four options:

Offers a submenu with two options, Ascending and Descending. Works on whatever field your cursor’s currently in. (Doesn’t appear in Form view.)

Toggles between sorted/filtered records and unsorted/unfiltered records (whichever you did last).

Saves the record you’re currently working in. Keyboard shortcut: Shift+Enter.

Updates what’s in the current database window with changes other people have made (Zoom).

Switches the form, datasheet, or query to a special, blank form, ready for you to add a new record to your database. On the toolbar, click the Filter button, or choose Records → Remove Filter/Sort to return to the normal view.

Checks the spelling of the text in the current record or table, and doesn’t work in Design view.

The three options on this submenu open wizards that help you connect your database information to another Office program (Word or Excel). For example, for mass mailings, you can merge address information in Access with a letter written in Word.

Choosing the Meet Now option launches Microsoft Net Meeting.

Launches Office’s Speech Recognition feature, which lets you say commands instead of choosing them. When you choose Speech for the first time, a wizard, complete with video, opens to guide you through the process of training Access to recognize your voice.

Opens Access’s Relationships window (A Relational Database Tour).

Lets you check for possible problems in your database, or just learn more about how it works. You have three options:

The options on this submenu let you work with your database file as a whole:

This command opens a submenu with options that let you control who has access to your database.

This advanced feature lets you add a copy of a table from one database to another and keep the information updated in both places. It’s network administrator territory.

Once you’ve set security options like accounts and permissions, you can use this dialog box to control what happens when your database folks open the file. For instance, you may want to hide the database window from data entry people, so they have to use your switchboard (Opening a Database), which shows only the forms they’re supposed to use.

Opens the Visual Basic Editor (Code). The submenu also lets you run macros that you or someone else has written into the database. The Security option lets you turn some or all macros off, since macros can be a vehicle for computer viruses.

Opens the Add-In Manager, a wizard that lets you install add-ins to your copy of Access (the add-in must be on your computer first). Add-ins are mini-programs that add supplementary features to Access. (The Add-In Manager itself is, in fact, an add-in.) Usually, you get add-ins by downloading them from Microsoft’s Web site (Visiting Microsoft Office Online) or a third-party resource like www.softwareaddins.com.

See Lookup Column/Lookup Field earlier in this appendix.

Opens the AutoCorrect dialog box. If you’ve used Microsoft Word, this window may look familiar. Like Word, Access automatically corrects your typing if you forget to capitalize the first letter of a sentence, hit Caps Lock by mistake, and so on. In this window, you can tell Access to stop trying to fix things for you.

Access is a very customizable program: You can add and remove commands from menus, create your own toolbars, and more. This dialog box has three tabs—Toolbar, Customize, and Options.

Figure C-1. The last item on the Categories list is called New Menu. When you click it, you can drag New Menu to the menu bar. Click Modify Selection (or right-click the New Menu) to name it. For example, create a new menu named Reports. Click All Reports in the Categories list, and drag the report icons, one by one, up to the menu. Wait until the I-beam appears before releasing the mouse.

The Options dialog box offers 12 tabs, each of which lets you control how a different feature of Access looks and acts. Most of the time, you won’t need to adjust any of these, although the General tab is worth a visit. This panel lets you set up some useful features like the following:

Power Users’ Clinic: Modding Toolbars and Menus

The Customize dialog box is like no other in Access. When it’s open, your whole Access window becomes a dialog box. The toolbars and menus look the same as always, but now you can move and edit them at will.

The Toolbars tab lists all toolbars in Access. You can turn these toolbars on and off, to control which ones appear automatically, as explained in Add-Ins. When you turn one on, it immediately appears at the top of your screen.

When the Customize dialog box is open, you can remove toolbar buttons by dragging them off the toolbar. When you see the X icon at your pointer, let go. Poof—it’s gone. Click Reset to restore customized toolbars to original condition at any time.

You can also reposition toolbar buttons by dragging them to the new position—indicated by a thick, black I-beam. (If you click Undo a lot, you may want to put that button at the far left or far right of the toolbar, so you have an easier time spotting it.)

The Commands tab lists all Access commands. Drag them from the list onto any onscreen toolbar to add those command to the toolbar. If the command has a button, the button appears on the toolbar. If there’s no button, the wording of the command appears instead.

Just like toolbar buttons, you can also drag the menus in the menu bar to delete and rearrange them. Also, when you open a menu, you can drag commands on and off it from the Commands panel.

Shrinks all your open windows so they fit on the screen at once, so you can see them all with no overlap. Horizontally creates wide, short windows, and Vertically crates tall, skinny ones. For example, if you have five windows open and choose Tile Vertically, you get five skinny windows lined up across your screen. Next, you can resize them any way you like.

If you have lots of open windows, the previous two commands may make them too small to recognize. The Cascade option overlaps them in a stack, with just the title bars showing. You can use the title bar to identify the window you’re looking for.

Hides the front-most window. Unhide makes all hidden windows visible again.

Places the bars for minimized windows in a neat line along the bottom of your Access window. It’s a cousin to the View → Arrange Icons command in the Database window, except it works on the “icons” for open windows, not closed ones.

Just like the list of files at the bottom of the File menu, the Window menu lists all open windows. Just choose the one you want to bring to the front.