ms Access short kye

                       Tips and tricks for Access

APPLIES TO
Microsoft Access 2000

Administering a database

Change the default working folder    

Whenever you create a new Access database (.mdb) file or open an existing one, Microsoft Access saves it to or opens it from the default working folder. You can change the default working folder for Access database files to any folder on your computer by typing the path for the new folder in the Default database folder box on theGeneral tab of the Options dialog box (Tools menu).

Controls and expressions

Create a vertical control    

In Access, you can create a vertical label or text box on a form or report by setting the Vertical property of the control.

Set a label or text box for vertical display    

1. Open the form or report in Design view.
2. Click the label or text box.
3. On the View menu, click Properties, and then click the Other tab.
4. Set the Vertical property to Yes.

For more information about the Vertical property, click the Vertical property box, and then press F1.

Customizing Access

Add descriptions for your database objects    

In Access, you can give each of your database objects a description. An object's description appears next to the object's name in the Database window when you click Details on the View menu. Although descriptions can be up to 255 characters, you may want to keep them short so that you can view the entire description in the Database window. To add a description for a database object, right-click the object in the Database window, click Propertieson the shortcut menu, and then type your description in the Description box.

Hide the new object shortcuts    

Access provides shortcuts in the Database window that you can use to create new database objects quickly. For tables, queries, forms, reports, and data access pages, there are shortcuts for using a wizard to create the object and for opening the object in Design view. You may want to remove these shortcuts from the Database window to allow more room for your own database objects. To do so, clear the New object shortcuts check box on theView tab of the Options dialog box (Tools menu).

Show database object details    

In Access, there are many details associated with each database object. These details include the object's name, a description of the object, the date and time the object was last modified, the date and time the object was created, and the type of object (table, query, form, report, data access page, macro, or module). To show the details for your database objects in the Database window, on the View menu, click Details.

Turn off the Office Assistant    

While the Office Assistant provides easy help and hours of amusement, you may want to bypass it and use theContents, Answer Wizard, or Index tab to navigate in the Help window. To turn off the Assistant, right-click it while it is visible, and then click Options on the shortcut menu. On the Options tab, clear the Use the Office Assistantcheck box. To turn the Assistant back on, click Show the Office Assistant on the Help menu.

Databases and tables

Avoid multiple fields that contain similar data    

When you design a table in Access, avoid creating multiple fields that repeat the same kind of data. If a table stores the same kind of data in more than one field, it's hard to search for a particular piece of data because it could be in any of the fields that store that kind of data. In addition, this type of table design makes it difficult to sort data and do calculations because you might need to include data from several fields to get accurate results. For example, if you are designing a questionnaire database, you might eventually find that this table design difficult to use:

RESPONDENTID	QUESTION1	QUESTION2	QUESTION3
100	Yes	Yes	No
101	No	Yes	No
102	Yes	No	Yes

It's difficult to create a query that returns only Yes responses because that data could be in any of three fields. However, it's easy to create a query that returns this information if you use the following table design:

RESPONDENTID	QUESTIONID	RESPONSE
100	1	Yes
100	2	Yes
100	3	No
101	1	No
101	2	Yes
101	3	No
102	1	Yes
102	2	No
102	3	Yes

Because each type of information is contained in only one field, searching, sorting, and calculating data are much easier.

Access specifications    

• What is the maximum number of concurrent users? 255
• How many fields can I have in a table? 255
• What is the maximum width of a form or report? 22 inches

You can find answers to these questions and many others by typing specifications in the Office Assistant or on theAnswer Wizard tab in the Help window, clicking Search, and then clicking Microsoft Access specifications.

Entering and formatting data

The Zoom box    

Sometimes a field on a datasheet, a text box on a form, or a property box in a property sheet is too small to display all the text it contains. Use the Zoom box to read or edit this text more easily. To display the Zoom box, click the field, text box, or property box, and then press SHIFT+F2.

Formatting

Apply text and graphics formatting multiple times in Office    

Have you ever wanted to make several non-sequential words stand out by using a special font in your document? Or have you ever wanted to change certain solid lines to dotted lines in graphics created with the drawing tools? If you've ever wanted to apply the same format to items in different locations in a file, you might not realize how easy it is. Instead of clicking the Format Painter button on the Standard toolbar every time you want to apply the new format, you can take advantage of the button's "sticky" feature.

1. Select the item whose format you would like to copy.
2. To copy the selected format to several items, double-click the Format Painter button. The button stays selected, or "sticky."
3. Select the text or graphic where you want to apply the new format.
4. When you're finished applying the format, click Format Painter again, or press ESC.

 NOTE    Applying text and graphics formatting multiple times does not work between programs. For example, you cannot apply formatting from Microsoft Word to Microsoft PowerPoint®.

For more information, type format painter or copy formats in the Office Assistant or on the Answer Wizard tab in the program's Help window, and then click Search. For related information, see "Quickly copy formatting with the Format Painter in Office" and "Insert a drawing object multiple times in Office" on Office Online.

Forms and reports

Add the database name to a report    

You might find it useful to display the name of the database on the report so you can better track the source of the report and its data.

 NOTE    The following procedure uses the Northwind sample database. The default location of the Northwind sample database is the Program Files\Microsoft Office\Office\Samples folder. If you don't see Northwind.mdb in your Samples folder, or if you need more information about opening Northwind, type open the northwind sample database in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Add the database name to a report    

1. Open the Northwind database.
2. In the Database window, right-click the Invoice report, and then click Design View.
3. Click the Text Box tool in the toolbox, and then click to the left of the Date field in the report header.
4. In the label for the unbound text box you just added, type Database:
5. Right-click the text box, and then click Properties.
6. Click the Data tab, and then type the following text in the ControlSource property box:

=Left(CurrentProject.Name,Len(CurrentProject.Name)-4)

7. On the View menu, click Print Preview to see the results.

Add vertical and horizontal lines to a report    

In Microsoft Access, you can add lines between rows or columns on a report by using the line control. The following illustration shows a report that displays vertical lines between the columns in the detail section. It also displays a horizontal line between the header and detail sections.

Add a vertical line between columns in a detail section    

1. Open the report in Design view.
2. In the toolbox, click the Line tool.
3. Draw a line between the first and second controls in the detail section. The height of the line must be the same as the height of the detail section.
4. Repeat Steps 2 and 3 for each pair of controls in the detail section.
5. On the View menu, click Print Preview.
6. If you see discontinued lines when you preview your report, increase the length of each vertical line in Design view until you see a single line.

Add a horizontal line to a report    

1. Open the report in Design view.
2. Click the Line tool in the toolbox.
3. Draw a horizontal line from left to right in the section in which you want display a line.

 NOTE   If you add a line at the top or bottom of the detail section, you will see a line either before or after every detail record. In the preceding illustration, the line is at the bottom of the header section.

Discard form and report design changes made during this session    

If you open a saved form or report in Access, make some design changes, and then decide that you don't want to keep those changes, you can revert to the last saved version of the form or report without closing it. In previous versions of Access, you had to close the form or report without saving changes and then reopen it in order to discard design changes.

To revert to the last saved version, click Revert on the File menu. The Revert command is not available unless you have opened a form or report in Design view and have made changes to it.

Once you have reverted to the last saved version of the form or report, you cannot recover the changes that you have made to the form or report since you last opened it. If you are not sure whether you want to discard your changes, save the form or report with a different name instead of using the Revert command.

Move a form or report control to another section    

In an Access form or report, you can move an existing control to another section by dragging it.

Move a control to another section of a form or report

1. Open the form or report in Design view.
2. Click the control.
3. Place the pointer over the control until it changes to an open hand. You can't drag the control to another section while the pointer is an upward-pointing hand.
4. Drag the control to the other section.

If there is a label attached to the control, that label moves with the control to the other section.

 NOTE   When the mouse pointer is an upward-pointing hand, you can move a control and its label separately, but only within the current section of the form or report.

Keyboard shortcuts

Useful shortcut keys in Access    

Do you find that using the keyboard is sometimes quicker than using your mouse? Shortcut keys can help you bypass menus and carry out commands directly. You can use shortcut keys in many ways with Access, from accessing commands and toolbar buttons to inserting today's date. Shortcut keys are sometimes listed next to the command name on Access menus. For example, on the File menu, the Print command lists the shortcut CTRL+P.

For a comprehensive list of shortcuts, ask the Office Assistant for help. In Access or any of the other Office applications, press F1 to display the Assistant, and then type shortcut keys in the text box. Here are some of the most useful Access shortcut keys:

ACTIVITY	SHORTCUT KEYS
Insert today's date	CTRL+;
Insert the current time	CTRL+:
Insert a carriage return in a memo or text field	CTRL+ENTER
Insert the data from the same field in the previous record	CTRL+'
Undo the changes you have made to the current field	ESC
Undo the changes you have made to the current record	ESC ESC (press ESC twice)
Display the database window	F11
Open a new database	CTRL+N
Open an existing database	CTRL+O
Switch between the Visual Basic® Editor and the previous active window	ALT+F11
Find and replace	CTRL+F
Copy	CTRL+C
Paste	CTRL+V
Undo	CTRL+Z
Save	CTRL+S
Print	CTRL+P