|
Server : Apache/2.4.62 System : FreeBSD fbsdweb2.web.rcn.net 14.1-RELEASE FreeBSD 14.1-RELEASE releng/14.1-n267679-10e31f0946d8 GENERIC amd64 User : www ( 80) PHP Version : 8.3.8 Disable Function : NONE Directory : /domains/bburke/xInHouseWebPages/ |
Upload File : |
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Microsoft Access Tips</title>
<meta name="Microsoft Theme" content="copy-of-blends 011, default">
</head>
<body background="../_themes/copy-of-blends/blegtext.gif" bgcolor="#CCCCCC" text="#000000" link="#0000FF" vlink="#CC3300" alink="#0000FF"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><font size="7">Microsoft Access Tips</font></p>
<!--msthemeseparator--><p align="center"><img src="../_themes/copy-of-blends/blesepa.gif" width="600" height="10"></p>
<div align="center">
<center>
<!--mstheme--></font><table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="400" id="AutoNumber2">
<tr>
<td width="200"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><a href="Index.htm">Main Page</a><!--mstheme--></font></td>
<td width="200"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><a href="MicrosoftOfficeTips.htm">Microsoft Office Tips</a><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p align="center"><b><font size="4">Tips added 08/11/2004</font></b></p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber1" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Adding watermarks to reports (Access 97/2000/2002)">Adding
watermarks to reports (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Quickly move to specific records (Access 97/2000/2002)">Quickly
move to specific records (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Alternate page number placement on reports (Access 97/2000/2002)">
Alternate page number placement on reports (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Require that specific fields contain data (Access 97/2000/2002)">
Require that specific fields contain data (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Calculate the percentage of records in a group (Access 97/2000/2002)">
Calculate the percentage of records in a group (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Set properties for multiple controls at once (Access 97/2000/2002)">
Set properties for multiple controls at once (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Changing the back-end data source in a split application (Access 97/2000/2002)">
Changing the back-end data source in a split application (Access
97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Simplify navigating subdatasheets using shortcut keystrokes (Access 2000/2002)">
Simplify navigating sub-datasheets using shortcut keystrokes (Access
2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Clarify list items with column headings (Access 97/2000/2002)">
Clarify list items with column headings (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Suppress repetitive data to avoid distracting readers (Access 97/2000/2002)">
Suppress repetitive data to avoid distracting readers (Access
97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Create desktop shortcuts to Access objects (Access 97/2000/2002)">
Create desktop shortcuts to Access objects (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Take the guesswork out of sizing controls (Access 97/2000/2002)">
Take the guesswork out of sizing controls (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Creating record-level validation rules (Access 97/2000/2002)">
Creating record-level validation rules (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Temporarily show all table fields in an Access query (97/2000/2002)">
Temporarily show all table fields in an Access query (97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Prevent erroneous data by applying validation rules (Access 97/2000/2002)">
Prevent erroneous data by applying validation rules (Access 97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Turning control text on its side (Access 2000/2002)">Turning
control text on its side (Access 2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Printing headers, footers, and details on separate pages (Access 97/2000/2002)">
Printing headers, footers, and details on separate pages (Access
97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Use UNC paths to avoid linked table problems (Access 97/2000/2002)">
Use UNC paths to avoid linked table problems (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Programmatically manipulate a controls attached label (Access 97/2000/2002)">
Programmatically manipulate a controls attached label (Access
97/2000/2002)</a><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Using intrinsic constants in parameters (Access 97/2000/2002)">
Using intrinsic constants in parameters (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5" align="center"><b><font size="4">Tips added 08/<font color="#000000">24</font>/2004</font></b></p>
<p style="margin: 1 5" align="center"> </p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber3" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center"><span style="font-face: Courier New">
<a href="#Changing an applications title and taskbar icon (Access 97/2000/2002)">
Changing an application's title and taskbar icon (Access 97/2000/2002)</a></span><!--mstheme--></font></td>
<td width="50%"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<p align="center">
<a href="#Keeping a form on top of other database objects (Access 97/2000/2002)">
Keeping a form on top of other database objects (Access 97/2000/2002)</a><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5" align="center"><b><font size="4">Tips added 10/<font color="#000000">06</font>/2004</font></b></p>
<p style="margin: 1 5"> </p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber4" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Customize the query designer for better readability (Access 2003)">
Customize the query designer for better readability (Access 2003)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<a href="#Prevent users from resizing forms (Access 97/2000/2002/2003)">
Prevent users from resizing forms (Access 97/2000/2002/2003)</a><!--mstheme--></font></td>
</tr>
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Navigating to database objects with hyperlinks (Access 97/2000/2002/2003)">
Navigating to database objects with hyperlinks (Access 97/2000/2002/2003)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Providing field and entry information in the status bar (Access 97/2000/2002/2003)">
Providing field and entry information in the status bar (Access
97/2000/2002/2003)</a></span><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5" align="center"><b><font size="4">Tips added 11/<font color="#000000">02</font>/2004</font></b></p>
<p style="margin: 1 5"> </p>
<div align="center">
<center>
<!--mstheme--></font><table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber5" bordercolordark="#000000" bordercolorlight="#999999">
<tr>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Open Access objects from other applications with hyperlinks (97/2000/2002/2003)">
Open Access objects from other applications with hyperlinks
(97/2000/2002/2003)</a></span><!--mstheme--></font></td>
<td width="50%" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica"><span style="font-face: Courier New">
<a href="#Quickly filter data to match a particular entry (Access 97/2000/2002/2003)">
Quickly filter data to match a particular entry (Access 97/2000/2002/2003)</a><br>
</span><!--mstheme--></font></td>
</tr>
<tr>
<td width="100%" colspan="2" align="center"><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
<span style="font-face: Courier New">
<a href="#Searching for substrings to parse data (Access 97/2000/2002/2003)">
Searching for substrings to parse data (Access 97/2000/2002/2003)</a></span><!--mstheme--></font></td>
</tr>
</table><!--mstheme--><font face="Trebuchet MS, Arial, Helvetica">
</center>
</div>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Adding watermarks to reports (Access 97/2000/2002)">Adding watermarks
to reports (Access 97/2000/2002)</a></b><br>
<br>
It's easy to apply a watermark image to each page in a report. To do so, open
your report in Design view and display the report's Properties sheet. Next,
place your insertion point in the Picture property's text box. Then, click the
associated Build button that appears in the text box. Finally, locate the
graphic file you want to appear on each page, select it, and click OK.<br>
<br>
Remember that you'll want to choose an image that doesn't overwhelm the text on
your report. You can affect how the image is displayed by changing the report's
Picture Size Mode, Picture Alignment, and Picture Tiling properties.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Alternate page number placement on reports (Access 97/2000/2002)">
Alternate page number placement on reports (Access 97/2000/2002)</a></b><br>
<br>
Although you may typically print out single-sided reports, situations may call
for a report printed on both sides of the<br>
page. When your reports must support duplex printing, you may want to alternate
the position of the page number indicators to create a more professional,
book-like appearance. For instance, you may want the page number to appear on
the left side of the footer on an odd-numbered page, but have it appear on the
right side on the page when the page number is even. Fortunately, you don't have
to go to a lot of work to set up such a page numbering scheme. <br>
<br>
With your report open in Design view, choose Insert | Page Numbers from the menu
bar. Then, choose whether the number should appear in the header or footer using
the appropriate Position option. Finally, select either Inside or Outside from
the Alignment dropdown list and click OK. The Inside setting prints odd page
numbers on the left side of the page and even numbers on the right. Selecting
the Outside setting produces the opposite result, with odd page numbers
appearing on the right and even numbers on the left.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Calculate the percentage of records in a group (Access 97/2000/2002)">
Calculate the percentage of records in a group (Access 97/2000/2002)</a></b><br>
<br>
Although it's easy to count the number of records in a group, determining that
number's percentage of the report's overall record count may not seem easy to
do. Fortunately, it's easy to calculate the percentage. First, add a textbox
control to the report's Detail section and name it txtCountAll. Then, set its
Visible property to No and set its Control Source property to:<br>
<br>
=Count(*)<br>
<br>
Then, add a textbox control the group footer. Name the new control txtCountGroup
and set its Visible property to No. As before, set its Control Source property
to:<br>
<br>
=Count(*)<br>
<br>
Next, add another textbox to the group footer and set its Control Source
property to:<br>
<br>
=txtCountGroup/txtCountAll<br>
<br>
Finally, set the new textbox control's Format property to Percent.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Changing the back-end data source in a split application (Access 97/2000/2002)">
Changing the back-end data source in a split application (Access 97/2000/2002)</a></b><br>
<br>
You'll often design an application as two MDB files: a front end that contains
objects like forms, reports, and modules, and a back end containing the data
tables. Although you may use the application without ever having to change which
database the front end looks to for its data, there may be cases in which you
need to change the back end specifications. For instance, If the back end
database file's location changes, you'll have to update the front end to look to
the new location. Sometimes, you'll want to change a functioning front end to
use different back end data to meet a particular business need, such as
providing an interface to archived data that uses the same structure as your
current application's current back end database.</p>
<p style="margin: 1 5"><br>
To change the data location information in a split application, open the front
end and choose Tools | Database Utilities | Linked Table Manager from the menu
bar. Select the check boxes next to the objects you want to update, or click the
Select All button to modify all of the linked table information. Then, select
the Always Prompt For A New Location check box and click OK. Locate and select
the appropriate back end database file and then click Open. Depending on how
many data sources your front end is linked to, you may have to repeat this
process multiple times. When you're returned to the Linked Table Manager, clear
the Always Prompt For New Location check box and click Close.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Clarify list items with column headings (Access 97/2000/2002)">Clarify
list items with column headings (Access 97/2000/2002)</a></b><br>
<br>
Although listbox and combobox controls have an associated label by default,
sometimes the data in the list would benefit from an additional descriptive
heading. This is particularly true when the list displays multiple columns.
Fortunately, it's easy to add column headings based on the control's row source
data. Just set the control's Column Heads property to Yes. If the row source is
based on a table or query, the displayed fields' Caption properties appear as
column headings. If the row source is based on a value list, the initial items
in the list are used as column headings; using as many items as are needed for
the number of specified columns. Although you can set the property when the Row
Source Type is set to Field List, the heading isn't really effective as the
first field names are used as column headings, producing inappropriate results.
Note that you can't change the way column headings appear -- if you want
formatted headings you'll need to take a different approach, such as adding
label controls above a listbox control.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Create desktop shortcuts to Access objects (Access 97/2000/2002)">
Create desktop shortcuts to Access objects (Access 97/2000/2002) </a></b><br>
<br>
If you frequently work with a particular Access object, you can create a
shortcut to it on your desktop. Right-click on the object in the Database window
and choose Create Shortcut. By default, Access creates the shortcut on your
desktop, but you can specify a different folder in the Location text box. If the
database is accessed through a network, select the This Database Is On The
Network check box and enter the UNC path to the database in the Full Network
Path text box. Finally, click OK to create the shortcut.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Creating record-level validation rules (Access 97/2000/2002)">Creating
record-level validation rules (Access 97/2000/2002)</a></b><br>
<br>
We've previously discussed how you can assign validation rules to a field or
form control to ensure that its entry conforms to particular criteria. In
addition to this, you can set record-level validation rules that must be met
before a record can be saved. Unlike field-level rules, which depend solely on
the value in that specific field, a record-level validation rule is based on
data from multiple fields within the table. Access only allows one record-level
validation rule per table, but you can apply more than one condition by building
an expression using an And or an Or operator.<br>
<br>
To create a record-level validation rule, open the appropriate table in Design
view. Then, choose View | Properties from the menu bar. Enter your validation
expression in the Validation Rule text box. For example, you might use an
expression such as the following:<br>
<br>
[Hours] >=30 Or ([Hours]< 30 And [Health]=False)<br>
<br>
This expression requires that the Hours value is greater than or equal to 30 or,
if it's less than 30, that the Health field<br>
equals False. If both of the conditions being tested evaluate to a False result,
that record can't be saved. To display a custom message to the user when the
entry violates the validation rule, enter an appropriate prompt in the
Validation Text property's text box.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Prevent erroneous data by applying validation rules (Access 97/2000/2002)">
Prevent erroneous data by applying validation rules (Access 97/2000/2002)</a></b><br>
<br>
The more you control the quality of the data being entered into tables, the
better results you'll get when you need to retrieve, analyze, or manipulate the
data. To ensure that data conforms to your business needs, you can use data
validation rules that prevent unacceptable entries. For example, say you have a
table storing purchase information and you want to require that the quantity
field always has a number greater than zero, or that a date field only contains
values after a specific date. You can easily create validation rules that
require correct entries and provide messages to users when information violates
the rules.<br>
<br>
To incorporate validation rules, set the Validation Rule and Validation Text
properties at either the control or table field level. For instance, to require
that a value is greater than zero, you can use the following property settings:<br>
<br>
Validation Rule: >0<br>
Validation Text: Quantity must be greater than 0.<br>
<br>
Likewise, date field validation might look like:<br>
<br>
Validation Rule: >=#1/1/2001#<br>
Validation Text: Only dates from 2001 and later are accepted.<br>
<br>
When a user enters an inappropriate value, the message stored in the Validation
Text property is displayed.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Printing headers, footers, and details on separate pages (Access 97/2000/2002)">
Printing headers, footers, and details on separate pages (Access 97/2000/2002)</a></b><br>
<br>
There may be times when you'd like the detail information on a report separated
from header or footer sections. For instance, a group footer may contain summary
results that you want to print as a separate sheet. Fortunately, it's easy to
control whether headers, footers, and detail sections begin on separate pages.
Simply set the appropriate section's Force New Page property. The default
setting (None) prints the current section on the current page. However, the
other settings allow you to force page breaks before and after the current
section. Although you probably rarely print forms, it's worth noting that form
sections also support a Force New Page property.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Programmatically manipulate a controls attached label (Access 97/2000/2002)">
Programmatically manipulate a control's attached label (Access 97/2000/2002)</a></b><br>
<br>
You probably most often use the Controls collection in procedures to loop
through the control objects on a form or<br>
report. What you may not realize is that other objects can also have a Controls
collection. For example, form and report sections, tab controls, and option
groups all support a Controls collection. <br>
<br>
These objects are obvious candidates, but what may not be so obvious is that the
collection can also apply to objects like textboxes, comboboxes, or any other
object that has an attached label. For a simple demonstration, add a button
named cmdListCaptions to an existing form and add the following procedure to the
form's module:<br>
<br>
Private Sub cmdListCaptions_Click()<br>
Dim ctl As Control<br>
For Each ctl In Me.Controls<br>
If ctl.ControlType = acTextBox Then<br>
Debug.Print ctl.Controls(0).Caption<br>
End If<br>
Next ctl<br>
End Sub<br>
<br>
Although this example doesn't serve a practical purpose, it shows how easy it is
to access related labels. When you click the button in Form view, the procedure
lists all of the caption text related to a form's textbox controls in the
Immediate/Debug window of the VBE. </p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Quickly move to specific records (Access 97/2000/2002)">Quickly move to
specific records (Access 97/2000/2002)</a></b><br>
<br>
Although the record numbers displayed in the text box associated with a
datasheet or form's record navigation buttons aren't permanently linked to
specific records, they're often helpful for finding records during the current
work session. All you have to do is enter the number in the Record text box at
the bottom of the form and press [Enter]. Access maintains focus on whatever
control was active before using the Record text box, providing an easy way to
examine data from records that may be far apart in the recordset. As convenient
as this is, you might find that using your mouse to move your insertion point to
the Record text box disrupts your workflow. Fortunately, you don't have to use
your mouse at all--just press [F5] to activate the Record text box.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Require that specific fields contain data (Access 97/2000/2002)">
Require that specific fields contain data (Access 97/2000/2002)</a></b><br>
<br>
Although it's usually okay for some fields in your tables to be blank, almost
every application has fields that absolutely must be filled in. If missing data
is causing headaches, you can take steps to prevent incomplete records from
being created. Setting a field's Required property at the table level prevents
users from being able to save a record if a required field is Null. <br>
<br>
Simply open the table in Design view, select the appropriate field, and set its
Required property to Yes. If your table<br>
already contains data, Access prompts you to check whether existing records
violate the new setting when you save the table. If you click No, the existing
Null values are accepted as is. However, new records must adhere to the required
entry rule.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Set properties for multiple controls at once (Access 97/2000/2002)">Set
properties for multiple controls at once (Access 97/2000/2002)</a></b><br>
<br>
If you have to assign the same property setting to multiple controls on a form
or report, you don't have to waste time<br>
configuring each control individually. With the Properties sheet displayed,
select all of the relevant controls. If the controls aren't adjacent, hold down
the [Shift] key as you click on each control. Then, simply assign the
appropriate property value in the Multiple Selection property sheet.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Simplify navigating subdatasheets using shortcut keystrokes (Access 2000/2002)">
Simplify navigating subdatasheets using shortcut keystrokes (Access 2000/2002)</a></b><br>
<br>
For the most part, working in a subdatasheet is just like working with a
datasheet. The shortcut keys you use with<br>
datasheets apply to subdatasheets as well, so you're probably comfortable using
the keyboard to navigate through data. However, there are several additional
shortcuts that specifically help with subdatasheet navigation and some familiar
navigation keys introduce new behavior when a subdatasheet is involved. Here's a
list of some of the subdatasheet shortcuts you're likely to find most useful:<br>
<br>
[Ctrl][Shift][down arrow] <br>
Expand the selected record's subdatasheet<br>
<br>
[Ctrl][Shift][up arrow] <br>
Collapse the active subdatasheet<br>
<br>
[down arrow] or [up arrow] <br>
Skip over an open subdatasheet to the next record in the same<br>
datasheet/subdatasheet as the current record<br>
<br>
[Ctrl][Tab] <br>
Exit the subdatasheet and select the first field in the next record<br>
<br>
[Ctrl][Shift][Tab] <br>
Exit the subdatasheet and select the last field in the previous record</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Suppress repetitive data to avoid distracting readers (Access 97/2000/2002)">
Suppress repetitive data to avoid distracting readers (Access 97/2000/2002)</a></b><br>
<br>
Sometimes, you may have data that needlessly clutters a report. For instance,
suppose your report is listing the fields <br>
Company, FirstName, and LastName. If there are multiple people listed for each
company, and the report is sorted by <br>
company name, repeating the company information is unnecessary. Your first
instinct may be to create a group header based on the company name. However,
sometimes the best layout for a business need dictates that the redundant data
should appear in the report's Detail section -- you just don't want to see it
repeated every time. Fortunately, you can create such a report easily.<br>
<br>
First, open the report in Design view and select the control that displays
repetitive information. Then, display the <br>
control's Property sheet and set the Hide Duplicates property equal to Yes.
Finally, save and preview your report. If the data in the modified control is
the same as the data from the previous record, the control is hidden.<br>
</p>
<p style="margin: 1 5"><b>
<a name="Take the guesswork out of sizing controls (Access 97/2000/2002)">Take
the guesswork out of sizing controls (Access 97/2000/2002) </a></b><br>
<br>
You'll find this tip helpful if you've ever had to repeatedly open a form or
report in Design view to tweak the size of a<br>
control because it wasn't big enough to display all of your data. When you click
on a control's sizing handles in Design view, Access displays the range of
characters that the control will display in the program window's status bar.
Access adjusts this number based on the control's font attributes, so it's easy
to tell what impact such property changes will have on the amount of data that
can be shown in the control.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Temporarily show all table fields in an Access query (97/2000/2002)">
Temporarily show all table fields in an Access query (97/2000/2002)</a></b><br>
<br>
When you're designing a query, you'll often find that you need to temporarily
view fields from the underlying tables beyond the ones you want shown in the
final result. To do so, you probably drag the relevant fields to the design grid
and then delete them when you're done. However, there's an easier way to view
the data that you typically want excluded in the query. To do so, open the query
in Design view. Click in the top pane of the window and then click the
Properties button to open the query's properties sheet. (You can also
right-click in the top pane and choose Properties from the shortcut menu.) Set
the Output All Fields property to Yes, close the properties sheet, and run the<br>
query. Data from all of the underlying tables' fields will be displayed in
addition to the fields you specified in the query design grid. To change the
query so that it only displays the fields explicitly selected in the query
design grid, simply reset the Output All Fields property to No.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Turning control text on its side (Access 2000/2002)">Turning control
text on its side (Access 2000/2002)</a></b><br>
<br>
Although you can't incorporate different page orientations into a single report,
you can simulate the effect by rotating text in label and textbox controls. To
do so, simply set a control's Vertical property to Yes. Note that changing the
property doesn't physically rotate the control, it just changes how the text is
displayed -- you'll usually need to resize the control to properly show the
rotated text.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Use UNC paths to avoid linked table problems (Access 97/2000/2002)">Use
UNC paths to avoid linked table problems (Access 97/2000/2002)</a></b><br>
<br>
Access developers commonly use a split application design model. In a split
application, data tables are stored in a separate file from the forms, reports,
and other objects that comprise the complete application. When creating a split
application, or any database that links to external files, be careful to avoid a
common source of problems--mapped drive letters.<br>
<br>
The ability to map network folders to a drive letter provides convenience to
users, but cause headaches for developers. For example, your PC may be
configured to map the E: drive to a particular folder on your network. However,
a co-worker's PC might have the E: drive assigned to a DVD-ROM drive. If you
design a split application that links to a file stored in what appears to be
your E: drive, the application won't work correctly when your coworker uses it.<br>
<br>
The solution is to use UNC paths instead of drive letters when setting the path
to a particular file. Don't use the traditional path format, which is as
follows:<br>
<br>
<drive letter>:\<folder name>\<filename><br>
<br>
Instead, use a path in the following format:<br>
<br>
\\<server name>\<folder name>\<filename><br>
<br>
Since the computer where the file resides defines the server name used in a UNC
path, the value is the same regardless of what computer you're using to connect
to the server.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Using intrinsic constants in parameters (Access 97/2000/2002)">Using
intrinsic constants in parameters (Access 97/2000/2002)</a></b><br>
<br>
When you create VBA procedures that accept parameters, you may wish that you
could take advantage of the constants that are automatically available when
working with common properties and methods. For example, when you enter
DoCmd.Close in a procedure and press [Spacebar], the IntelliSense feature
automatically displays a dropdown list of the relevant object type constants.
Now, let's say that you create a procedure like the following:<br>
<br>
Sub DoStuff(strObjectName As String, intObjectType As Integer)<br>
'... <br>
'Do stuff<br>
'...<br>
End Sub<br>
<br>
You need to pass an object name and type to the procedure. Although the object
type is an integer value, you can use an intrinsic constant to pass the
appropriate values, such as with the statement DoStuff "EmployeeForm", acForm.
However, you must already know the appropriate constant, because the VBE won't
provide the list of valid possibilities as the procedure is currently written.
Fortunately, there's a way to take advantage of existing constant collections.
To do so, declare the parameter using the appropriate class name. For example,
change the procedure to the following:<br>
<br>
Sub DoStuff(strObjectName As String, intObjectType As AcObjectType)<br>
'... <br>
'Do stuff<br>
'...<br>
End Sub<br>
<br>
When you subsequently call the procedure, the dropdown list of constants appears
as soon as you add a comma after the name parameter.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><span style="font-face: Courier New"><b>
<a name="Changing an applications title and taskbar icon (Access 97/2000/2002)">
Changing an application's title and taskbar icon (Access 97/2000/2002)</a></b><br>
<br>
By default, the text appearing in an Access application's title bar is simply
Microsoft Access. This is the same text that appears in the application's
Windows taskbar button. While the role of such title text may seem trivial,
things can get confusing if you have multiple Access files open--changing the
title text to something more meaningful simplifies keeping track of the open
windows if you find yourself switching between the applications.<br>
<br>
Fortunately, it's easy to change the title text. In addition,you can replace the
default key icon that appears in the title bar and taskbar button with an image
that helps differentiate applications. To do so, open your database and choose
Tools | Startup from the menu bar. Then, enter the title you want your
application to use in the Application Title text box. To replace the icon
associated with the application, click in the Application Icon text box and then
click the associated Build button. You can select icon files with .ico or .cur
extensions or change the Files Of Type setting to select a bitmap file. Simply
locate the file you want, select it, and click OK. Finally, click OK to close
the Startup dialog box. Your changes are immediately applied to the file.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
</span></p>
<p style="margin: 1 5"><b>
<a name="Keeping a form on top of other database objects (Access 97/2000/2002)">
Keeping a form on top of other database objects (Access 97/2000/2002)</a></b><br>
<br>
Sometimes you may wish for a particular form to continually remain visible over
other database objects, even when you're working with the other objects. For
example, you may want a form containing detail about a company location to
always be visible as you enter related data in another form. Configuring a form
to behave this way is easy. Simply open it in Design view and set its Pop Up
property to Yes.</p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><b>
<a name="Prevent users from resizing forms (Access 97/2000/2002/2003)">Prevent
users from resizing forms (Access 97/2000/2002/2003)</a></b><br>
<br>
By default, users' Access forms are resizable. However, there are often times
when you want to restrict users from changing a form's dimensions. Fortunately,
doing so is easy. Just change the form's Border Style property from its default
of Sizable to any of the other choices. The selection you make affects other
aspects as well, as described below:<br>
<br>
None: The form doesn't have a title bar, so there are no Minimize, Maximize, and
Close buttons. You'll probably want to create a custom method for closing the
form. Also, the form can't be moved.<br>
<br>
Thin: All of the usual form elements are available, but the form can't be
resized.<br>
<br>
Dialog: The Minimize and Maximize buttons are unavailable. On older Windows
operating systems, the border appears slightlythicker than usual, but there's no
real discernible difference if you use the Windows XP style interface.<br>
</p>
<p style="margin: 1 5"><span style="font-face: Courier New"><b>
<a name="Customize the query designer for better readability (Access 2003)">
Customize the query designer for better readability (Access 2003)</a></b><br>
<br>
In the interest of allowing you to see more text when constructing queries, the
query designer uses an 8-point font. If you'd rather sacrifice some characters
for easier readability, you can finally do so in Access 2003. To do so, choose
Tools |Options from the menu bar and click on the Tables/Queries tab. Simply set
the font properties you want in the Query Design Font section and click OK.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
</span></p>
<p style="margin: 1 5"><span style="font-face: Courier New"><b>
<a name="Navigating to database objects with hyperlinks (Access 97/2000/2002/2003)">
Navigating to database objects with hyperlinks (Access 97/2000/2002/2003)</a></b><br>
<br>
You'll often want to provide users with a way to launch database objects from a
form. For example, you might include a switchboard form that allows them to open
different entry forms. Or, you might want to let users display a small report or
query datasheet as a reference while they enter data on a main form. You
probably<br>
use VBA code and command buttons to accomplish such goals, but you can often
achieve the same results with no coding at all. Instead, you can use hyperlinks
to open Database objects.<br>
<br>
To set up object hyperlinks, choose Insert | Hyperlink while working in a form's
Design view. Then, click the Object In This Database button. Expand the
appropriate object node in the Select An Object In This Database tree, select
the object to which you want to link, and click OK.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
<br>
</span><span style="font-face: Courier New"><b>
<a name="Providing field and entry information in the status bar (Access 97/2000/2002/2003)">
Providing field and entry information in the status bar (Access
97/2000/2002/2003)</a></b><br>
<br>
If you cut corners when setting up tables, chances are you do so by ignoring the
Description property when defining fields. Remember that the time you save now
may be lost in the long run if another developer--or you--have to subsequently
modify the table structure. The descriptive information can eliminate<br>
detective work needed to understand what information Access is storing or
provide insight into seemingly odd design decisions.<br>
<br>
Field descriptions don't just help developers--end users stand to benefit as
well. When you specify a description, that text is automatically displayed in
the application window's status bar when the field has focus in a datasheet.
Likewise, if you use the field list to add a bound control to a form, the
description is automatically displayed in the status bar when the control has
focus. Of course, you may want to override a form control's default status bar
text, or assign text to an unbound control. It's easy to do so--just change the
control's Status Bar Text property setting.<br>
</span></p>
<p style="margin: 1 5"><span style="font-face: Courier New"><b>
<a name="Open Access objects from other applications with hyperlinks (97/2000/2002/2003)">
Open Access objects from other applications with hyperlinks (97/2000/2002/2003)</a></b><br>
<br>
We recently told you how to open objects in a database using hyperlinks on an
Access form. You can also use hyperlinks in<br>
other Office documents to open Access objects. For instance, you can click a
hyperlink in Microsoft Word and launch a particular form or click a hyperlink in
a worksheet cell to launch a an Access query from Microsoft Excel.<br>
<br>
To create the hyperlink, open the appropriate Office document, place your
insertion point where you want to create the link, and choose Insert | Hyperlink
from the menu bar. The specific options and settings vary by version, but you
should see a choice to let you link to existing files. In the place where you
specify the file that the hyperlink jumps to, specify the path, filename, and
object using the following format:<br>
<br>
path\filename#objecttype objectname<br>
<br>
For instance, if you want to link to a specific form named frmEntry, you might
use something like:<br>
<br>
C:\Documents and Settings\bdesso\My Documents\ClientDatabase.mdb#Form frmEntry<br>
<br>
Likewise, to link to a Table, you'd use #Table YourTable (where YourTable is the
name of the Table) at the end of the hyperlink's path. The same holds true for
queries (#Query YourQuery) and reports (#Report YourReport).</span></p>
<p style="margin: 1 5"> </p>
<p style="margin: 1 5"><span style="font-face: Courier New"><b>
<a name="Quickly filter data to match a particular entry (Access 97/2000/2002/2003)">
Quickly filter data to match a particular entry (Access 97/2000/2002/2003)</a></b><br>
<br>
When examining data, you'll often want to view all of the records that have a
particular field value. For example, you<br>
might see a questionable billing transaction as you scan a datasheet and want to
examine all of the records pertaining to<br>
the customer in question. Doing so is easy. First, place your insertion point in
the field containing the data that should be<br>
found in all of the records you want to see--such as a CustomerID field in our
proposed scenario. Then, select Records | Filter |<br>
Filter By Selection from the menu bar or click the Filter By Selection button on
the toolbar. To redisplay all records, choose<br>
Records | Remove Filter/Sort from the menu bar or click the Remove Filter button
on the toolbar. Note that you can filter by<br>
selection with both datasheets and forms.</span><span style="FONT-SIZE: 10pt; font-face: Courier New"><br>
</span></p>
<p style="margin: 1 5"><span style="font-face: Courier New"><b>
<a name="Searching for substrings to parse data (Access 97/2000/2002/2003)">
Searching for substrings to parse data (Access 97/2000/2002/2003)</a></b><br>
<br>
You'll occasionally come across data, such as from an external source, that
you'll need to manipulate in order to conform to<br>
your database design. For example, you may receive a table of contact
information in which the contact name is stored in a<br>
single field. For instance, it might be stored in a single field named
ContactName using a "Last Name, First Name" format like:<br>
<br>
Desso, Brian<br>
<br>
To conform to your table, and normalization rules, you need Brian in one field
and Desso in another. <br>
<br>
<br>
The trick is to find a string that you can use to consistently separate the
first and last name data. In our case, we can use<br>
the comma character. To search for the comma within the larger string, you use
the InStr() function. This function returns the<br>
character position of a string within another string. In its simplest form, the
function uses the following syntax:<br>
<br>
InStr(string1, string2)<br>
<br>
where string1 is the value being searched and string 2 is the value you want to
find.<br>
<br>
In a query, you can use the function to get the first and last names from our
example using field expressions like:</span></p>
<p style="margin: 1 5"><span style="font-face: Courier New"><br>
FirstName: Mid([ContactName],InStr _([ContactName],",")+2)<br>
LastName: Left([ContactName],InStr _([ContactName],",")-1)<br>
<br>
Once you figure out the right expressions for your data, you can change the
query into an action query to save the data to the<br>
appropriate table fields. Keep in mind that a similar approach may not be able
to parse all of the records accurately, due to<br>
data entry inconsistencies, but it will often simplify the bulk of your cleanup
work.<br>
</span></p>
<!--mstheme--></font></body>
</html>