|
MICROSOFT ACCESS |
Please email questions or
|
EXIT |
When you're designing forms and reports in Microsoft Access 2000, you can access a number of tools with a single click using the Toolbox toolbar. But the Toolbox lacks shortcuts to some common text-formatting tools such as bold, underline, italics, and tools for aligning text or ordering objects on the form. Put those tools within one-click reach by customizing the Toolbox toolbar.
In addition to adding buttons related to designing forms and reports, you also might want to explore the other categories of commands available and add additional shortcuts to the toolbox. For example, under the File category, you'll find the Close and Exit commands. Adding those buttons to the Toolbox toolbar makes it easy for you to remember to close your forms and save your work.
Requesting a password is one of the best ways to secure access to a control on a form. When you write a routine to prompt a user to enter a password, it's standard practice to mask the keystrokes of the password by displaying asterisks [*] instead of the letters and/or numbers. However, passwords aren't necessarily the only fields you'll ever need to mask on a form. Some users may request that you mask sensitive information such as account numbers or Social Security numbers.
Access 2000 makes it easy to mask the entry for a control. Open your form in Design view, right-click the control whose input you want to mask, and choose Properties. Set the Input Mask property to Password and close the Properties dialog box to save your changes. When a user enters text into that control, Access displays an asterisk for each key pressed.
When reviewing table records in Datasheet view, it's tempting to scroll through records to manually look for a particular date or text entry. However, with Find And Replace ([Ctrl]F), you can quickly zoom in on the records you want. Users accustomed to using Find And Replace in other Office applications may be pleasantly surprised at some of the options it offers in Access 2000.
For example, unlike in Word, Access' Find And Replace accepts wildcard characters by default. Any wildcard character you can use in a query is valid in Find And Replace. Those wildcards include the asterisk [*], which stands for multiple characters, the question mark [?], which stands for a single character, and the pound sign [#], which stands for any numeral.
If you click More in the Find And Replace dialog box, you'll see that Access gives you an additional advanced search option, Search Fields As Formatted. With this option, you can enter search strings that match the way data is formatted.
Although searching with this option might take slightly longer than searches without it, this feature can come in handy. For instance, if you've formatted your dates to appear as dd-mm-yy, activating the Search Fields As Formatted option allows you to enter search strings in the same format.
When you must store a lot of text in your table, you use a text field for entries up to 255 characters. If you need more room than that, you use a memo field. No matter which type you use, though, it's frustrating to review entries when you can't see all of the text at once.
Access 2000 provides an easy way to zoom in on too-long text whether you're reviewing information in Datasheet view, in a set of query results, or in most forms: Click the field you want to see and press [Shift][F2]. Access opens the Zoom dialog box and displays the current field's entire entry.
You can also format text within the Zoom dialog box. Just click the Zoom dialog box's Font button and customize the size, color, and appearance of the text. The formatting options you select remain in effect for any other fields you open with the Zoom dialog box, but that formatting only affects text within the dialog box. When you close the Zoom dialog box, the text is displayed as it was before you opened the Zoom window.
While you've got a field open in the Zoom dialog box, you can press [Ctrl]P to summon the Print dialog box. Click OK and Access prints the field's contents as it appears in the Zoom dialog box.
Do you support Access users who enter data via Datasheet view? If so, pass on these two tips to help those users save time and keystrokes.
First, suppose you're entering information in a new record and you want to copy into it an entry from a field in the previous record. Rather than copying and pasting, select the field in which to paste and press [Ctrl]'. Access copies the entry from the preceding record into the new one.
Second, suppose you want to copy all the entries from an existing record into a new record. Click the existing record's record selector and press [Ctrl]C. Once copied, there are two ways to paste the new copy of the record to the table.
You can go to Edit | Paste Append, which places a copy of that record at the bottom of the current table. Or, paste the copied record into the table by clicking the record selector that contains the asterisk and pressing [Ctrl]V. A pencil icon in the record selector reminds you that your changes to this new record haven't been saved.
Note: Just because you copy a record doesn't necessarily mean you get to keep it. If the copied record violates a key field restriction, you may need to edit the copied record before Access will let it stick.
When reading a report printed by Access 2000, you may not be able to determine from what database the data originated if you aren't familiar with the database. That can make it difficult to locate the source database if you need to update the entries in any of its tables.
Fortunately, you can easily stamp a report with the name of its database. Follow these steps:
By customizing your report's page header in this way, you'll be certain that anyone reading the report will know the information's source.
Access 2000 provides shortcuts that let you create new objects just by double-clicking. For example, when you click Tables in the Objects list, by default you'll see three shortcuts in the database window: one for creating a table in Design view, one for creating a table with the wizard, and one that lets you create a table by entering data.
When you select Queries, Forms, or Reports, you'll see only two shortcuts: one for working in Design view and one for launching a wizard. Click Pages, and you'll see the Design view and wizard shortcuts, plus a shortcut that launches the Locate Web Page dialog box.
By the time you finish most of your initial design work, your database window may start to get cluttered. You can eliminate some clutter and make it easier to locate the objects you use on a regular basis by getting rid of the default shortcuts. To do so, go to Tools | Options | View. In the Show section, deselect the New Object Shortcuts check box and click OK. When you do, Access stops displaying those shortcuts for all databases.
Access 2000, like other Office suite applications, supports the Undo command. However, unlike other Office applications, repeatedly going to Edit | Undo doesn't automatically undo the most recent series of changes you've made.
Sometimes you'll make multiple changes to a form or a report in Design view before you realize you've gone down the wrong path. When that happens, how do you undo those changes? You could simply close the database and answer "No" when Access asks if you want to save your changes, but there's a better way to undo unwanted changes.
As soon as you realize that you've made changes you don't want to keep, open the File menu and choose Revert. Choosing Revert restores the form or report to the state it was in before you started the current editing session. You can then begin again.
Note: The Revert option is only available under the File menu if you've made changes in Design view.
Some forms, such as order entry forms, are used both to display customer information and enter data. Because changes to customer data fields in the order entry form affect other orders from the customer, users shouldn't modify them from the order entry form.
Follow these steps to prevent users from entering data in a form field:
1. Open the form in Design view, right-click the field, and select Properties.
2. Select the Locked property under the Data tab, click the drop-down arrow, and select Yes.
3. Close the Properties box.
To lock multiple fields in a form, press [Shift] while selecting each field, right-click one of the selected fields, select Properties, and follow Steps 2 and 3. Now only the fields that weren't selected can be changed.
CHANGE THE DEFAULT POSITIONS FOR ATTACHED TEXT BOX LABELS
Every time you create a text box, Access attaches a label to the left of it by default. While you can click and drag the label to reposition it, if you need to reposition a number of controls, it's quicker to change the default.
To change the default so the label appears at the right of the text box, follow these steps:
1. Open the form in Design view, and click the Formatting toolbar's Toolbox button.
2. Double-click the form's Properties button (the box in the upper-left corner where the rulers meet) to open the form's Properties window.
3. In the Toolbox, click the Text Box button, and the Properties window will display the default Text Box properties.
4. On the Format tab, change the default from -1 to 1 for the Label X property.
5. Click in the form to create the text box with a label attached to the right.
You can also change the default position of the attached label so it's located above the text box. Follow the above procedure, but set the Label X property to 0 and the Label Y property to -.25 in Step 4. This changes the default position of the label to one-quarter inch above the text box.
STANDARDIZE FORMS WITH AUTOFORMAT
AutoFormat automatically applies an organization's standard formatting styles to a form. To use AutoFormat, add a customized format to the Form AutoFormats list.
Follow these steps:
1. Create a form that uses your organization's standard fonts, colors, borders, background bitmaps, and control properties.
2. With the form open in Design view, go to Format | AutoFormat, and click Customize.
3. Under Customize options in the Customize AutoFormat dialog box, select Create A New AutoFormat Based On The Form and click OK.
4. Enter a name for the standard style in the New Style Name dialog box, and click OK.
5. Click Close.
When you want to apply the standard formats to a form, open the form in Design view, go to Format | AutoFormat, select your organization's style from the Form AutoFormats list, and click OK.
If you need to update your organization's standard style, create a form that includes the changes, go to Format | AutoFormat, and select the style you want to update from the Form AutoFormats list. Click Customize, and select the update option under Customize Options. Click OK twice.
SEARCH FOR TWO OR MORE SINGLE CHARACTERS IN A FIELD
You can use the [ ] wildcard with the Like operator in your queries to search for two or more single characters in a field. For example, suppose you want to find all customers with the following ZIP codes: 08052, 08053, or 08055. To use the [ ] wildcard, enter the following in your query's Criteria row under the ZIP Code field:
Like "0805[235]"
This expression searches for all field entries whose last character matches one of the characters specified between the brackets. Conversely, to search for all customers that don't live within these three ZIP code areas, place an exclamation point before the list, as shown below:
Like "0805[!235]"
The exclamation point inside the brackets stands for Not in the list. The query results will include all entries whose characters do not match any character in the list within the brackets.
You can combine the [ ] wildcard with any other wildcard character. For example, you can combine the * wildcard character with [ ] to search for any ZIP codes that begin with 0805, 0807, or 0808:
Like "080[578]*"
RESTRICT DATE ENTRIES
Access users often need to restrict a date field's entries to days of the traditional workweek, excluding Saturday and Sunday dates. To help them out, teach them to use a validation rule that rejects dates that fall on The weekends.
To demonstrate this technique, open any table that contains a date field in Design view, and select the date field that you want to add the validation rule to. On the General tab of the Field Properties, enter the following for the Validation Rule property:
DatePart("w",[yourfieldname],7)>2
In the Validation Text property, be sure to enter a user-friendly message, such as "The date you entered is a Saturday or Sunday. Please enter a date corresponding to a weekday." Otherwise, your users will see the standard validation rule error message, which won't explain why the entry is invalid.
Here's how this works: The DatePart function returns a number from 1 to 7, corresponding to the days of the week. A Saturday date returns 1, and a Sunday date returns 2. Therefore, checking for a value that's greater than 2 eliminates any entry that equals 1 or 2.
SEARCH FOR FIELDS WITH NO DATA
It's not uncommon for a field to have no data. A user may have skipped the field or left it blank because there was no value for the field at the record's time of entry. For example, phone numbers may be missing for some customers in your Customer table. To find all customers whose phone number field contains no data, follow these steps:
When you run the query, the results will list all customers whose phone numbers are missing. But suppose your salespeople need a list of all customers they can call for a special promotion. To create a list of all customers with phone numbers, follow the same steps. But this time, enter Not Null in the Criteria cell. Access automatically replaces the entry with Is Not Null, and the query results will list all customers whose phone number fields are not blank.