MS ACCESS 97, 2000 & XP
LAST UPDATED:
08 November 2007 18:22:53 -0600
Translate this page
using
FreeTranslation.com
Changes to this
page are IN PROGRESS
If you're considering a new diet or exercise regime, good for you.
If you're not, you might want to check your Body Mass Index and then
reevaluate. To check your Body Mass Index in Access, you can use the
following procedure:
Function BMI(pounds As Integer, feet As Integer, inches As Integer)
As Integer
BMI = (pounds * 0.45) / (((feet * 12) + inches) * 0.0254) ^ 2
End Function
You can attach the procedure to a text box control or run it in the
Immediate window by entering the function
?BMI(pounds, feet, inches)
where pounds, feet, and inches represent your information as
integers. For example, if you're 5'5" and you weigh 130 pounds, you'd
use the statement
?BMI(130,5,5)
This function returns a BMI value of 21. If the result is 25 or
under, your BMI is acceptable. If it's over 25, you need to reduce
your body fat through diet and/or exercise. The first part of our
expression--pounds * 0.45--converts your weight into kilograms. The
second component-(feet * 12) + inches--converts your height into
meters. Your height in meters is then squared. In a nutshell, your
weight is divided by your height squared to return your BMI. If you're
wondering why our expression doesn't return decimal values, stay tuned
for our next tip.
In our last tip, we showed you how to return your Body Mass Index.
Although the expression we used should, in most cases, return decimal
values, our procedure doesn't. If you're rounding decimal values to an
integer, you don't really need special expressions for rounding.
Simply assign the value of your procedure as an Integer data type. If
you recall, the procedure used in our last tip was Function BMI(pounds
As Integer, feet As Integer, inches As Integer) As Integer By
assigning the entire procedure to an Integer value, we limit our
function to returning only Integer values.
Therefore, any rounding is automatic. If the result of the
expression is 21.45, the function will return 21; if the result is
22.55, the function will return 23. The function doesn't care how many
decimal values the expression returns. While this isn't a typical
programming solution, it is a quick solution in the right situation.
By now, you probably know that Access 2000 includes a new feature
called Name AutoCorrect. This feature automatically updates all
references to an object when you rename it. If you rename a field,
table, query, form, or report, you don't need to spend lots of time
hunting for all the references in your other objects. (We discussed
this feature in an earlier tip.)
Unfortunately, Name AutoCorrect doesn't automatically update
references in your VBA code and events (modules). You must remember to
do this yourself. Fortunately, this task isn't a laborious undertaking
if you use the Replace feature. First, open your module and click the
Find button on the Visual Basic toolbar. Next, click the Replace
button and then enter the object's original name in the Find What
control. Then, enter the object's new name in the Replace With
control. Once you've entered both references, click Find Next to run
the feature. Access will highlight each occurrence of the original
name and ask you if you want to replace it. Updating object names this
way is quick and thorough. Just be sure to open and search each module
in your application (and any linked databases).
Microsoft already offers free add-ins for Access 2000; you can
download them from its site at
http://officeupdate.microsoft.com/downloadCatalog/dldAccess.htm
Once you access this page, you can specify downloads for just Access
2000, or you can view all the Access add-ins. Either way, you're sure
to find something useful.
In our last tip, we advised you always to create a backup before
running an action query. However, if you'd like to steal a quick peek
at the results before running the query, you can. Once you've created
your action query in the query design grid, you generally execute the
query by clicking the Run button. As we stated in our last tip, you
can't undo these changes. So, instead of clicking Run, click the View
button. Specifically, choose Datasheet View from the View button's
options. Doing so will display a preview of your action query's
results without permanently committing those changes. Simply choose
Design View to return to the query design grid.
We seldom use the label component of a text box, but Access gives
us one anyway by default. Fortunately, you can inhibit this behavior.
But you must do so before you add the text box to your form. Here's
how it works. First, click the Textbox control on the Toolbox. Then,
before you click the form to add the text box, click the Properties
button (if the Property sheet isn't open) and locate the Auto Label
property. The default is Yes; change the setting to No. Now, click
inside the form to position your text box, minus the label component.
Access will retain this setting while you're working in that form. If
you change your mind, simply repeat the process, setting the property
back to Yes.
Earlier versions of Access don't allow you to undo an action query,
and Access 2000 is no different. Before running any action
query--update, append, or delete--back up your tables. Then you can
revert to the backup if you decide to discard the results of your
query.
To copy a table, select it in the Database window and then choose
Edit, Copy. Then, select Edit, Paste and give the table a new name.
If you frequently use the same file (or files) but you don't want
to make that file's folder your default folder (perhaps they're on a
network), create a shortcut to the file(s) and store the shortcut in
your default folder or the Favorites folder. Here's how: First,
right-click the file in its original folder and choose Create Shortcut
from the shortcut menu. Next, select the newly created shortcut and
then choose Add To Favorites from the Tools menu in the current dialog
box (Open or Save). Doing so moves the shortcut to the specified
folder.
Once you've created the shortcut and saved it your Favorites
folder, you can open the file by simply clicking the Favorites folder
on the Places toolbar. Then, locate the shortcut in the list of
folders and files and click it!
If you prefer to store the shortcut in your default folder, simply
cut and paste it from one folder to another.
Each Office application has a set of VBA Help files that are
separate from that application's Help files. The VBA Help files are
available only from the VBA IDE window. Simply pull down the Help menu
as you would in the Access window.
However, if you installed Microsoft Office using the standard
installation settings, Office did not install the VBA help files. If
you want access to these files, you must specify them during the
install process.
First, select Start, Settings, Control Panel, then double-click the
Add/Remove Programs icon. Click the Install/Uninstall tab and
double-click your version of Microsoft Office 2000. In the first panel
of the install program, click the Add or Remove Feature button. Next,
click the plus sign next to the Office Tools item. You'll find Visual
Basic Help at the bottom of the list of tools. Click the small
triangle, select Run From My Computer or Install On First Use, click
Update Now, and exit any remaining windows.
If you selected Install On First Use, Office won't install the
files until you attempt to use them.
To maximize the available workspace, Access may place the Standard
and Formatting toolbars on one line. This means you have to expand a
toolbar--by clicking the More Buttons button at its right edge--to see
the rest of the toolbar's buttons.
If you'd prefer to sacrifice some screen space in exchange for
having all the buttons handy, you can do so in a couple of seconds.
First, right-click any toolbar and choose Customize. Click the Options
tab and deselect the Standard And Formatting Toolbars Share One Row
option. Then, click Close.
Now the two toolbars will reside on individual rows. Should you
wish to place the toolbars on a single row, just follow the same
steps, except this time you will check the option.
Incidentally, there's an easier way to set this option: You can
turn off the Standard And Formatting Toolbars Share One Row option by
dragging one of the toolbars to its own line. You can turn the option
back on by dragging one of the toolbars to the same line as the other.
Some people are reporting a small problem when installing Office
2000 on the same system with Office 97. The first time you try to run
Access 97 after installing 2000, you may receive the message
Microsoft Access can't be started as there is no license for it on
this machine.
To resolve this problem, you need to reset the Access 97 license in
the Windows Registry. To do so, select Start, choose Run, then type
regedit
to launch the Registry Editor. In the editor, follow the hierarchy
to the following key:
\\HKEY_CLASSES_ROOT\Licenses\8CC49940-3146-11CF-97A1-00AA00424A9F
If this key doesn't exist, try uninstalling and then reinstalling
Access 97. After uninstalling Access 97, click the Start button and
choose Run. Type the appropriate path for the Access 97 setup program
(which is probably on your installation CD), using the /y switch:
E:\setup /y
(Be sure your installation disk is in the drive.) Follow the
instructions for reinstalling Access 97.
If that doesn't do the trick, you can try creating the key yourself
using the Registry Editor. However, if you're not familiar with the
Registry, we suggest you find someone who is. Fooling around with the
Registry can have grave consequences and should be attempted only by
those who really know what they're doing.
If you've based a report on a parameter query, you can print the
parameters as part of your report. For instance, if your parameter is
a date, you can print that date in the header of your report. Simply
add a text box to your report's header and specify the parameter by
name as the control's Control Source using the form
=Reports![reportname]![parametername]
Let's suppose you want to add the feature to a report named
rptDates and your query's parameter is [Enter Date]. In this case,
you'd enter the expression
=Reports![rptDates]![Enter Date]
If you need to inhibit the navigation buttons on an Access form,
you can still track the total number of records in the underlying
recordset. To do so, add a text box to the form and name it txtTotal.
Then, add the following code to your form's Current event:
Private Sub Form_Current()
Me.RecordsetClone.MoveLast
Me![txtTotal] = Me.RecordsetClone.RecordCount
End Sub
The rules to follow when working with recordsets are fairly
straightforward. However, you must remember that the underlying table
can also affect the recordset. For instance, if you set a field's
Required property to Yes but leave the recordset variable for that
field blank, VBA returns an error instead of updating your table with
the new record. To avoid this problem, set the Required property in
the underlying fields to No and the Allow Zero Length property to Yes.
You can use the asterisk (*) character from the field list to
represent all of a query's underlying fields in the design grid.
However, some functions won't work on the asterisk character. Instead
of using this character, you can enter all the fields by
double-clicking the field list's title bar, then simply dragging the
selected block of field names to a Field cell. Access automatically
fills in the Field cells with all the field names.
As you try to squeeze another button on your toolbar, you might
wish for a wider toolbar. You can't expand the toolbar beyond the
Access container window, but you CAN reduce the amount of space taken
up by text boxes and drop-down lists such as the Font, Font Size, and
Zoom controls on your toolbars.
To narrow (or widen) these text-based toolbar buttons, start by
right-clicking any toolbar and choosing Customize. With the Customize
dialog box displayed, select the text box on the toolbar by clicking
it. Move the pointer over the box's right border to change the pointer
to a vertical bar with left- and right-pointing arrows. At this
point,click and drag the border in or out. Finally, click the Close
button.
Access 2000 offers a new organizational feature--groups. Groups are
objects that relate to one another in some way. For instance, you can
create a group called Customers and gather all your database
objects--tables, queries, forms, and reports--that pertain to your
customers into that group. When you want to access a customer object,
you need only click the Group icon to see them all.
To create a group, right-click the Groups button on the Object bar
and choose New Group. Enter an appropriate name and click OK. Access
will display that group name under the Groups icon. To add objects to
the group, simply drag them from the Database window list to the
appropriate group listing on the Object bar under the Groups icon.
All objects added to a group are available as part of the group and
the appropriate object listing in the Database window. You don't
actually move any object; you're just creating a shortcut to it. So
all your tables, queries, forms, and reports are still listed
together.
When you view data in a linked table, Access has to retrieve
records from another file, which can slow down your application.
Fortunately, there are a few simple guidelines you can follow to
improve the performance of linked tables:
- Limit the number of records that you view by using filters and
queries.
- Don't move up and down the page unnecessarily in a datasheet.
- Don't jump to the last record in a large table unless it's
necessary.
- If you just need a form for data entry, set your form's DataEntry
property to Yes to avoid loading existing records into memory.
In our last tip, we discussed a few ways to improve performance
when working with linked tables. That tip begs the question: Do we
link or import?
Access is a bit faster when working with native data, so go ahead
and import if you don't plan to use the data in any other
applications.
However, if you know that you'll be updating the data in a program
other than Access, stick with linked data. You can use all the Access
features you're used to--queries, forms, and reports--on linked data.
(Of course, if you're sharing Access data with other users via a
network, you'll want to link to that data.)
The Name AutoCorrect feature automatically renames forms, reports,
tables, queries, fields, and controls. However, what you might not
realize is that this feature isn't turned on by default in a converted
database. If you think that you might have need for this feature after
converting your database, turn it on. To do so, begin by choosing
Tools, Options and clicking the General tab. Select the Track Name
AutoCorrect Info check box if you want to maintain the information
Name AutoCorrect needs to run but not take any action. Select the
Perform Name
AutoCorrect check box if you want to perform Name AutoCorrect as
changes occur. Select the Log Name AutoCorrect Changes check box if
you want to keep a log of the changes Name AutoCorrect makes. You'll
find this log in the Name AutoCorrect Log table.
Remember, Name AutoCorrect works only with objects. It can't update
references in macros or VBA code.
You can easily print Help topics by clicking the Print icon in the
Help window.
However, as you know, most Help topics are spread across several pages
with many subheadings. That means you must access each one and print
it to get a set of an entire Help topic. Right? Not anymore. To print
the entire topic, first locate the appropriate book in the Contents
tab. Then, click the Print button. In the Print Topics dialog box,
click Print The Selected Heading And All Subtopics, then click OK
twice.
Furthermore, Access prints the topic continuously, rather than
printing each heading on a separate page.
You can use a field or control's Default Value property to enter
data automatically. This is especially helpful when you repeat one
entry often from record to record. When the default entry isn't
appropriate for the current record, you can simply overwrite it by
typing a new entry. If you change your mind, you can return to the
default value by pressing Ctrl-Alt-Space, which overwrites the current
entry with the
default value.
Input masks allow you to display data in a particular format.
However, that format isn't usually stored with the data. If you choose
to store a literal character with the data, you must specify the value
0 in the input mask's second component. For instance, if you use an
input mask to display a hyphen character between the two components of
a phone number, you might use a mask similar to
!\(999") "000\-0000;0;_
Notice that the second component is the value 0. That means Access
stores the entry (555)123-4567 as (555)123-4567, not 5551234567.
There's no right or wrong decision. However, when you store literal
characters with data, you need to make sure the field size can
accommodate the extra characters. As you can see, our phone number
field needs to allow for 13 characters, not the usual 10.
Most of us use combo and list boxes frequently, so it's important
to make them as efficient as possible. Here are a few tips for
creating the most efficient combo and list boxes:
- Include only those fields that you really need--extraneous fields
use resources unnecessarily.
- Set the Auto Expand setting to No if it doesn't matter what item
Access displays in the text box portion of your combo box.
- Don't hide columns using the Column Widths property of 0 unless it's
absolutely necessary.
- Treat list items as text whenever possible, because numerical data
can slow things down.
If you follow these few rules, your combo and list boxes should
perform exceptionally well.
If you enter one- and two-line addresses in the same table, you
probably have two fields, such as Address1 and Address2. However, if
not all your addresses contain two lines, you can end up with blank
lines between the Address1 and City fields in your reports and mailing
labels. To avoid this problem, apply the Can Shrink property to the
Address2 control. That way, Access omits the blank line when there's
no entry in the Address2 field. Unfortunately, this technique won't
work if your control has an attached label, since Access still prints
the label text.
Access users rely on queries to manipulate and analyze their data,
so it's important that queries be efficient and run quickly. To get
the most from your queries, try applying the following tips:
- Include only the fields you really need.
- Avoid on-the-fly queries; use stored queries whenever possible.
- Compact and compile often.
- Avoid nesting queries.
- Don't use the asterisk (*) character in the query design grid;
specify the individual field names instead.
By default, Access waits until you press Enter before it selects
the next control on your form. However, this is a wasted keystroke you
can eliminate by setting each control's Auto Tab property to Yes. This
property also requires that you set an input mask. Once the data
you're entering satisfies the input mask, the Auto Tab property
automatically selects the form's next control. We don't recommend you
use this feature unless you can apply it consistently; otherwise,
you'll just confuse your users, who won't know when to press Enter.
The VBA IDE (Visual Basic Editor) has three windows--the Properties
window, the Project Explorer window, and the Immediate window. If the
setup makes you a little claustrophobic (it does me), then close the
Project Explorer and Properties windows when you're working in the
Immediate window writing code. You'll cut down on the clutter and
increase the amount of code you can view on screen. (You'll probably
have to resize the Immediate window yourself.)
Anytime you need to access the Properties or Project windows, you
can do so with a quick keyboard shortcut:
- Press Ctrl-R to display the Project Explorer.
- Press F4 to display the Properties window.
In our last tip, we talked about closing the Project Explorer and
Properties window in the VBA IDE (Visual Basic Editor) so you can have
plenty of room to work in the Immediate window. If you're lucky enough
to have a large screen, you can track your code and watch its results
in your Access database at the same time. How? Split the screen
between the VBA IDE and the Access container window.
First, open the VBA IDE, restore it, and then size it the way you
want. You might start with about half the screen. Minimize that window
when you're done. Next, display the Access container window. Restore
it and size it to approximately half the screen. At this point, click
the IDE icon on the taskbar. You may have to resize and position the
windows a bit to arrange them just the way you want them. The result
you're looking for is two windows, one on top the other (or side by
side). That way, when you run your code, you can see its effects on
your database.
If you're a typical user, you probably rely heavily on wizards to
create forms and reports. You can also use them for applications
development--wizards aren't just for creating objects. Whether you're
a developer or a beginner, you should at least take a look at these
wizards:
- Database Wizard: Lets you choose from several completed business and
personal databases.
- Import Text Wizard: Walks you through the process of importing
foreign data into an Access table.
- Command Button Wizard: Offers many predefined button tasks.
- Combo Box Wizard: Helps you display items in a combo box.
- List Box Wizard: Helps you display items in a list box.
- Option Group Wizard: Similar to the Combo Box Wizard, except you're
working with an option group.
- Database Splitter: Splits your database into a front-end/back-end
database. This wizard is new to Access 2000. The feature was available
in earlier versions as an add-in.
All of these wizards can help you get your work done faster. If
you're not that familiar with VBA, they may even help you learn more
about developing Access with VBA. Of course, you can do all the work
the wizard does yourself, but why would you want to? Take advantage of
all of Access's tools--including these wizards.
We've talked a bit about dockable toolbars, but some windows are
also dockable. Simply double-click the window's toolbar to dock a
floating window. For the most part, you'll use this feature in the
Visual Basic Editor. (With objects like forms and reports, a
double-click on the title bar maximizes the object.) Not all windows
are dockable, though. A quick glance is all you need to discern
whether a window is
dockable. If the title bar has only a Close button, the window isn't
dockable. All dockable windows also have the Minimize and Maximize (or
Restore) buttons.
The Microsoft Office 2000 Resource Kit is now available, and you
can find information online at
http://www.microsoft.com/office/ork/2000/default.htm
If you think the Resource Kit is just for network administrators
and IT professionals, check out this Web site and you'll find several
tools, utilities, and support documents. You'll also find a few treats
for the average user--wizards, sample files, and tons of information.
There's even a white paper on the new Office 2000 Web components.
Microsoft offers an Excel workbook that lists all (yes, all) the
Office error messages and their corresponding values. The file,
Errormsg.xls, is available by download at
http://www.microsoft.com/office/ork/2000/appndx/toolbox.htm#custalrt
Once you've downloaded the file--an EXE file, which you should find
in the Program Files\ORKTools\Download\Documents\Cstalert folder--run
it. The EXE file will install several files, including Errormsg.xls.
At that point, simply open Errormsg.xls in Excel. Each Office
application has its own sheet--click the corresponding tab to view the
error messages for an application. Since you're working with an Excel
workbook, you can easily add your own notes and information to each
record.
If your Access 2000 application contains security, you should know
that you can't convert an Access 2000 workgroup file to Access 97. You
can convert the 2000 database to 97, but you'll need to create new
security settings for the 97 version in Access 97. We know of no
workaround for this limitation.
To convert the 2000 database, choose Tools, Database Utilities. Then,
choose Convert Database and select To Prior Access Database Version.
Most developers use naming conventions to assign consistent names
to their objects. What you might not realize is that an Access object
name must be 64 characters or less. Try to use 65 characters, and
Access will return an error. There are a few other guidelines you must
exercise when naming objects:
- An object name can include any combination of letters, numbers,
spaces, and special characters except a period (.), an exclamation
point (!), an accent grave (`), and brackets ([ ]).
- An object name can't begin with a leading space.
- An object name can't include control characters (ASCII values 0
through 31).
- An object name can't include a double quotation mark (") in table,
view, or stored procedure names in a Microsoft Access project.
You probably know that you can base a form or report on a query as
well as a table. What you might not realize is that the query must be
a Select query. You can't base a form or report on an Action query
(Delete, Update, Make-Table, and Append).
The workaround is simple--base your form or report on the result of
the Action query, not the query itself. While this tip may seem
obvious, it's easy to make this mistake when you're used to basing
forms and reports on queries.
Have you ever used the GoToControl or SetFocus method to select a
control and had it fail to work as expected? These methods might not
work for several reasons, which aren't always obvious. When this
happens to you, check for the following first:
- Are you trying to give focus to a control that can't receive focus?
Not all controls can receive the focus. The label control is one of
these control types that can't receive the focus.
- The control's Visible property must be set to Yes. If the control's
Visible property is set to No, that control can't receive the focus.
- The control's Enable property must be set to Yes. An enabled control
can't receive the focus.
So, if you have trouble with one of these methods, check these
three conditions.
Continuous forms are great for viewing several records at the same
time. However, you can't view a form in Continuous mode if that form
contains a subform, an ActiveX control, or a bound chart. If your form
contains one of these three objects, you must set the main form's
Default view property to Single Form or Datasheet.
If you really must view the main form in Continuous mode, try
working with two forms--one with the embedded object and one without,
and use a pop-up form to display the embedded subform or chart if
necessary.
The Font Size tool on the Formatting toolbar lists sizes from 8 to
72, but Access isn't limited to those sizes. You can specify a Font
Size property from 1 to 127 using VBA. For instance, to change a
report's font size, you'd use code similar to
Dim rpt as Report
Set rpt = "rptMyReport"
rpt.FontSize = 24
Just because you specify a font size doesn't mean Access or your
printer will be able to accommodate that size with the specified font.
You must have the appropriate font installed, and the font itself must
be able to accommodate the size you choose. If it can't, Access will
substitute a similar font if possible.
You probably know that a combo box or a list box can have multiple
columns. In fact, the control will display all of the fields in the
bound table or query from left to right up to the number you specify
as the control's Column Count property. In other words, if the bound
object has ten columns and you set the control's Column Count property
to 8, the control will display the first eight columns from left to
right.
There is one catch that you might not be aware of--the Column Count
property value must be an integer 1 through 255. Neither the combo box
nor the list box can display more than 255 columns. However, this
limitation shouldn't cause too much trouble, since 255 columns will be
more than adequate for most uses.
Microsoft has identified a problem you may experience with Access
2000. The problem occurs when Access 2000 improperly evaluates an If
Then Else expression comparing a variable that is null. Microsoft's
solution is to use IsNull() check on variables in the If Then Else
expression. Rewrite the expression to include IsNull(), as in the
following example from Microsoft.
The expression below will not evaluate properly if strName or
txtName is Null.
If strName = txtName then
strName = '1'
Else
strName = '2'
End if
Change the expression to:
If strName = txtName and Not IsNull(strName) and Not IsNull(txtName)
then
strName = '1'
Else
strName = '2'
End if
Having trouble keeping your NumLock key on while using Access 2000?
Microsoft has acknowledged a problem with Access 2000 where the
NumLock key keeps turning off. A well-known bug in all versions of
Access causes SendKeys to switch off the NumLock key. Unfortunately,
no solution is available at this time. Keep an eye on the Microsoft
Support Web site for a future fix.
In our last tip, we told you that the list box and combo box are
both limited to displaying 255 or fewer columns. The number of columns
that control displays is determined by the control's Column Count
property (which can't exceed 255).
This means the control's Bound Column property can't be greater
than the Column Count property. The Bound Column property determines
where the control stores its value. You can display many columns, but
the control is bound to only one column. While this tip is another one
of those seemingly obvious tips, it's an easy mistake to make but hard
to find. It's especially easy to make this mistake if you change the
Column Count property setting but forget to update the Bound Column
setting.
There are many methods for creating a set of random records.
Perhaps the easiest is a simple query expression in the form
RandomExpression: Rnd([field])
where field is any value field in your bound object. You can use
this expression on any value field: AutoNumber, Number, or Date/Time.
Next, apply any sort order other than None to the RandomExpression
field. It doesn't matter which you choose--either will work.
Running this query produces a random value (based on [field]) for
each record. Sorting the RandomExpression field has the effect of
sorting the records in random order.
In our last tip, we talked about using the Rnd() function in a
query expression. Access's Rnd() function returns random values, but
it doesn't always work the way you might expect. That's because the
argument you pass to this function using the form
Rnd(value)
changes the seed value. This seed value determines where Access
begins to generate random values. If value is greater than zero (or
not supplied), Rnd() returns the next random number in the sequence.
If value is less than zero, Rnd() returns the same number. When value
equals zero, Rnd() returns the most recently generated number.
In our last few tips, we've discussed using an expression to return
a random value using the Rnd() function. Did you know there's a limit
on the number of characters you can use in any expression? There
is--an expression can't contain more than 2,045 characters. While
2,045 characters should be adequate most of the time, it is possible
to write an expression that Access simply can't evaluate because it's
too
long. If this should happen to you, there's no easy workaround.
Perhaps the best solution is to write your own procedure function and
break down the expression into components rather than trying to write
the entire task as one statement.
When you need to display a message, you probably use the MsgBox()
function. Unfortunately, you can't control how Access displays it. For
instance, if your message is long, Access, not you, determines where
to wrap it. However, you can take back control using the Chr()
function.
To experiment, simply open the Debug window (the Immediate window
in version 2.0), enter the statement
MsgBox("This message is too long so" & Chr(13) & "we added a
Chr(13) function to wrap the text.")
and then press Enter. Access returns a message box displaying two
lines of text. Furthermore, the Chr(13) function inserts a line break
between the words "so" and "we." Without this function, Access, not
you, decides whether and where to wrap the text.
You won't often want to delete a table, but it's an easy process
when you do. Simply select the table in the Database window and press
the Delete key, then click Yes when Access wants you to confirm the
action. If you want to omit this action, press Shift-Delete, but be
very careful when using this shortcut. In fact, always think twice
before deleting a table full of data. You might want to archive the
data instead of deleting it. However, if you do delete a table and
change your mind, you can undo the delete by clicking the Undo button
or choosing Undo from the Edit menu. But you must do so immediately
after deleting the file--before you take any further action.
Access 2000's Database window has a new look. The Object Bar offers
shortcuts and the Database Window toolbar organizes object commands.
You can open new objects in Design view or launch one of the object
wizards by taking advantage of these shortcuts. For instance, if you
click Tables on the Object Bar, Access will display the following
shortcuts:
Create table in Design view
Create table by using wizard
Although many users find these shortcuts convenient, a few users
find them annoying. Fortunately, you can inhibit these shortcuts. To
do so, choose Options from the Tools menu. Then, click the View tab
and deselect the New Object Shortcuts option in the Show section.
Macros help us automate many redundant procedures, but on occasion
you need to ignore them. When this is the case, you can in effect turn
off the macro until you're ready to use it again. To do so, open the
macro window and enter the value False in the Conditions column.
Save and close the macro and return to your work.
When Access encounters the command to execute that macro, it
appears to ignore the request. Actually, Access is running the macro,
but it's interpreting the False value, which means the same as doing
nothing. When you're ready to use the macro again, open the macro
window and delete the False value. Remember to save this last change.
When you work with decimals, you must use the Single or Double data
type. After specifying the data type, you should specify the number of
decimal places using the Decimal Places property. When you're setting
this property in Design view, the property offers a list of value
settings from 0 to 15 and Auto. The Auto setting is the default and
relies on the Format property to determine how many decimal values to
display.
If you're using VBA to set this property, you can specify the
values 0 through 15. If you want to specify the Auto setting, use the
value 255.
Generally, you add controls to a form or report by clicking the
appropriate control button on the Toolbox in Design view and then
clicking the form or report. If you want to add a second (or several)
controls of the same type, you probably return to the Toolbox and
click the same button before inserting the additional control.
Fortunately, this repetitive task isn't necessary.
If you want to add several controls of the same type to a form or
report, simply double-click the control button instead of using a
single click. Double-clicking a control button temporarily selects
that control, so you can insert as many controls as you like without
clicking that button again on the Toolbox. To reset the current tool
selection, click another control button or the Selection arrow on the
Toolbox.
A quick way to determine the first or last date in a date field is
to run a Totals query. After specifying the query's group, select the
First or Last aggregate function for the query's date field and then
run the query. Doing so returns the first or last date, accordingly,
for your group of records.
To specify a Totals query, open the query in Design view and choose
Totals from the View menu. Doing so will display the Totals field in
the query design grid. Open this field's drop-down list to see a list
of aggregate functions, including First and Last.
When working with code in the VB Editor, you can return to your
form or report without losing your spot in the code module--and you
don't have to do a thing to mark your place. The next time you're
working in a code module, pay close attention to the position of the
cursor before you close the module. After closing the module, reopen
it and you'll find the cursor in the same spot you left it in. That
way, you can go right back to the code you were working on if
necessary.
Access 2000 makes working with foreign data easier than ever. If
you open a foreign file, Access 2000 will automatically create a new
database and link to the data, which can save you a lot of setup time.
You can work with dBASE files, spreadsheets, text files, and Paradox
tables. Of course, the data will need to be arranged in tabular format
with rows and columns before you open that file in Access. In
addition, the foreign file should contain the same type of data in
each column and the same number of fields in each row.
Once you've launched Access, click the Open tool and locate the
foreign file. Once you select the file and click OK, Access will
create the database and set the links. If Access displays a link
wizard, simply follow the instructions to complete the link. This
capability isn't available with Access projects.
The Filter By Form data is one of the easiest ways to search for
matching data in Form View. Simply click the Filter By Form button on
the Form View toolbar and Access will display a blank version of your
form. At this point, you enter the data you're searching for in the
appropriate control and then click the Apply Filter button on the Form
View toolbar. For instance, if you're viewing records in a customer
database and you want to see only those customers in the state of New
York, you'd enter New York in the State control and then click the
Apply Filter button.
Access will search the underlying (or bound) recordset and display
only those records where the state field contains the entry New York.
Access will also display the number of matching records at the bottom
of the form along with the string "(Filtered Set)", so you can easily
tell when you're working with a full set of records or just the
results of a temporary filter.
Once you've viewed the matching records and you're done with the
filter, simply click the Remove Filter button, which is actually the
Apply Filter button. This button acts like a toggle switch--you click
it to apply a filter and then you click it again to remove the filter.
If the Form View toolbar isn't available, you can choose Records,
Filter, Filter By Form.
In our previous tip, we talked about using the Filter By Form
feature to view records that match a specific search string. Many
features available in Form View are also available with the Filter By
Form form. For instance, if you're working with a single form, you can
click the View button and choose Datasheet to view all the records at
one time. Fortunately, this feature also gives you a quick glance at
all the records in your filtered set. After specifying the search
string and displaying the filtered set, simply click the View button
and choose Datasheet, as you would in Form View. Access will display
just those records that match your search criteria in Datasheet View.
Whether you're working with event procedures or function
procedures, most form-modifying code will return an error if that form
is open in Design View instead of Form View. However, there's an easy
way to make sure the form is open in Form View before you execute any
of those changes to your form via code. The following procedure
returns the Boolean value True if a form is open in Form View and
False if that form is open in Design View.
Function ObjectState(name As String) As Boolean
ObjectState = Forms(name).CurrentView <> 0
MsgBox ObjectState
End Function
We included the MsgBox statement so you can test the procedure.
Open any form in Design View and run the function by entering the
statement
ObjectState("yourformname")
in the Debug window. Access will display the word False in a
message box. Now, open the form in Form View and try again. This time,
Access will display the word True in a message box. When you include
this function in your code, you'll probably want to omit the MsgBox
statement.
You can also use the CurrentView property to determine how a data
access page is displayed.
Access 2000 takes the guesswork out of displaying four-digit years
in your date fields. Now, you can force all your controls and fields
to display four-digit years with just a few clicks. By displaying all
four digits, you can ensure that a date's century is the right one
with just a quick glance. To enforce this behavior, you'll need to
access the Options command from the Tools menu and then click the
General tab. In the Use Four-Digit Year Formatting section, you'll
find two options:
* This Database: Sets the four-digit formatting option for the
current database.
* All Databases: Sets the four-digit formatting option at the
application level and affects all databases and Access projects.
Both options will override any Format property settings you've
defined for individual fields and controls.
We think it's odd that there's no direct way to import data from
Works 99 to Access 2000, but it can still be done. If you've finally
decided to take on Access but your data's in Works 99, don't worry. Go
ahead and make the switch. When you're ready to transfer data, follow
these two guidelines:
* Export your Works 99 database files to .dbf or .txt format.
* Export your Works 99 spreadsheets to .wk1 format.
When exporting database files, use the .dbf format if you want to
retain field names. Once you've exported the database or spreadsheet
data, import it into Access 2000. You can find specific instructions
for importing Works 99 data at
http://officeupdate.microsoft.com/2000/articles/AcWorks.htm
In Access 97 you could add formatting codes to a MsgBox function.
This feature is still available in Access 2000, but you'll have to use
the MsgBox action because the MsgBox function doesn't support the
formatting codes in Access 2000. For instance, the following steps
will display a message box's first line of text in bold.
First, open a macro window by selecting Macro in the Object bar and
then clicking New. Choose MsgBox from the first Action cell's
drop-down list. Then, in the Message argument field, enter the
following string (don't use quotes as you sometimes do with strings):
This line is bold@This line is plain@This line is plain
Click the Save button on the toolbar and name your macro. Then,
click the Run button.
Access will display a message box with three lines of text. The
first line, "This line is bold", will appear in bold typeface. The
next two lines will both display the text "This line is plain" in
normal, or plain, typeface.
It's easy to hide a table so the average user doesn't know it
exists. Right-click the table in the Database window, then choose
Properties from the submenu. In the resulting Properties dialog box,
click the Hidden option at the bottom of the box. Next, click Apply
and then click OK.
When you're ready to work with the table, choose Tools, Options and
click the View tab. Then, select Hidden Objects from the Show options
and click Apply. Finally, click OK to return to the Database window,
which will now display your hidden table.
A few tips ago, we shared a procedure that returns True if a form
is open in Form View and False if that form is open in Design View. If
the form isn't open at all, your procedure will return an error.
Function ObjectState(name As String) As Boolean
If SysCmd(acSysCmdGetObjectState, acForm, name) <> 0 Then
ObjectState = Forms(name).CurrentView <> 0
End If
End Function
This function will return True if the form is open and in Form
View, and it will return False if the form is open and in Design View
or not open at all.
Have you ever added a Sum function to a report's footer expecting
to return a grand total of one of your fields? If you have, you found
it didn't work. Instead of a total, your function returned an error.
That's because the Sum function won't work in a report's footer. To
work around this limitation, add your Sum function to some other
section of your report and set its Visible property to No. Then, in
the report's footer, add a text box and set its ControlSource to the
name of the text box that contains your working Sum function. For
instance, if you named the control that contains the Sum function
txtTotal, you'd then add another text box to the report's footer and
use the expression
=txtTotal
as that control's ControlSource property.
When you select a control, Access can display text in the status
bar that reflects the purpose of the current control. This behavior
provides a good opportunity to define the control or give hints on how
to enter the correct data. To display such a message, simply add the
desired text to the control's Status Bar Text property. This message
can contain up to 255 characters, but Access displays only what fits
in the space allowed--so if your status bar is cluttered, you'll need
a short message.
There are two ways to use the New Object button: You can click the
drop-down arrow to display a list of items, then choose the
appropriate object, or you can click the button itself. This button
displays the type of object you last selected. To create another
object of the same type, simply click the button instead of opening
the drop-down list and selecting that object type.
Microsoft has identified a bug in Access 2000 that results in the
error message "Error Message 2046 Calling OpenForm or OpenReport with
Access Automation." When you automate Microsoft Access and call either
the OpenForm or OpenReport method, you receive one of the following
errors: "Run-time error '2046': The command or action 'OpenForm' isn't
available now" or "Run-time error '2046': The command or action
'OpenReport' isn't available now."
This problem occurs when the Database window is hidden at the time
you call the OpenForm or OpenReport method from automation.
You can work around this problem with one of the following
techniques:
- Make sure the Database window is shown before calling the
OpenForm or OpenReport method.
- Create a module in the Access database that contains the code to
open the form or report, then call that procedure from automation
using the Run method.
A format determines how Access displays (or prints) your data. You
may think of Access as a smorgasbord when it comes to formatting
options, but push yourself away from that table before you make a
mess! Professionals agree: Limit special formatting for emphasis. In
other words, you should apply formats sparingly, and only when they
complement the data. Otherwise, your formats may lose their appeal. In
fact, too many formats in the pot can create a negative distraction.
We've had a lot of people ask how to use ADO to connect to the
database. That's because ADO is typically used with foreign data
(although we'll all be using it in the future as Microsoft plans
eventually to replace DAO with ADO). As a result, most examples and
documentation stick with connections to files other than the current
file. If you'd like to use ADO in the current database, use the
following code to open a connection to the database:
Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Then, refer to your cnn variable when you open your recordset in
the form
rst.Open source, cnn, cursortype, locktype
where source identifies your data source, cursortype is one of four
constants that identify the position of your cursor, and locktype is
also a constant that specifies your locking preference. As a rule,
you'll use adOpenKeyset and adLockOptimistic, respectively. Now, let's
suppose you want to open a recordset based on a table named tblMyWork
in your current .mdb file. To do so, you'd use the code
Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rst.Open "tblMyWork", cnn, adOpenKeyset, adLockOptimistic
You can replace the cnn variable with the connection reference in
the form
rst.Open "tblMyWork", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Most multiple-table queries are based on related tables. If not,
Access will create what's known as a cross, or Cartesian, product.
That means Access joins every record in every table. For instance, if
you add a ten-record table and a 20-record table, your query will have
200 records. It's not often that you'll want this kind of datasheet,
but you might.
Let's suppose you want to match each of your products with every
storage medium in your warehouse to learn the least expensive way to
store your inventory. To do so, you'd join your product and storage
medium tables without creating a relationship. The product would be a
record for every product in every medium. At this point, you could
compare all the mediums for each product to determine which is the
least expensive. Of course, there'd be more calculations going on, but
this simple example gives the gist of the arrangement.
Last month we talked a bit about changing a field's name. If you
change a field's name in Datasheet view, you're making a permanent
property change. If you change a field's name using a query
expression, you don't modify the underlying table, but any subsequent
object that's built on that query will use the new name you specify in
the query expression. If you'd like to change a field's heading
without changing any of the underlying references, use the field's
Caption property in the query design grid. Once you've specified the
field in the query design grid, right-click that field (in the grid)
and select Properties from the context menu. Next, enter the heading
you'd like to see in the query results as the Caption property in the
Field Property sheet. Access will display the Caption property as the
field's heading in Datasheet view (after you run the query). However,
Access won't change the field's name in the query or the underlying
table.
It's a good idea to name a control as soon as you create it, if you
plan to change the default name. If you decide to give the control a
more descriptive name later, you'll have to update all the code that
references that control. Most important, you'll need to update the
control's event procedures. VBA won't update these for you, which
explains why your control ceases to work after you change its name. To
update the control's code, locate the original procedure in the module
and change the name. For instance, if you rename a command button
named Command0 to cmdOpen, you should find the original procedure name
Command0_Click()
and rename it accordingly
cmdOpen_Click()
While we're on the subject of table design, you should be
especially careful when you change the design of an existing table.
You probably know that deleting a field will also delete the data in
that field. However, what you might not realize is that if you add a
field or change the name of a field, you must manually update your
queries and forms if you want to include that new or modified field in
your dependent object. Access won't automatically update your objects
for you. In addition, if you reduce the size of a field, you might
inadvertently truncate data, so be careful. For instance, if your
field handles 50 characters and you change that to 6, any existing
data that is 7 characters or longer will be trimmed after the sixth
character.
Many people work directly with data in Datasheet view. When you're
working in Datasheet view, you can easily change your mind if you
change an existing field entry in one of three ways:
Press the Undo button on the Table Datasheet toolbar.
Choose Edit, Undo Typing.
Press the Esc key.
Macros automate a database by repeating actions and tasks for you.
To create a macro, you open the macro window and enter a macro action
in the Action column. There are three ways to enter a macro action:
Type it directly into the Action cell.
Choose the action from the Action cell's drop-down list.
Drag an object from the Database window to the macro window.
Now, you're probably familiar with the first two options, but the last
option might be new to you. Once you've opened the macro window, click
the Database Window button on the Macro Design toolbar. Then, drag an
object from the Database Window into the macro window. Access will
automatically default to the appropriate open action and enter the
dragged object's name as that object's name property.
When you click on most controls, you produce a Click event. Right?
Well, let's see. When you click a text box, the control fires its
Click event--that much is true. However, if you click inside the text
box component of a combo box, you don't trigger that control's Click
event. So what's the difference? The combo and list box controls don't
fire their Click event until the user selects an item from the
control's list. This behavior is considerably different from other
controls because they trigger their Click event as soon as the control
receives the focus. So consider the outcome carefully when you attach
code to a combo or list box's Click event.
Access offers an unusual data type named Yes/No. It's really a
Boolean data type and stores one of two values: -1 or 0. The field
displays a small check box. When checked, it equals Yes, or -1. When
not checked, it equals No, or 0. In earlier versions of Access, a
simple Sum function was used in a query expression to count the number
of Yes responses in the form
Yes: Sum(field)
The function returned a negative value, as all the fields contain
only -1 or 0. If the negative value is a problem, you can wrap the
expression in an Abs() function in the form
Yes: Abs(sum(field))
Access 2000 can handle this too, but you might have to alter the
query's SQL expression. If you receive an error message about trying
to group on fields with the asterisk character, open the query's SQL
window by clicking SQL from the View button and delete the * character
and the comma right before the asterisk in your SQL clause. If you
want to count the No fields, use a query expression in the form
No: Count(field) + sum(field)
This simple expression will count the number of fields and then add
the absolute value of just the yes fields. For instance, if you have
three records and two contain Yes values and one contains a No value,
this expression would evaluate as 3 + -2, which equals 1.
In our previous tip, we showed you how to add subtotals to a group
report. That report displayed all the detail records for each group.
Sometimes, a list of subtotals is easier to read and all you really
need. Fortunately, this type of subtotal report is easy to create.
Referring to our previous tip, simply follow the steps for building a
subtotal report. Then, delete all the fields from the Detail
section--yes, you read that right. Remember, you don't want the detail
records for each group; you just want the subtotals. Trust me, it will
work. Just in case you can't access our previous tip, we'll repeat
those instructions today:
Create your report--it needs to be a grouped report.
Open the group footer section and enter the appropriate subtotal
function. For instance, if you're wanting to display a total of each
region's sales, your function might be similar to =Sum([Sales]).
To create today's one-line subtotal report, delete all the fields from
the Detail section and close up that section.
Typically, grouped reports beg for subtotals, but the report wizard
won't just give them to you--you'll have to work for them. Here's how:
Create your report--it needs to be a grouped report. Open the group
footer section and enter the appropriate subtotal function. For
instance, if you're wanting to display a total of each region's sales,
your function might be similar to =Sum([Sales]). The results will be
an additional line below each group displaying the result of your
function. That result will reflect only the records in its group.
A self-join relationship allows you to relate records in the same
table. To do so, you simply add the same table to a query twice.
However, when you do so, Access renames the second query by adding a 1
(or the appropriate consecutive value) to the title. For instance, if
you add the table tblMyTable to a query twice, Access will rename the
second table tblMyTable_1. You can accept this default name or change
it. To change the default name, simply right-click the field list in
question. Then, select Properties from the context menu and enter a
temporary name in the Alias field. An alias helps you differentiate
between tables in the query. However, assigning an alias doesn't
modify the name of the underlying table.
In our previous tip, we discussed assigning an alias to a table in
a query. You might want to do so when working with copies of the same
table in a self-join. The Alias property also comes into good use when
your tables have long names. That's because Access won't display the
entire title in the field list's title bar. Assigning a short alias in
the query makes each table easier to find.
With all the noise about Y2K bugs and leap years, we thought you
might need to know how to tell if a particular year is a leap year.
You can use an expression in the form (year Mod 4 = 0) - (year Mod 100
= 0) + (year Mod 500 = 0) to determine whether year is a leap year. If
the year is a leap year, the expression returns -1; if it isn't, the
expression returns 0. We've seen a lot of expressions, some shorter,
but this is the only one we've come across that always works.
The Toolbox is actually a toolbar. As such, you can dock it or move
it the same way you do any other toolbar. If you prefer a floating
Toolbox, you can still move it by clicking its title bar and dragging
the Toolbox to a new position. If you decide to dock the Toolbox,
simply double-click the title bar, and Access will dock it below the
other docked toolbars. To undock the Toolbox, grab the handle at the
toolbar's left border and then drag the Toolbox back to the Access
window.
Have you ever noticed that little pencil that appears in the gray
cell just to the left of the record when you enter data directly into
a table in Datasheet view? Obviously, it means you're editing a
record, but it means more than that. The pencil indicates that new
data has been entered but not saved. So don't ignore the image, and be
sure your data is properly saved. Fortunately, you don't have to do
much to save a new record or modified data. Anytime you move the focus
from the current record, Access saves your changes. In other words,
simply move the cursor to another record, and Access will
automatically save any changes you've made.
Creating calculated controls can be awkward when you need to refer
to other controls in an expression or property but you can't remember
their names. If this happens to you, don't play hide-and-seek games
with your controls. Instead, open the Field List by clicking the Field
List button on the Form Design toolbar. All of the form's controls are
listed by their exact names in the Field List. Once you open the Field
List, you can refer to it for the control names.
Limiting the type of data you enter doesn't always require a
complex event procedure. Sometimes, the Validation Rule property is
one way to limit the type of data you enter. For instance, if you want
a control to accept only weekday values, you can enter the expression
DatePart("w",[field]) Mod 7 >= 2 as the control's Validation Rule
property. Saturday and Sunday will return the integers 0 and 1,
respectively. Consequently, checking for a value that's greater than
or equal to 2 eliminates any entry that equals 0 or 1. Therefore, the
control won't accept any date that falls on a Saturday or Sunday.
Hyperlinks are popping up all over, and it's no wonder. They
perform a unique and useful task, and they're easy to create. You can
choose Insert, Hyperlink, or you can insert a label, enter text, and
press Ctrl-K. Pressing Ctrl-K will display the Insert Hyperlink dialog
box, where you can identify the link. That method is especially
convenient when you want the hyperlink to display text other than the
link's address.
It's easy to add an identifying prefix to an AutoNumber field using
the Format property. Why would you want to? To make the entity that
value represents easier to identify. For instance, let's suppose you
use an AutoNumber field to assign a unique number to each order
record. In this case, you might want to prefix each number with the
string OrderID or OID. To do so, open the table in Design view and
select the AutoNumber field. Then, use the expression >OrderID as the
Format setting. You can use this technique with any field--we just
find it particularly useful with AutoNumber fields.
To import a table or form from another database, you probably use
the Import command. Specifically, you choose File, Get External Data,
Import, and then identify the database that contains the object you
want to import. Next, you select the object and click OK. You might
not realize you can import a lot more than objects. For instance, you
can import toolbars and relationships, and you can even import and
export specifications. You'll find these import tasks by clicking the
Options button in the Import Object dialog box.
In our previous tip, we told you how to import toolbars and more
using the Import command. You can also import a query as a table. To
do so, choose File, Get External Data, Import. After identifying the
database that contains the query you're importing, select the query
you want to import as a table. Next, click the Options button in the
Import Object dialog box and select the As Tables option in the Import
Queries section. Any query you import with this option selected will
take the form of a table in the importing database.
If you've experienced a problem converting an Access 97 database to
Access 2000, take a look at the Access 97 queries. If you have a
series of complex relationships, that may be your problem. The Jet 3.5
that comes with Access 2000 creates an index for both tables on either
side of a relationship--two for one, you might say. Since Access
allows only 32 indexes per table, a complex table with many
relationships can quickly exceed this limitation. As a result, the
conversion to Access 2000 could fail. If you find yourself in this
situation, the only choice you have is to delete some of the
relationships and try the conversion process again.
In our previous tip, we showed you a simple report expression that
will print custom messages. If you need a bit more emphasis, try
applying a few special formats to the message control. For instance,
if the message is urgent and you want to make sure it isn't missed,
change the color of the control's font to red. If you want the message
to stand out but without the urgency the red may imply, try using the
Italics or Bold style.
In our previous tip, we showed you how to determine whether a year
is a leap year by using an expression in the form (year Mod 4 = 0) -
(year Mod 100 = 0) + (year Mod 500 = 0) However, this expression
assumes you're working with a year value. If you're working with an
entire date, this expression won't work. In that case, you can use the
expression (Year(date) Mod 4 = 0) - (Year(date) Mod 100 = 0) + (Year(date)
Mod 500 = 0) where date represents your date value or a field that
contains date values.
In our previous tip, we showed you how to use VBA to open a form in
data entry mode. If you'd rather use a macro, you can. First, open a
blank macro window. Specify the OpenForm Action, and identify the form
by name in the Form Name property. Next, specify the Form View
setting, then choose Add from the Data Mode property field.
You probably know that you can limit a form to data entry mode by
using the Data Entry property. Specifically, you simply set the
property to Yes. The No setting allows data entry but also shows bound
records. By activating data entry mode, you can use a form for data
entry purposes only. That way, the form is bound to a data source, but
the user doesn't have access to existing records. If you'd like to use
the same form to switch back and forth between modes, you can use VBA
to open the form in data entry mode using the OpenForm method in the
form DoCmd.OpenForm "formname", , , acAdd The acAdd argument specifies
the form's data mode.
Have you ever found yourself adhering Post-Its with the same
message on reports you're sharing with others? Wouldn't it be nice if
Access could do that for you? It can. Adding a personalized message to
a report when certain conditions are met is easy. Simply add a control
to your Detail section (or whatever section is appropriate, but most
likely it will be the Detail section) and use an expression in the
form =Iif(condition is met, "message you want to print") as the new
control's Control Source property. For example, let's suppose you're
printing a report of grade point averages for your students. If a
student's grade falls below a certain average, you want to call that
student's parents to keep them well informed of their child's progress
(or lack thereof). In this situation, you might use an expression
similar to =Iif([average] > 75, "Call parent") When you print the
report, any student that has a grade point average below 75 will also
have a reminder next to his or her name to call that child's parent.
QUERY LIMIT
Access limits a query to 255 fields. We can say this with a smile
on our face because, frankly, most queries will never reach this
limit. However, it isn't impossible to have a query so large and
complex. If one of your queries should reach the limit, you do have a
few alternatives. First, try the most obvious solution and eliminate
unnecessary fields. This should be easy if you used the asterisk
character to add fields to the query design grid. Chances are there
are a few fields you don't really need but are included only because
you used the asterisk character (which includes all fields). Second,
if your query is the data source for a form or report, consider adding
a subform or subreport to the main object and splitting your query in
two, accordingly.
You can choose File, Database Properties, and Access will display
properties for the current database. However, the menu bar isn't
always available. If you find yourself in this situation, try
right-clicking the Database window title bar. Then, choose Database
Properties from the context menu. Access will respond by opening the
same tabbed dialog box.
The Open dialog box offers several ways to open a database. The bar
on the left is the Places bar. Each icon is a shortcut for opening
files a little quicker than the traditional method of browsing through
all the folders. The History folder displays recently opened
databases. Unless you've changed your default settings, your Microsoft
Office documents are stored in My Documents. You can quickly access
files you've stored on your desktop by clicking the Desktop icon. The
Favorites directory isn't a real directory, but rather a collection of
shortcuts to your most frequently used files. Web Folders is similar
to Favorites--it stores shortcuts to Web sites.
While we don't recommend it, you can change a field's name. To do
so, you could open the table in Design View, select the appropriate
field cell, and then enter the new name. Fortunately, you can
eliminate most of those steps by changing the name in Datasheet View.
In your open table, double-click the field name cell (the gray cell at
the top of the field). Access will respond by highlighting the field
name--the cell will now be in Edit mode. At this point, you simply
type the new name and press Enter. When you rename a field name, be
sure to update all your dependent references using the Name
AutoCorrect feature. Then, use the Replace/Find Edit command to update
any field references in your modules, since the Name AutoCorrect
feature works only on objects--it won't update modules.
A parameter query is a query that prompts you for specific data to
search for (and limit the query to). For instance, if you want to see
sales by each region, you could create a query that prompts you for a
particular region. You'd respond to the prompt by entering a region,
perhaps Northeast, and clicking OK to run the query. Now, the reason
we create parameter queries is because we run the same query on a
variety of different entries. Following our region example, you'd
probably want to run the query for each region, right? You might think
you have to close the query and reopen it. Or you might consider
changing the view to Design View and rerunning the query from there.
Fortunately, there's a simpler way. Simply press Shift-F9 to rerun a
query. Doing so will redisplay the parameter box so you can enter a
search string and begin again.
In our previous tip, we told you about the Microsoft Web Browser
control, which you may have trouble resizing. Here is a workaround for
this apparent bug. If you can't resize your Web Browser control,
select it and press Ctrl-X to cut the control from the form. Next,
press Ctrl-V to copy it back to the form. Now, resize your control and
save your form. No one seems to understand why this simple
cut-and-paste task does the trick, but it definitely does. We've been
wanting to share the Microsoft Web Browser with our readers for months
but didn't because we couldn't resize the darn thing.
Parameter queries are useful, easy to create, and somewhat improved
in Access 2000. In earlier versions, a parameter might accept invalid
data. For instance, if you were limiting your records by the contents
of a numeric field and you entered text, Access accepted the incorrect
parameter and simply returned no matching records. Version 2000
restricts a parameter to the same data type as the field you're
querying. If you attempt to enter inappropriate data, Access will
display a generic error message. This improvement means that
specifying restrictions for parameters is unnecessary in most cases.
Many simple expressions are floating around that will return a
person's age. We've tested them all and found them all lacking in some
area. Eventually, these expressions will return an incorrect age, and
you may not know it. The best route is to go with an accurate
expression, even if it's long, like the one we're about to show you.
When working with age, you can use an expression in the form
Year(Now())-Year(birthdatefield) + (DateSerial(Year(Now()),
Month(birthdatefield), Day(birthdatefield))>Now())
where birthdatefield is the field that contains your birthdates.
The expression may be long, but it's the only one we've found that
works in all situations.
Several tips ago, we showed you how to use SQL to return the top n
records in your query result. Basically, the SQL method is the same as
using the query's Top property. However, there's no corresponding
Bottom property to return the last n records in a query. The trick is
to use the Top property and sort the records in descending order. As a
result, the records that are generally last are now sorted to the top
of the query's results--what was last is now first and vice versa.
Consequently, the Top query still returns the first records in the
query result. If you decide to use SQL to return the last records in a
query, use a statement in the form SELECT TOP n field FROM table ORDER
BY table.field DESC;
Let's suppose you like the Office Assistant and you want to keep
it. However, it's a little larger than you'd like it. If this is the
case, you can reduce the size of your assistant. Display the assistant
and then position the mouse pointer over any of the assistant's
borders and click. Doing so will reduce the assistant. To enlarge it
again, repeat the process.
Using the default My Documents file folder is a great idea if you
have to back up your work on a regular basis. With all your files
automatically filed in one folder, you need only back up that one
folder. To do so, you'll simply pull that folder to your floppy drive
or zip drive (or whatever medium you're using to store backup files).
I've been using Access since version 1, and I just found the
Spelling button on the Table Datasheet toolbar. It's not hidden--it's
right on the toolbar. Nor does it have an obscure design. It has the
text ABC and a checkmark on it. What could be plainer? Simply select a
cell, a field, or the entire table, then click the button. The Spell
Checker feature will respond accordingly, just as if you'd selected
Tools, Spelling.
The SQL DELETE clause sometimes refers to a single column, but
don't assume that field's value is the only one being deleted. The SQL
DELETE clause deletes an entire record--it makes no difference if the
clause references only a single field. For instance, the SQL
statements
DELETE FROM tblMyStuff WHERE Flag = True;
and
DELETE FROM tblMyStuff.OrderID WHERE Flag = True;
both do the same thing--they both delete all the records in
tblMyStuff where the Flag field contains the True value. The second
statement won't delete just the value in the OrderID--it will delete
the entire record.
Did you know that some submenus could double as toolbars? They can,
and this capability is especially convenient if you find yourself
using one of these tools repeatedly because you'll omit a few
keystrokes. For instance, let's suppose you use the Font Color tool
often. You click the arrow to the right of the Font Color button and
choose a color--each time you use the tool, you must open it first.
You might find working with this tool as a floating toolbar a bit
easier.
To do so, open the control's drop-down list and position the cursor
over the horizontal gray bar at the top of the list. In response, the
bar will turn blue, which means you can drag this control to a
floating position. (This behavior is true of any tool that can double
as a floating toolbar.) Simply click the blue bar and drag the control
to a position off the toolbar. To close the new toolbar, click the
Windows Close button (the X button in the top-right corner).
There's a treasure chest of information available with just a few
quick clicks, and you probably don't know a thing about it. From the
Help menu, click About Microsoft Access. In the lower-right corner,
click the System Info button to open the Microsoft System Information
dialog box. Here, you'll find information about your system: hardware,
components, and even the other applications you have installed. Take a
look around--you may be surprised at what you'll learn about your
system.
If you use the Table Wizard to create tables, you're probably
saving a lot of time. However, you might be wasting some of your
resources. If you have tons of memory--and many of us do--it doesn't
really matter. Regardless of the amount of memory you have,
inefficient is still inefficient. Besides, if you're working with
thousands of records, resources may become an issue in the future. In
either case, open any wizard-created table and check the field
properties. You'll find that the wizard always uses the data type
defaults. For instance, text fields use the Field Size default setting
of 50. If the field will consume only six characters, you're wasting
space. In many cases, you can reset some of these field properties and
save on resources.
An AutoExec macro runs when you first launch a database. If you
want to disable the AutoExec macro once, you can hold down the Shift
key when you launch the database. Occasionally, you may find yourself
wanting to run a series of tests without activating the AutoExec macro
each time you open the database. In this case, remembering to hold
down the Shift key can become a nuisance. Perhaps the easiest way to
temporarily deactivate a macro for a prolonged period of time is
simply to change the macro's name. You see, an AutoExec macro must be
named AutoExec. Renaming an AutoExec macro will keep Access from
running it again until you change the name back to "AutoExec."
When scrolling through records in Datasheet view, you're not
actually changing the active record. You're just changing the areas of
the table you can see. To change the active record, you must press the
Down Arrow or Up Arrow key or the Page Down or Page Up key. As you
might expect, the Down Arrow key selects the next record, and the Up
Arrow key selects the previous record. The Page Down and Page Up keys
move the screen and the selection an entire screen. In other words, if
the active record is row 2 and the current screen displays 20 records,
pressing Page Down would select record 21. You can also use the
navigation buttons at the bottom of the table.
It's easy to forget about the Control menu--the Access key icon at
the left end of the application title bar. Clicking this icon opens a
menu that provides a list of commands for sizing and positioning the
application window. Specifically, you can use these easy-to-access
commands to minimize, maximize, restore, and even close the
application window quickly.
You probably know that you can use a query's Top property to return
only a specific number of records, instead of all the records. For
instance, after sorting all your orders by the total value, you may
want to select only the top ten sales. To do so, you'd specify 10 as
the query's Top property.
You can do the same thing with SQL using the TOP keyword in the
form
SELECT TOP n field(s) FROM table
More than likely, you'll need to also include an ORDER BY clause.
For instance, to select the top ten best sales from a table named
tblSales, you might use a statement similar to the following:
SELECT TOP 10 * FROM tblSales ORDER BY TotalOrder
where TotalOrder is the name of the field with the total sales
value for each order. If you don't specify an argument
for ORDER BY, SQL defaults to ascending order.
When using the TOP keyword, don't confuse it with a value, as in
the greatest or highest values. The keyword simply returns the first
records in the query's results.
Access allows wildcards in a parameter query. Just remember to use
the Like operator in your parameter expression. For instance, if you
want to return all your employees whose last names begin with the same
letter, you'd use the parameter expression
Like [Enter the first letter of the last name]&*
When you run the query, Access will prompt you to enter the first
letter of the last name. After you do so and click OK, the parameter
expression will evaluate to
Like "x"*
where x is the letter you entered. For instance, if you entered the
letter S, the expression will reduce to
Like "S"*
As a result, Access will return only those records whose entry in
the last name field begins with the letter S.
In our previous tip, we talked about using the * wildcard with the
Like operator to return all the records whose last name field began
with a specific letter. The Like operator is very versatile and can be
used in several situations. For instance, let's suppose that each
piece of inventory in your warehouse is identified by a part number
that consists of two digits, a hyphen, two more digits, a second
hyphen, and then two alpha characters. Furthermore, let's suppose that
you want to see all the records for part numbers between 11-XX-XX and
12-XX-XX. You might waste a lot of time trying to come up with a
Between statement, but the simplest solution is the Like operator. Use
a criteria expression in the form Like "11*" Or Like "12*"
The resulting query will return any part number that begins with
the string 11 or 12. Don't forget the * character, or you'll end up
with a completely different (and useless) set. Unfortunately, this
expression won't work if you need to see all the records between two
nonconsecutive values, such as 11 and 13.
To visit the Web while you work in Access, you can create a
hyperlink that launches a specific Web site, or you can use the Web
toolbar. However, both methods launch your default browser, which may
or may not be what you want.
If you want to view Web sites but remain in Access, you can do so
by using the Microsoft Web Browser control. You add this control to a
form and specify a URL, and the control displays the Web site from
inside your Access form. Here's how to set it all up.
First, create a table of URLs. Base a form on that table, then add
a text box and name it txtURL. Click the More Controls button on the
Toolbox and select Microsoft Web Browser from the list. Add the new
control to your form and name it msWebBrowser. Next, open your form's
module and enter the following statement in the form's Current event:
Me.msWebBrowser.Navigate txtURL
When you run the form, the Web browser control will display the
appropriate Web site for that record's corresponding URL (in txtURL).
One of the questions we see most often is how to track time when
the time period begins in one day and extends into the next (past
midnight). Fortunately, there's an easy way. But first, let's look at
a simple expression that tracks time within the same 24-hour period
(midnight to midnight). If you know that the time period will be
inclusive of the same day, you can use an expression in the form
end - start
where end marks the ending of the time period and start marks the
beginning of the time period. Unfortunately, this expression doesn't
work with elapsed time. In these cases, you'll need the expression
1 + end - start
When entering expressions in the query design grid, you can press
Shift-F2 to open the Zoom dialog box. Doing so allows you to see the
entire expression as you enter it or while modifying an existing
expression. If you prefer the mouse, you can still zoom in on your
expressions without using the keyboard. Simply right-click the cell
that contains the expression (or will contain the expression) and
choose Zoom from the context menu.
Oops!
A recent tip suggested you use the following expression
=Iif([average] > 75, "Call parent")
to print the message "Call parent" when the [average] value falls
below 75. Obviously, we used the wrong operator. We should've used the
< operator. We apologize for any inconvenience.
A Totals query is a special kind of query that allows you to group
records by a field or a group of fields. Most often, we use a Totals
query to perform calculations on groups. For instance, you might want
to subtotal all the sales by region. In this case, you would create a
query that contains the region and sales total fields, group by the
region, and sum the sales total field.
You can create a Totals query by choosing View, Totals in the query
design grid. Then, select an aggregate function in each field's Total
cell. There are lots of possibilities. One thing you want to watch for
when working with a Totals query--Access sorts the result, even if you
don't specify a sort order. Since Access must group the underlying
records, this behavior might seem obvious, but it's easy to miss if
you don't realize it's happening.
When viewing reports in Print Preview, you generally get one page.
If you'd like to see more, right-click the report, choose Multiple
Pages from the context menu, and then select the number of pages you
want to see. For instance, if you want to see two pages side by side,
you'd select the second page in the top row--the 1 X 2 Pages setting.
If you want to see three pages side by side, you'd select the 1 X 3
Pages setting, which is the third page in the top row. You can also
view stacked pages. Simply select the first page in the second row,
the 2 X 1 Pages setting, to see one page on top of another, and so on.
Ever have a Totals query return the error
Cannot group on fields selected with '*"
If so, the query's Output All Fields property is probably set to
Yes--and that just won't work. You see, a Totals query groups the
fields by entries, so you need to be careful about the fields you add
to a Totals query. Each additional field will increase the number of
"groups." When the query's Output All Fields property is set to Yes,
then your query is considering all the fields in all the underlying
data sources. The effect would be a query that's not grouped at all,
since each field is a "group."
Fortunately, the solution is simple. Open the query's properties
sheet by clicking the Properties button and set the Output All Fields
property to No.
If you work a lot with Access projects, you might be interested in
the new action query add-in that's available at
http://support.microsoft.com/support/kb/articles/q263/4/71.asp
Action queries in an Access project are stored within the SQL
Server database as stored procedures and therefore don't provide a
query design grid for creating your queries (stored procedures). As a
result, you must know the proper syntax to speak to SQL Server. The
Action Query Wizard eliminates this problem by simplifying the process
of creating stored procedures for those of us who don't know
Transact-SQL. (Don't confuse Transact-SQL with the query SQL
statements Access uses.)
The Chart Wizard makes it easy to add a chart to an existing
report. Simply open the report in Design View, then choose Insert,
Chart. After positioning the chart in the report, Access will launch
the Chart Wizard. At this point, you'll need to respond to the
wizard's question to create the chart you want. If you can't decide
where to position the chart, try the report's header or footer. In the
header, the chart can introduce your data; in the footer, it can serve
as a summary.
In our previous tip, we showed you how to add a second (or more)
column of items to a list or combo box list. You can use this same
method to display blank lines in a control. Specifically, insert a
zero-length string between each item in the list in the form
"item1";"";"item2";"";"item3";""
In addition, be sure to select the Value List setting from the Row
Source Type property. The Column Count property should be set to 1.
The combo and list box controls allow you to enter a list of items.
When you choose this route, you enter in the control's Row Source
property a setting in the form of
"item1";"item2";"item3"
You must also specify the Value List setting for the Row Source
Type property. The result is a list of items, one right after the
other, in the same order as they appear in the Row Source property
setting. You might not realize that you can display more than one
column of items in a Value List control. To do so, you simply add a
second item to the list in the form
"item1a";"item1b";"item2a";"item2b";"item3a";"item3b"
Access will display items 1a and 1b in the first row, items 2a and
2b in the second row, and items 3a and 3b in the third row. Just be
sure to update the Column Count property to reflect the appropriate
number of columns.
If you use the AutoForm and it adds a subform, you're probably
seeing the table's subdatasheet. A subdatasheet displays related
records in another table in Datasheet View (it's new to Access 2000).
If you don't want the subform, you have two alternatives:
Modify the table's Subdatasheet Name property to [None]. Use the
Form Wizard to create the form (instead of the AutoForm). If you
choose to adjust the table temporarily, open it in Design View and
click the Properties button. Choose [None] from the Subdatasheet Name
property field's drop-down list. After creating your form using the
AutoForm, restore the property setting.
Sometimes you need a little bit more analytical power than you can
get in Access. When this is the case, use Excel. Simply create a
report and export it to Excel, which is easy to do. First, select the
report in the Datasheet window. Then, choose File, Export. Next,
specify the appropriate version of Excel in the Save As Type control
and click Save. At this point, you can specify a name and location for
the new worksheet. Later, you can open the worksheet in Excel and take
advantage of all its analytical tools.
There's a new security problem with Access. If you're running
Microsoft Internet Explorer, you may be vulnerable simply by visiting
a Web site. An Object tag in the Web page, email, or newsgroup posting
can give someone access to your database files, and you won't have any
idea what's going on.
For information, visit
http://www.microsoft.com/technet/security/bulletin/MS00-049.asp
For the patch, visit
http://www.microsoft.com/windows/ie/download/critical/patch11.htm
There's even a WebCast session on the subject. For more
information, visit
http://support.microsoft.com/servicedesks/webcasts/wc061600/wcblurb061600.asp
Subdatasheets are new to Access 2000 and you're probably familiar
with them already. (We've written a few tips about
the feature.) You might not realize that Access 2000 automatically
creates subdatasheets. When you create a
relationship between two tables, Access 2000 adds a subdatasheet to
the one side of the relationship. Haven't you ever
noticed the little plus signs to the left or your records and
wondered what they meant? Those plus signs mean there are
related records in another table. Simply click a plus sign to
display related records.
When you update a bound control, Access opens the data source
internally. You won't see it happen, of course, but the table opens
just the same. If you don't close that table, you could create an
error. You see, the next time you try to modify the table, Access will
return an error because the table is already opened and locked. So,
how could you create this problem in the first place? You could force
Access to update a field. For instance, you might run a quick VBA
statement similar to
Me!fieldname = "newentry"
to change a particular field. Unfortunately, that statement alone
would open and lock your data source. If you must update a field in
this way, remember to unlock the table afterward. The easiest way to
do so is simply to move to the next record using a statement in the
form
DoCmd.GoToRecord acDataForm, Me.fieldname, acNext
Following the second rule of normalization, you probably store all
data that relates to a table's primary key in the same table. Most of
the time, this is a good idea. However, when security is an issue,
you'll want to work with different tables. A good example is employee
information--some of it is public and some will be confidential.
You'll probably want to give public access to the employee's office
phone extension, department, and supervisor. On the other hand, most
likely you'll want to keep salary and personal information
confidential. Consequently, you shouldn't store both types of data in
the same table. Store the confidential data in its own table so you
can limit access to that data.
Most of the time, it's easy to modify a table. You open it in
Design view, then make your changes and save them. If Access won't let
you modify a table, there are a few possibilities. First, make sure
the table's not open by pulling down the Window menu and checking the
active objects. (VBA code could also be the problem. We discussed this
possibility in an earlier tip named BOUND BEHAVIOR.)
If your table's not open, and you're working with a networked copy,
you may have to wait until no one else is working in the same
database. To make sure of this, choose Tools, Options and then click
the Advanced tab. If the Shared button in the Default Open Mode
section is selected, you must be the only one working in the open
database. In other words, everyone else must close the appropriate .mdb
file before you can make changes to a table. When the Shared button
isn't selected, you'll need to contact your network administrator.
Chances are you don't have permission to modify tables.
You're probably familiar with the Undo button. In fact, we've
written a few tips about this feature, which allows you to undo many
tasks. Unlike some of the other Office programs, Access lets you undo
some tasks after saving a table. For instance, try opening any table
and changing the contents of any cell. Then, save the table. Notice
that the Undo button is still enabled. If you click it, Access will
undo your change, even though you've saved the table.
By design, you can't use intrinsic constants in queries, forms, and
reports. You can use only VBA constants with VBA code. However,
there's a simple workaround--use the constant's numeric value instead.
Most of the time, this quick alternative will work just as well. The
downside: The numeric values aren't as readable and they may not
properly convert to a new version.
If you don't know the numeric value of an intrinsic constant, you
can search Help. Alternatively, you can open the Debug window (press
Ctrl-G) and run a statement in the form
?intrinsicconstant
(where intrinsicconstant represents the constant) to return its
numeric value.
Access 2000's conditional formatting feature is a big improvement
over Acce
