|Adding a group filter control to a data access page (Access 2002)
|Data access pages allow you to view and edit data stored
in Access tables via an HTML page. The page can be viewed
in Internet Explorer, eliminating the need to use Access
as the front end to the data. Although they were fairly
limited when originally introduced in Access 2000, data
access pages are worth investigating in Access 2002, as
they have been significantly improved.
As you work with pages, you may find that you want to
limit the information shown on a page. For instance, if
your page lists products, you may want to only show one
particular category of products at a time.
Data access pages let you limit the items displayed on a
page using a group filter control. It's easy to add a
group filter control to a page, but the easiest technique
to do so is far from obvious.
To add the control, open your page in Design view. Then,
using your right mouse button, drag the field containing
the data you want to use as the basis for your filter
from the field list to the appropriate section of the
page. When you release the mouse button, select Group
Filter Control from the shortcut menu.
By default, Access adds a combobox control to the page.
When you view data with the page, the control contains a
list of the items stored in that field. Choose the item
you're interested in, and the page displays only those
records that meet the filter value.
|Copy data from one Access table to another
|Before Office XP, Access users who wanted to copy columns of data from one table to another in a database had to copy and paste each column separately. With XP's Office Clipboard Task Pane, Access users can copy as many as 24 columns of data to the clipboard, and then later paste some or all of the columns into another table. Follow these instructions to copy data from one Access table to another.
First, follow these steps to collect the columns of data you wish to copy:
1. Click Edit | Office Clipboard to display the Clipboard Task Pane.
2. Open the table from which you want to copy the data, and select the first column of data to copy.
3. Click Copy, and a partial listing of data for that column will appear as an item in the
4. Clipboard Task Pane.
5. Repeat Steps 2 and 3 for each column of data you wish to add to the clipboard.
Close the source table.
Next, follow these steps to paste the columns to a new table:
1. Open the table to which you want to copy the data, and select the blank column in which you want to paste an item from the clipboard. To avoid errors, ensure that the data type of the destination column is the same as that of the column to be pasted.
2. In the Clipboard Task Pane, click the item that lists the column you want to paste and confirm the paste.
3. Rename the field if necessary.
4. Repeat Steps 1 through 3 for each item you wish to paste from the clipboard.
5. Save your changes before closing the new table.
Before closing the Clipboard Task Pane, click the Clear All button to clear the clipboard.
|Restore the last saved version of a form or report
|Starting with Access 2002, you can finally undo multiple
actions when working with a form or report in Design view.
Access 2002 lets you undo the last 20 changes, whereas
prior versions could only undo the most recent change.
Even if you're using an older version of Access, there is
a way to undo all changes that have been made to a form or
report since the object was last saved. To do so, choose
File | Revert from the menu bar. Note that this also
restores any associated VBA code to its original state.
While the Revert command may be convenient when you want to
undo unwanted changes in one fell swoop, use it judiciously.
Once you execute the Revert command, all of your changes are
lost -- you can't selectively recover changes. Essentially,
the result is the same as closing the object without saving
and then reopening it.
|Keep the Toolbox control button active in Access 2002
|When you add controls to forms and reports you'll often
need to add several of the same type, such as when creating
a group of option buttons or a series of unbound text boxes.
In such cases, repeatedly moving between the Toolbox and the
object you're designing can quickly become tedious.
Fortunately, you can make the process easier. When you
select the control you want to add from the Toolbox,
double-click the control button. Doing so lets you add as
many controls of that type as you need. When you finish,
click the button again to disable the control tool.
|Create a custom format in Access 2002
|You may be familiar with built-in AutoFormats through the form
and report creation wizards. AutoFormats are saved collections of
attribute settings for such elements as fonts, colors and object
borders, and picture property settings. If you find yourself
repetitively making the same formatting changes to form and
report controls, you can save time by saving your formatting
preferences as a custom AutoFormat.
To create your AutoFormat, open a form that uses your desired
default attributes in Design view. Then, select Format |
AutoFormat from the menu bar. When the AutoFormat dialog box
appears, click the Customize button. The subsequently displayed
Customize AutoFormat dialog box is where you manage your system's
AutoFormats. You can create a new format, incorporate the changes
you've made into whatever AutoFormat was selected in the
AutoFormat dialog box when you clicked the Customize button, or
delete the selected AutoFormat.
Select the Create A New AutoFormat Based On The Form (form name)
option button and click OK. You'll then be given the opportunity
to specify a name for the new AutoFormat. To later apply an
AutoFormat to a form or report you're designing, simply redisplay
the AutoFormat dialog box, choose the correct AutoFormat and
|Adjust your startup options in Access 2002
|If you want to control what happens when you open a database,
you'll want to examine the Startup Property dialog box. Some of
the more popular options are the Application Title, Application
Icon, Display Form/Page, and Display Database Window settings.
The Application Title feature allows you to change the text on
the Access Window's title bar from Microsoft Access to a name
that best suits your database. This name also appears as the
button caption on the Window's Taskbar. The Application Icon
property can either be a bitmap (.bmp) or icon file (.ico) and
will be displayed next to the Application Title on the title bar
and taskbar button. Perhaps even more useful is the Display
Form/Page setting. Clicking the dropdown arrow on this field
displays a list of available forms and data access pages in your
database. When it starts the database, Access automatically
displays the form or page you selected. Finally, the Display
Database Window check box determines if the Database window is
visible when the database opens. Even when this option is turned
off, however, the Database window can still be accessed by normal
means, such as by pressing [F11] or by choosing Window | Unhide
and selecting the database window.
To access the Startup Property dialog box, from the Database
window, choose Tools/Startup... from the menu bar.
|See the detail behind summary values in Access 2002's PivotTable view
|One of Access 2002's most exciting new features is the
PivotTable view. Similar to crosstab queries, this view lets you
look at summaries of information grouped by row and column
categories. If you're unfamiliar with crosstab queries, the
results look similar to something you might create in Excel. For
instance, you can show sums of revenue by sales rep (listed as
row headings) by month (displayed as column headings). The cell
at the intersection point of a row and column contains a summary value.
Unlike crosstab queries (or even Excel's similar PivotTable
feature), PivotTable view can also display the detail information
behind the summary calculations. Each row and column heading in
the PivotTable includes plus and minus controls that are used to
toggle the display of the detail information. However, you can
also drill down to the detail for a particular summary without
displaying a lot of extraneous data. To do so, simply
double-click on the summary cell you're interested in. The column
expands to show the appropriate detail data. To rehide the data,
simply double-click on the summary value again.
|Pass values to a report object with OpenArgs in Access 2002
|Access 2002 is the first version to support the OpenArgs
property for the Report object. Previously, this property was
available only with forms. As with the form property, the report
OpenArgs property is accessible only through Visual Basic for
Applications (VBA) code or a macro. The property's purpose is to
pass a value to the report as the report is opened by the DoCmd
object's OpenReport method in the form
DoCmd.OpenReport(reportname, view, filtername, wherecondition,
where reportname is the name of a Report object and the optional
OpenArgs argument accepts a Variant. Once the report is opened,
the value passed to the argument becomes the report's OpenArgs
property value. You can, in turn, use this value for such things
as conditionally executing code statements or customizing
elements of the report.
|Keep selected database objects invisible to your users in Access (2002)
|Although you may typically want to let users access any object
in a database, there may be certain times when you'd prefer that
users are unable to see certain tables, queries, or other
objects. If so, you can flag an object as being hidden. By
default, such objects are invisible. To flag an object,
right-click on it in the Database window and choose Properties
from the shortcut menu. When the Properties dialog box appears,
select the Hidden check box and click OK. Note that users can
still get to the object if they really want to--and you can
temporarily redisplay the object if you later want to clear the
Hidden property setting. To display objects that are normally
hidden, choose Tools | Options from the menu bar. Then, click on
the View tab, select the Hidden Objects check box in the Show
panel, and click OK.
|Quickly copy table structures through the user interface in Access 97-2000-2002
|A short while ago we discussed using action queries to create a
blank table that uses an existing table's structure. As
mentioned in the tip, the query technique was intended to help
those that need to provide an automated way of creating a table
to reduce repetition or aid those unfamiliar with Access.
However, many of you wrote in to point out the ease with which
table structures can be manually copied. We've covered this
before, but it seems as though it's a good time to revisit the topic.
Copying a table structure is easy--it's just about as easy as
copying and pasting text in word. First, select the table that
has the structure you want to copy in the Database window. Then,
choose Edit | Copy from the menu bar or use the familiar [Ctrl]C
keyboard shortcut. Next, select Edit | Paste or press [Ctrl]V.
Access now displays the Paste Table As dialog box. To create a
new table with the same structure, enter a name in the Table Name
text box, select the Structure Only option button and click OK.
Note that if your database contains two tables with the same
structure you can also use the Paste Table As dialog box to
append data from one table to the other.
|Fine-tune form and report control placement in Access 97-2000-2002
|When you add a control to a form or report, Access tries to aid
you in sizing and aligning it using the Snap To Grid feature.
The grid is typically visible as you work with a form or report
in Design view, but if it isn't you can display it by choosing
View | Grid from the menu bar. Although the Snap To Grid feature
is usually helpful, when you fine-tune the placement of some
controls you may find that you need to move some of them to
positions that aren't exactly aligned with the design grid. You
can toggle whether the Snap To Grid feature is active by
selecting Format | Snap To Grid while in Design view. However,
you don't have to take this step--you can temporarily disable the
Snap To Grid feature by holding down the [Ctrl] key. Then, you
can use your mouse or the cursor arrows to place the controls
exactly where you want them.