MS ACCESS 97, 2000 & XP
LAST UPDATED:
08 March 2009 17:03:45 -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 Access 97's format offerings. However, you can still use VBA
instead. For instance, let's suppose you want to display certain
report data in bold, depending on the contents of a second control.
You could use the conditional formatting feature or you could attach
code, similar to the following, to the Detail section's Print event:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me!chkName Then
Me!txtName.FontWeight = 700
Else
Me!txtName.FontWeight = 400
End If
End Sub
The above procedure will print the contents of a text box in bold
print when a second control evaluates to True. When chkName (which
represents a checkbox) equals True, Access bolds the contents of
txtName (which represents a text box). If the checkbox is False,
Access doesn't bold the contents. You can easily adjust this procedure
to fit most any condition. Simply update the control names,
properties, and settings, and specify an appropriate condition.
You can review a list of confirmed bugs in Access 2000 at
http://support.microsoft.com/support/serviceware/access/acc2000/76WB3DFCZ.ASP
Here, you'll find additional links to more information on each bug.
The list is a bit short; we hope Microsoft will catch up soon.
You probably know that you can create a new table in Datasheet view
by double-clicking the Create Table By Entering Data shortcut in the
Database window. This approach will open a blank datasheet with ten
generic fields. As you enter data, Access will do its best to
determine the appropriate data type for each field.
You can help Access choose the correct data type by entering
descriptive characters. For instance, if you want a field to be a
Currency data type, be sure to preface your entry with your system's
Currency symbol, such as the $ character. When entering percentage
values, be sure to include the % symbol, and Access will assign the
Number data type and Percent format. The more information you enter
with your data, the more complete your table will be.
You can perform several tasks from the Database window. To see a
complete list, right-click the Database window's title bar. Here are
just a few of the available tasks:
Open another database.
Import, link, and export objects.
Modify the current database's startup options.
Modify the current database's properties.
Other methods require a few more mouse clicks.
There's not much you can do about your human relatives, but you
have complete control over the relationships in your Access databases.
By default, Access creates a relationship between two tables in a
query, if the two tables contain a common field of the same name and
data type, and at least one of the fields is a primary key field.
If you don't like this arrangement, turn off this feature by
choosing Tools, Options, then clicking the Tables/Queries tab and
deselecting the Enable AutoJoin option.
Access forms include the Windows Close button (X) in the top-right
corner. Clicking this button will close your form. You can force your
users to close the form a certain way by disabling this button and
including some other method for closing the form, such as a command
button. You can use a Windows API to disable this button, but Access
provides a much simpler solution. Set the form's Close Button property
to No. (The default setting is Yes, which displays the Close button.)
Your form will still display the button, but it will be disabled
(grayed out). As a result, your users will need an alternate way to
close the form, so be sure to supply one.
It's easy to assume the Enabled and Locked properties are the same.
Even Help lists the two together. Although they're similar in some
respects, they aren't the same and they aren't interchangeable. You'll
use the Enabled property to determine whether a control can have the
focus in Form View. Specifically, a Yes setting means you can set the
focus to that control; a No setting means you can't. For the most
part, that means you can't select the control in Form View--even if
the control's Tab Stop property is set to Yes. The Locked property
will keep users from changing the contents of a control. You can still
set the focus to the control, meaning you can select it. However, you
can't change the control's contents.
You've probably read the term "engine" in relationship to Access a
lot, but if you're like many users, you don't know what it means. The
database engine is the software that stores and retrieves (and
indexes) your data--and you thought Access did that! You just don't
know about the engine because it works behind the scenes and you never
directly interface with it. The good part is you don't need to know
about the Jet (the name of the database engine) to take advantage of
it. However, the next time someone mentions it, you'll be able to
follow the conversation (at least a bit).
When you open an object's properties sheet, you'll see that
object's events listed in the form On event
As a result, you may think On is also part of the event's name, but
it isn't. Anytime you see an event prefaced with On and a space
between each word in the name, you're looking at the event's property
name. In contrast, the actual event doesn't contain the word On and
there's no space between the words. For example, On Click is the Click
event's property. In addition, On Mouse Move is the MouseMove event's
property.
You can use a combo box to update other form controls. For
instance, if you choose an employee by name from a combo box, you
might then use a DLookup() function to return that employee's address
and phone number. If so, there's a quicker way. If all the data you
want to display is stored in the same record, try using the Column
property in the form
ctrl.Column(x)
where ctrl is a reference to the combo box and x identifies the
position of the corresponding field in the combo box. Just remember to
add all the necessary fields to the combo box when you're creating it.
In addition, x is 0 based, so the first field in the combo box equals
0, the second equals 1, and so on.
When you add a header to a form, you'll also get a footer, because
they come as a pair. It doesn't matter whether you need both; you
still get both. To eliminate an unnecessary header or footer, you need
only close it. To do so, select that section and open its property
sheet by clicking the Properties button. Then, set the Visible
property to No.
Do you have to enter sensitive data--information that you wouldn't
want anyone else to see? If you're using a data entry form, then use
the control's Input Mask property to protect that data from being seen
by prying eyes. Specifically, set the control's Input Mask property to
Password. Then, as you type an entry, Access will display an asterisk
character--instead of the actual character--for each character you
type.
If your report needs a title page, don't go to the trouble of
typing one up separately--use the report's header. First, add any text
and graphics you need for your title page in the report's header
section. Then set the section's Force New Page property to After
Section. By doing so, you'll force Access to print the report's header
on a separate page instead of at the top of the first page, as is the
general rule. Remember, a report header appears just once, at the
beginning of the report, so Access won't print your title page
information on any other pages.
If you're still using the Insert menu to insert rows and columns,
there's a quicker way. First, right-click the header cell for the
column or row to the right or just below the column or row you want to
insert, respectively. (The header cell is the gray cell at each column
and to the left of each row.) Access will respond by displaying a
shortcut menu. Select Insert to insert a column or row. You just saved
yourself a couple of keystrokes.
Changing a control's value programmatically is a fairly common and
useful task. But there's something you should know if you choose this
route. Changing a control's value triggers both the BeforeUpdate and
AfterUpdate events. However, when you change a control's value
programmatically, the control doesn't trigger either of these events.
So you'll want to be careful about placing code in these events when
the control might be updated by VBA. The form's BeforeUpdate and
AfterUpdate events are still triggered if you alter a control
programmatically.
If your wizards turn up missing, don't worry. Microsoft has
confirmed that there's a problem with the Access 2000 wizards. Your
first step is to see if they're really missing. Check your system for
the following two files: acwzmain.mde and acwztool.mde. If you don't
find either file, you can copy it from the Office 2000 CD. To do so,
insert your Office or Access CD and open the folder Pfiles\Msoffice\Office\1033
You'll find both .mde files in this folder. Simply copy them both
to the same folder in which you installed Office 2000 (the default
folder is C:\Program Files\Microsoft Office). After copying the files,
restart Access and try to use a wizard. Access may have to install the
wizard first, but once that's done, the wizard should work fine. For
more information, review the support article at
http://support.microsoft.com/support/kb/articles/Q242/2/18.asp
You can quickly open a form or report's module by selecting the
object in the Database window and then clicking the Code button on the
Database toolbar. However, when you use this method, you should know
that you're also opening the object itself in Design View. It's right
under the Visual Basic Editor window. When you close the editor, the
object remains open in Design View and you must remember to close it
yourself.
For the most part, the order of your table fields is irrelevant,
because you can display data in any order you want. However, it's a
good idea to position your fields in the same order you plan to
arrange your data entry forms. If you use a form wizard to create your
form, that wizard will rely on the field order to add controls. If
your field order matches your data entry order, you won't have to
rearrange anything.
A form wizard doesn't have this problem. If you use a form wizard
instead of an auto wizard, you determine the order of your controls.
Simply add fields in the order you want them to appear in your form.
The Table Wizard can quickly create tables of predefined fields.
However, you may not want a complete table. When this is the case, you
can still take advantage of the wizard's available fields. With your
table in Design View, click the Build button on the Table Design
toolbar to launch the Field Builder. Specify a table and then
double-click any of the corresponding fields to add that field to the
current table. This is a quick and easy method for adding fields to an
existing table, because all the field's properties are already set.
You may have to alter a few properties, but doing so should be quicker
than building the field from scratch.
Often, related fields share the same field name, but you might not
realize that it isn't necessary. You can relate fields even if they
have different names. The only requirement is that both fields contain
the same type of data. For instance, you could relate two fields named
CustomerID and CustomerNumber as long as their data types match. If,
however, Customer ID is a Numeric field and CustomerNumber is a Text
field, the relationship won't take.
In our previous tip, we talked about subdatasheets and how Access
2000 automatically adds them to a table when you create a
relationship. If you find them distracting, you can always remove
them. With the datasheet in question open, choose Format,
Subdatasheets, Remove. Just remember that this choice is permanent.
Later, if you want the subdatasheet back, open the table, choose
Subdatasheet from the Insert menu, select the many side of your
relationship, and click OK.
A hyperlink can be difficult to select because a simple click will
trigger the link. If you need to highlight (select) the entire link
without triggering it, position the cursor to the left of the first
character in the hyperlink's display text and then press Ctrl-Right
Arrow. You only need to press the right arrow once, because your
application will interpret your hyperlink as one entity. If you like,
you can position the cursor to the right of the text and press
Ctrl-Left Arrow. Either combination will select the entire hyperlink.
In our previous tip, we suggested that you add a chart to a footer
to summarize your data. This recommendation doesn't mean that you
should never summarize data in the header--in fact, sometimes this is
just the effect you'll want. You can place totals and averages in the
header just as easily as you can in the footer. If you're worried that
the data won't be accurate, don't worry. Access makes two passes
through the database before printing (or displaying) data.
The Validation Rule field property allows you to limit entries. For
instance, a simple expression such as <10 would limit field entries to
values less than 10. You can apply this strategy to tables, since they
also have a Validation Rule property. Most of the time, you'll use
this property when you want to limit data based on the contents of
more than one field. Let's suppose all product prices can be no more
than 20 percent over their wholesale value. To limit your product
price field, you'd open the table in Design View; choose View,
Properties; and enter the expression
[Price]<=[Wholesale]+([Wholesale]*0.2)
As a result, every Price entry must be equal to or less than 20
percent more than the corresponding Wholesale value.
Technically, a Memo field stores text and accepts more characters
than a Text field, so you might wonder just why you'd need to bother
with a Text field. There are a few distinctions between the two, and
knowing them will help you choose the most appropriate data type.
A Text field accepts up to 255 characters, including letters,
numbers, and any punctuation. A Memo field can hold up to 64,000
characters. You can limit the size of a Text field using the Field
Size property; you can't limit the size of a Memo field. Text fields
accept formatting, but Memo fields don't. Therefore, you'll want to
rely on Text fields when the entry is relatively small (fewer than 255
characters) or you want to limit or format the data. When the entry is
larger than 255 characters, use a Memo field.
The Office Assistant displays several possible Help topics when you
enter a question. You might not realize it, but the Office Assistant
is using the Help system's Answer Wizard. The Office Assistant simply
limits the number of topics it shows you. Specifically, the Office
Assistant displays the first nine topics found by the Answer Wizard.
Sometimes, none of those nine topics is the answer you're seeking.
When this happens, you may be tempted to click the See More On The Web
option. The most efficient reaction is to click any of the nine topics
to open the Answer Wizard window. Here, you'll be able to view the
entire list all at once.
The block If allows any number of conditional checks. First, the If
statement itself specifies a condition. If that condition isn't met,
control will pass to an ElseIf or Else clause. (You can also omit both
and simply end the statement with an End If clause.) How do you know
which to use? If you want to specify additional conditions, use
ElseIf in the form
If condition1 Then
...
ElseIf condition2 Then
...
ElseIf condition3 Then
...
End If
You'll use the Else clause when you want to catch what falls
through the cracks. That's because the Else clause doesn't accept a
condition. For instance, we could add an Else clause to the above
example:
If condition1 Then
...
ElseIf condition2 Then
...
ElseIf condition3 Then
...
Else
...
End If
Any value or expression not caught by condition1, condition2, or
condition3 will be handled by the Else statement. The thing to
remember is that you can't add an ElseIf clause after the Else clause.
Every If statement can have numerous ElseIf clauses, but only one
Else.
Access 2000 offers more choices than ever if you're working with a
client/server environment. To help you choose between the new Data
Engine (MSDE), the Jet, or Access 2000 features, read "The Data Engine
Links to the Access 2000 Data Engine Options" white paper. You'll find
this up-to-date information at
http://www.microsoft.com/office/access/MSDtaEng.htm
When you place a command button on a form and the wizard is
activated, you begin the step-by-step process of creating VBA code
that will be fired in the OnClick event of the button. (If you already
have a button on the form, you can't trigger the wizard. You can get
to the code by clicking the builder button to the right of the OnClick
event in the Events tab of the button's Properties.)
Along with placing the correct VBA code based on your selection,
the wizard will also insert four lines of standard error handling
code.
At the top of the sub routine, you'll see:
On Error Goto err_YourButtonName
This instructs the code to move to that label in the event that an
error is "raised" in the code. At the bottom of the code, you'll see:
err_YourButtonName:
The colon at the end of this label is mandatory.
The next two lines will be:
MsgBox err.Description
Resume exit_YourButtonName
The message box will display the error message description so you
can see what went wrong. Expanding on the basics If you want to add a
little more information, you can insert the following:
MsgBox err.Number & " – " & err.Description
This displays the error number (which is helpful for tech support
and scanning Microsoft's Knowledge Base), a dash for readability, and
then the error message.
You can also add an icon and title to the message box to indicate
the severity of the error and the routine in which it was handled.
MsgBox err.Number & " – " & err.Description, vbInformation, "sub_YourButtonName"
Have you ever tried deleting an object from the database window in
Access 97? Have you noticed how Access handles the cursor movement? If
you haven't, then here's something to keep in mind as you remove
objects in the future.
Let's say that you have a mature application. You have many objects
within each area of the database window. At some point in the future,
you decide to go through the application to see if there are any
objects you can delete—perhaps a few test queries made while
researching a problem or a report that's no longer used.
Since time is always of the essence, you want to delete these
objects quickly. You remember that old Windows trick of pressing
[Ctrl] while using the mouse to select multiple things at once—but
that doesn't work. You also try pressing [Shift] while clicking the
mouse—but that doesn't work either. So you resign yourself to deleting
each object one by one.
You scroll down to the first query you want to delete. As it turns
out, this is the first of three that are listed one right after
another. You press the [Delete] key, Access asks for confirmation, you
press [Enter], and you're ready to go into autopilot. You assume that
Access has moved down to the next object in the list so you can
rapidly press [Delete], [Enter], [Delete], [Enter], and so on. But
beware: There are no guarantees that the cursor has moved to the next
object!
While we haven't been able to figure out the "rule" by which the
cursor moves after you delete an object, this warning still stands. Be
careful as you delete objects from the database window—because you
never know where the cursor will end up.
Parameter queries and the .Execute method: A bad combination
Automating your application is a very broad phrase. Many times the
evolution begins when you become tired of "manually" changing queries.
You know there has to be a better way, and you discover parameter
queries. By including a square bracketed statement in the Criteria row
of a query, you can be prompted for a value and then have it passed
into the query "automatically."
But then you realize that data validation, error checking, and user
feedback are difficult when only using a parameter query. At that
point you discover that you can use a form as a well-behaved "front
end" to all your processing needs.
But even after you've had this epiphany (and it is a good epiphany
to have), Access may trip you up once in a while. One such example is
trying to use the .Execute method on a parameter action query.
Let's say that you want to update the SendDate field in a table
based on a date on a form. You create an update query and enter the
following in the Update To row for that field.
Forms!myForm!myDateTextBox
As long as you have the form open and you have a date in that text
box, the next time you run that query, the date will be populated by
the value on the form. But wouldn't it be neat to run the query from a
button on that same form?
So you create a button and type the following in the OnClick event
designed to run the update query you just created—the query that
worked fine when you ran it from the database window.
CurrentDb.Execute "myUpdateQuery"
But for some reason, Access can't handle it. However, it will work
if you use the OpenQuery action instead, as shown below.
DoCmd.OpenQuery "update"
If you want to get rid of the query object itself, you can do the
whole thing in code to create a much more professional, and
controllable, environment. Here's an example of what the OnClick
subroutine would look like.
Private Sub cmdUpdateDate_Click()
On Error GoTo Err_cmdUpdateDate_Click
' Define variable.
Dim strSQL As String
' Set variable to the SQL string that was copied from the SQL view
of the
' original query along with the date value from this form.
strSQL = "UPDATE tblRounding " & _
"SET tblRounding.tDate = #" & _
[Forms]![frmTest]![dteDate] & "#"
' Execute the SQL statement.
CurrentDb.Execute strSQL
' Let the user know what just happened.
MsgBox "Updated date to " & Me!dteDate, vbInformation, "Status"
Exit_cmdUpdateDate_Click:
Exit Sub
Err_cmdUpdateDate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateDate_Click
End Sub
Regardless of what naming convention you use or don't use, you'll
eventually work on a piece of functionality that will incorporate many
different objects. Let's say you create a new Admin screen that uses
two forms, a few queries, and a code module. Regardless of the
example, over time these "groups" become more and more difficult to
track in terms of what makes up this functionality.
In Access 97, you can get creative with each object's Description
property to help you solve this problem. To see the Description
property, all you have to do is right-click the object and select
Properties.
If you create a little code, or prefix, such as "Admin" or "ADM,"
you can visually see all the objects that are used for this particular
aspect of the application by sorting on the Description property in
Detail view in the database window.
To help you organize your objects even more, Access 2000 introduces
a new concept called Groups. It appears at the left of the database
window and resembles the Favorites folder structure you've seen in My
Computer and Internet Explorer.
To create a new group, follow these steps:
1. Right-click the area beneath the Groups bar.
2. Select New Group.
3. Type the name of your new group and click OK.
Now you can use the new group to organize your objects. To place an
object in the group, right-click the object you wish to add, choose
Add To Group from the shortcut menu, and then select the group.
A shortcut is then placed in the group, which points you back to
the original object. This way you can easily see all the objects that
go with a particular part of the application. And if an object is used
in multiple places, you can put multiple shortcuts in the appropriate
groups as needed.
Access makes the task of creating queries upon queries very easy.
All you have to do is create your first query and save it. You can
then make a new query using the first one as its source.
When you're using these configurations of nested, or sub, queries,
you need to be aware of when Access requeries open objects. If you're
not attentive to this, you may be seeing "stale" data that could lead
you to make an incorrect decision.
Let's say that you make a query on a table with sales data. To be
fancy, you group by SalesRep and then Sum all the person's sales in a
Totals query. While you're reviewing this query, you decide you also
want to see the sum of all sales—regardless of the sales rep. So you
save the query and then create a new query on top of it. You remove
the Group By sales rep so you only have the Sum of sales.
But as you're working with the data, other users are updating the
sales data. Assuming that your first and second queries are both open
in Datasheet view, the first one will be updating somewhat dynamically
(the speed depends on the
Refresh Interval setting in the Tools | Options | Advanced dialog
box)—but the second query will not. It's based on a picture taken of
the query the last time that it was saved. So how can you be sure that
your information is 100 percent, up-to-the-minute accurate? You need
to requery it.
Simply press [Shift][F9] from the keyboard, and you'll requery the
object that has focus. If you want to requery both queries, you'll
have to select each one and use the keyboard combination for each.
Many people are unaware that there's a Source property and a Name
property for any control that displays data. Knowing about one without
the other can be a dangerous combination when you're trying to figure
out why something isn't working.
When you first place a field from the field list on a form or
report, the default name of the control is the field name. If this is
a text box control and you look at the ControlSource property or the
Name property, you'll see the field name in both spots. The same holds
true for combo boxes and list boxes. Let's fast-forward a few days:
You've decided to make some changes on the form. You realize that you
put the field cNickname on the form when you meant to select cLastName.
No problem: You select the control, view the properties of the text
box, and quickly change the ControlSource property from cNickname to
cLastName.
Since there's also a label control associated with this text box,
you change the Caption property for that control so that it now reads
Last Name instead of Nickname. You switch to Form view to look at the
finished product and it looks great.
But there's one problem. You forgot to change the control Name
properties when you changed the ControlSource and Caption ones.
Here's what will happen: Sometime in the future you, or one of your
colleagues, will be working on this form, and they'll get confused
when they see cNickname in a controls list—when in fact, it's actually
cLastName-based controls!
So what's the lesson here?
If you change your ControlSource (or Caption if it's a label), also
change the Name property so you don't create aproblem later for
yourself or someone else. Good naming conventions will go a long way
to keeping your database maintainable and extensible.
The word "Null" is often thrown around somewhat loosely. To the
non-database geek, it means the equivalent of "nothing." To a computer
person, it means "valueless"—similar to nothing, but not exactly.
When you're using a query to find fields that don't have
information in them, you would usually place "Is Null" in the Criteria
row for that particular field. This would work for a text field, a
date field, a Boolean field, or a numeric field.
But when you transition into using VBA code, you have to be more
aware of the data type you're working with before you check to see if
it has "something in it."
For example, let's say you define a string type variable called
strRepName and then make strRepName equal to a control on your form
called Me!SalesRepName. What happens if you forget to put something in
the control on the form?
Your first line of defense would be to check for that control in
the code. You could use the following If...Then test.
' Test form control
If IsNull(Me!SalesRepName) Then
MsgBox "Nothing on form"
Else
strRepName = Me!SalesRepName
End If
Let's say that somewhere else in your code you need to check the
value of the string variable strRepName. If you used
the test above, you wouldn't get accurate results. A string
variable can't store a Null value. The closest thing that
it can store is a zero length string—or "".
One way to check for that is to use the length (Len) function as in
the example below.
' Test string variable
If Len(strRepName) > 0 Then
MsgBox "RepName is Not Null"
ElseIf Len(strRepName) = 0 Then
MsgBox "No data in variable"
End If
Here's the lesson: Be aware of what type of data you're working
with and then use the appropriate test based on that data type. Use
the ListWidth property to show more in a combo box
When working with controls on a form or a report, most people are
aware of the Width property, which sets the dimension for the control.
But if you're working with combo boxes on a form, there's another
property that you need to be aware of—the ListWidth property.
Combo boxes are great because they're very flexible to work with.
This flexibility is also evident in the formatting capabilities of the
control.
Let's say you're working on a form, and you have a limited amount
of space. In one part of the form you have a combo box with a width of
1.5 inches, and you have other controls on either side.
As you're looking at the data that displays, you realize that you
need one more field in the combo box. So you add the field to the
RowSource of the combo box. Then you realize you can't move the
controls around enough to see this new value in the combo box when
it's displayed.
What can you do? Change the ListWidth property to something wider.
Assuming you have another two inches between the right edge of the
combo box and the right edge of your form, you could set the ListWidth
to be up to 3.5 inches. While the combo box is dormant, it takes up
1.5 inches of space—but when you expose the drop-down list, it expands
to 3.5 inches.
Charlie Brown was always getting berated by Lucy for his "round
head." Unfortunately, the abuse doesn't stop with Lucy—Access
continues with the tradition of causing heartache over rounding.
Most of us first experience rounding in Excel, which is very well
behaved when it rounds numbers. Some might even say that it's "pretty
smart" in the way it handles it. Access, on the other hand, is a bit
more complex in how it approaches rounding, so our results are not
always so clean. Let's look at an example.
Let's say that you have a numeric field in a table that's a
"double" format. (We'll name it dblAmount). This means that it can
store positive and negative numbers, along with 15 decimal places of
precision. Here are our test values:
100.248299
49.4544
100.2544
50.5099
If we create a query on top of this table and format the column in
the query to use two decimal places, here's what we'll see.
100.25
49.45
100.25
50.51
Let's assume you used this query as the source for a report. If you
put in =Sum([dblAmount]), what number would be displayed? You would
assume it would be $300.46, right? Not exactly.
Access actually adds up the unformatted, unrounded numbers behind
the scenes. The number that Access uses in the totaling control is
$300.466999—which when rounded becomes $300.47. How can you work
around this?
What you need to do is round first and add second. Then you'll have
consistent results. In the query, place the following in the Field
cell:
RoundedValue: Format([dblAmount],"$0.00")
When you use Format, it formats and rounds at the same time. The
problem is that it converts the value to a string. This means we can't
directly add it. So, to add the rounded values, let's use the
following line for the ControlSource property of a text box control in
the report footer of the report.
=Sum(CCur([RoundedValue]))
This will convert the rounded "string" back to a number and then
add the rounded values—giving us $300.46.
In this three-part series, we're looking at the essentials of using
linked tables and ODBC to connect Access on your desktop with backend
databases. Last time we talked about the basics of linking. Now, let's
link up our tables!
Making the link
To link an Access table with a table in a backend database system,
you first need an ODBC connection to the database. It's important that
you have ODBC already installed and configured on your system before
beginning this process. Entire books have been written on this topic,
and setting up an ODBC connection is too complex a procedure to
explain here. You'll need an account and password for your server
database, so the person who helps you should also be able to help you
create an ODBC connection if you need assistance. Once you've jumped
the ODBC hurdle, you can link to a table in your server database and
start sharing data!
Follow these steps:
1. In Access select File | Get External Data | Link Tables.
2. In the resulting dialog box, you're asked to select the database
that contains the table you want to link to. Select ODBC Databases
from the Files Of Type drop-down list—you'll usually find it at the
bottom of the list.
3. Access next prompts you to select an ODBC connection via the
Select Data Source dialog box. Depending on our OS and the version of
ODBC you have installed, this dialog will vary somewhat in appearance.
But it's important to note the tabs at the top. ODBC data sources can
be user-specific or available to any user of the computer. You'll have
to select the appropriate tab to see the data source you created.
4. Locate the connection you created and click OK. At this point
you may be prompted for your username and password. (In some cases
this info is stored along with ODBC connection information, so you
won't need to provide it.)
5. Select the table or tables you want to use from the Link Tables
dialog box. Access will create a linked table for each table you
select.
That's it. Once you've created a linked table, open it and work
with the data much as you would a regular Access table.
Of course, there are always exceptions, and we'll discuss a few of
those next time.
In this three-part series, we've been looking at the essentials of
using linked tables and ODBC to connect Access on your desktop with
backend databases. We've talked about the basics of the process, and
we've discussed how to link tables with backend databases. But as we
mentioned last time, there are some exceptions to working with the
data of a linked table.
Know your limitations
One of the biggest limitations of linked tables is that you can't
alter the structure of a linked table. Altering the structure means
that you are also altering the structure of the backend table you're
linked to. And altering the structure of a table in an Oracle or SQL
Server database is not a trivial matter. Issues of referential
integrity are taken very seriously in these worlds, so even if you
have the necessary permissions, very few database administrators (DBAs)
would be comfortable with a desktop tool that allowed you to change
the structure of a table as easily as Access does. Bottom line: You
can't directly alter the structure of a linked table.
Most of the other problems you'll encounter when working with
linked tables have to do with running into the many constraints (i.e.,
data validation rules) that are typically placed on large database
systems.
For example, when a column (or field) should store only a specific
set of values, a second table is created containing the allowable
values. The first column is then constrained to allow only values
found in the second table (for example, job titles). If you enter a
value not found in the lookup table, you typically get a rather
obscure ODBC error message about a "failed constraint."
Therefore, it's a good idea to develop an understanding of the data
model so you can try to figure out the problem on your own. You may
also want to develop a relationship with your DBA, so you can call him
or her and ask without getting your database access cut off!
Another issue you'll confront is how to obtain sequence numbers to
be used as the primary keys for the new records you insert into a
linked table. That's right: You can't just set the data type to
AutoNumber as you would for a native Access table.
Many server databases use something called sequences to fill that
role. To insert a row, you have to call the sequence to get the next
available number, which you then use in the primary key column. Bottom
line: If you're going to enter new rows in a linked table, ask your
DBA how to properly obtain primary key values.
You can make a minor enhancement to the standard Access VBA code
within the Switchboard form to help out your users. To quickly review,
a switchboard is a built-in menu system in Access that's form-based.
To create a switchboard, go to Tools | Add-Ins | Switchboard Manager
in Access 97, or choose Tools | Database Utilities | Switchboard
Manager in Access 2000. You can create "pages," along with "items" on
these pages. You can select each item from a predefined list, and you
should find most of the standard items.
One item command that you should put on your default page is called
Exit Application—you can assign your own text for the form to display.
The unfortunate thing is that while it does exit the application (the
.mdb file), it leaves the user in Access with no database open—not a
good scenario. But you can make a minor adjustment so that Exit
Application not only closes the .mdb file but also closes Access.
Follow these steps:
1. Open the Switchboard form in Design view.
2. Select View | Code.
3. In the VB Editor, select Edit | Find.
4. Type Case concmdExitApplication in the Find What text box and
click Find Next.
5. Comment out the line below by placing an apostrophe before the
command so it looks like 'CloseCurrentDatabase.
6. Type the line DoCmd.Quit.
7. Save and close the code window.
8. Save the form and switch to Form view.
Now, when you click Exit Application, Access will also close down.
One of the things that end users find particularly annoying is
being presented with data in a combo box or a list box that's not
applicable for that particular record. For example, let's say that you
have a number of different health insurance carriers available to your
employees. If you have offices in multiple states, you're likely to
have different carriers depending on which state your employee lives
in.
So why would you want to present health insurance carriers that
only service your Massachusetts employees when the employee you've
just entered lives in California? Here's how you can solve this
problem. Let's talk about synchronizing your combo boxes. The user
makes a selection in a State combo box and then sees that the
selections in the Insurance combo box are based on the selected state.
So in our example above, when you click the Insurance combo box, you
would only see those carriers available to employees in California.
Let's set the stage for what we'll need to do to set up these combo
boxes. First we need a table to hold our employee information—let's
call it tblEmployee. We'll also need a table for the health insurance
carrier information. This will serve as our lookup list for all
insurance companies—we'll name that lkupInsurance.
Since this example is predicated on the state where the employee
lives as well the state that the insurance carrier services, we'll
need a state field in each table. We'll name the state field in
tblEmployee eState, and we'll name the state field in lkupInsurance
iState.
It would also be a good idea to create a table for all the states.
By having this table, you can standardize your state abbreviations.
Let's name this table lkupState and just include the field sCode for
the abbreviation.
Stay tuned: Next time we'll talk about creating the two combo boxes
on the form, where we'll see the "syncing" in action.
Last time we set the stage for creating two synchronized combo
boxes on a form, which means that the data in the second combo box is
based on a selection in the first combo box. Using our example, we're
trying to display just those insurance carriers that offer service in
a state where an employee lives. Let's create our form and name it
frmEmployee.
Our first combo box is a list of all the states—we'll name this
cboState, and the RowSource will be lkupState. (This is our one-field
table of all the states we can select from.) Set the ControlSource in
cboState to be equal to eState. You won't need to make this combo box
too wide.
Our second combo box (let's name it cboInsurance) can't be based
simply on our entire lkupInsurance table. We need some way of
"querying" that table so we only display those insurance carriers
where the iState field matches what was selected in the State combo
box. How can we do it?
Like most things in Access, there are a variety of ways to approach
this situation. Here's how to do it with a simple select query.
Create a query against the lkupInsurance table (the lookup list for
all insurance companies). Include the following
fields in this query:
iCode—the unique identifier (primary key) for each insurance
carrier
iName—the name of the carrier
iState—the states that this carrier services for your employees
In the Criteria row under iState, insert the following line:
Forms!frmClient!cboState
Save this query with the name qryInsuranceByState, and close it.
Once you've created the query, use it as the RowSource for
cboInsurance—your Insurance combo box.
The last little trick we need to do is to "force" the Insurance
combo box to update if the State combo box changes. And we'll add a
simple alert to the user. If the state changes, we want to remind
users that they need to update the insurance selection as well. Let's
use the OnChange event of cboState to accomplish this.
Private Sub cboState_Change()
' Check to see if there is already a selection.
If Not IsNull(Me!cboInsurance) Then
MsgBox "Please update your insurance carrier.",
_ vbCritical + vbOKOnly, "State Change"
End If
' Requery the combo.
Me!cboInsurance.Requery
End Sub
By doing this, you're setting the query so its results will be
dependent on the selection in the State combo box. So every time you
change the entry in cboState, your choices in cboInsurance will change
as well. Now your combo boxes are in sync.
Access 2000 allows you to undo all changes made since the last save
to the current form. With the form open in Design view, simply choose
File, Revert, and Access will undo any changes you've made to the form
since the last time you saved it.
If Revert is disabled, no changes have been made since the last
save.
Many Access 2000 users are curious about the new Groups option on
the Places toolbar (in the Database window) and the Favorites folder.
This new feature has several uses, but one of my favorites is hiding
objects the user doesn't need to see. Most applications are full of
tables and queries that the user never opens or interacts with, and
they can create a lot of clutter. Moving these objects to the
Favorites folder in the Groups section can clean things up a bit. To
add an object to the Favorites group, simply drag the object to that
group and drop it.
Once you've moved the object to the Favorites group (actually,
you're just creating a shortcut), hide the object in the Database
window by right-clicking it, choosing Properties, selecting the Hidden
object, clicking Apply, and then clicking OK. Now you still have easy
access to the object, but it won't clutter up the Database window.
Tired of puzzling error messages? If so, download and run
Extralert.exe to install Microsoft Office 2000 Customizable Alerts.
This program will offer to find more information. Visit
http://officeupdate.microsoft.com/2000/downloadDetails/alerts.htm
to download the program. Once you've installed it, Access 2000 will
(sometimes) display a Web Help button along with the normal error
message. Clicking the button will take you to updated information on
Microsoft's site. (Of course, you'll need an Internet connection for
this feature to work.)
Last month, we warned you about Access 2000's missing wizards.
Sometimes they're there and sometimes they're not. It's a confirmed
problem and you can learn more about it at
http://support.microsoft.com/support/kb/articles/Q242/2/18.asp
Here is a method for reinstalling your wizards if the Small
Business Tools have been installed. (If the Small Business Tools
haven't been installed, reinstalling the wizards may not work.) First,
close Access and insert your Office 2000 CD. Then, choose Settings
from the Windows Start menu, and select Control Panel. Double-click
Add/Remove Programs, select Office 2000, and click Add/Remove
Programs. Choose Add Or Remove Features and then click the plus sign
to the left of the Microsoft Access heading. At this point, click the
Additional Wizards heading and choose Run All From My Computer.
Finally, click Update Now, and when that's complete, you should be
able to launch Access and find your wizards at home.
A few tips ago, we showed you a short procedure that applies print
formats depending on a condition. In that procedure, we used the
following statement to check a control for a True value:
If Me!chkName Then
This conditional statement states an implicit form of the True
value, and it evaluates differently than the explicit form.
Specifically, as long as chkName's entry is any value other than 0,
this statement will evaluate to True. On theother hand, when expressed
explicitly in the form
If var = True Then
var must evaluate to -1 in order for the condition to return True.
Any value other than -1 will evaluate to False.
Keep this behavior in mind when depending on the implicit True
value--the results might not be what you expected.
Ever print a report that prints blank pages between each page or
every other page? Usually this is an indication that the report is
wider than the selected page size. This can happen if a control
extends past the margin. When positioning and resizing controls, don't
forget about that right margin. For instance, if you're working with a
typical 8-inch by 11-inch sheet of paper in a portrait report and both
the left and right margins are one inch, you have only six
inches--from left to right--to work within. That means all of your
controls must be to the left of the 6-inch ruler mark. If a control
extends past that mark, Access will print an extra blank page for each
page of your report. Fortunately, this problem is usually easy to
resolve. Review the page size and margins and then check all the
controls at the right edge of your report. Resize or reposition any
controls that extend past the report's right border.
By default, Access will open an object if you double-click it in
the Database window. A single click simply highlights the object.
After selecting an object with a single click, you can click the Open
button on the Database window toolbar to open that object or
double-click it. You can change this behavior by choosing Tools,
Options, then clicking the View tab. The Click option in Database
window has two options: Single-click Open or Double-click Open. If you
prefer to open objects with just a single click, choose the
Single-click Open option. Once you do, resting the mouse pointer on an
object will highlight (select) that object and a single click will
open it.
Finding typos when you know you ran the Spell feature? It's
possible because, by default, Access will ignore words that are in
uppercase letters or that contain numbers. If this is causing you a
problem, you can modify the spelling option. To do so, run the Spell
feature on data you know will produce the error. When Access displays
the Spelling dialog box, click the Options button. Clear the Words In
UPPERCASE option in the Ignore section. Then, clear the Words With
Numbers option in the Ignore section. Finally, click OK and then click
Cancel. Of course, you don't have to disable both options--you can
select one or the other, or both.
Controlling the data your users enter can help eliminate data entry
errors. For instance, if all phone numbers must have an area code, you
can use an input mask to return an error if the user omits that
portion of the phone number. The easiest way to apply an input mask is
to use the Input Mask Wizard--simply click the Builder button to the
right of the Input Mask property field in Design View.
All input masks have three sections. The first section defines the
actual mask, which can consist of literal characters and placeholders.
The second section--either a 0 or a 1--is often misunderstood and
certainly underused. A value of 0 will store any literal characters in
the mask with the value the user enters. For instance, a mask may
include a hyphen between the three sections of a social security
number. If the input mask's second component is 0, Access will store
those hyphens with the social security number. (You probably wouldn't
really want to do this.) The last section defines the character the
mask displays as you're entering data--the default character is the
underscore character. If you want to display a space character
instead, you'd enter the string " ".
Ever start to create a new field and notice that some fields
already contain the value 0? These are Number or Currency fields,
which automatically default to 0. There's nothing to worry about--just
enter a value as you normally would, or leave it blank if that's
appropriate. Just keep in mind that a Number or Currency field can't
be Null (it doesn't make sense), so Access is protecting you from a
potential error by making sure the field contains some kind of value.
The Filter By Form feature is powerful and still a bit
misunderstood. After you click the Filter By Form button on the Form
View toolbar, Access will display your search form, which closely
resembles your actual form. Now, here's where a lot of people make
their mistake--don't ignore the And and Or tabs at the bottom of the
form. Each criterion you enter in the And tab will be combined by the
And operator. As a result, the search will return only those records
that match all the criteria, and this may or may not be what you want.
When a record can meet more than one criteria, but only needs to meet
one of the criteria, enter criteria in the And tab and the Or tab.
That way, Access will return records that meet either condition.
Lots of people trim imported data because it often drags along
unwanted space characters. Before committing data to a field in your
table, run it by one of the three trimming functions:
Trim: Removes both leading and trailing spaces
RTrim: Removes trailing spaces
LTrim: Removes leading spaces
Queries accept expressions--in fact, calculated fields are common
in queries. For instance, you might enter the expression Total:
Quantity * Price to return the total cost of an item. When you enter
expressions, Access doesn't care a fig about capitalization. You can
enter lowercase, uppercase, use proper case, or even a combination of
all three, and it won't make a bit of difference to Access. But don't
even think about misspelling a field name. Misspelling a field name
will cause Access to display a parameter prompt when you run your
query.
You can easily rename an Access database file without launching the
Windows Explorer, but not while the database file is open. When you
need to rename a database file, follow these simple steps. First close
the file. Next, select File, Open Database. Locate the file in the
resulting Open dialog box and right-click it. Select Rename from the
pop-up menu, and type the name you want to give the database file.
Finally, press Enter. One word of caution when using this method:
Watch for linked objects. Access won't automatically update links.
After renaming a database, open it and relink your objects by choosing
Tools, Add-Ins, Linked Table Manager to update any links.
You probably know about the Relationships window and even use it to
create new relationships and modify existing ones. Once you've defined
a relationship, Access will use that relationship if you create a new
query. However, Access won't update existing queries to reflect your
changes or additions. You'll have to open any existing queries that
should be affected by your changes and create the relationship(s)
yourself.
We use queries to limit data, but sometimes we also depend on
queries to update the underlying data source. For instance, it's
common to base a form on a query. If the form allows changes, the
query will modify the underlying table. Occasionally, queries won't
allow updates, and when this happens you should check the query's
recordset type. To do so, open the query in Design view and display
the query's property sheet by clicking the Properties button on the
Query Design toolbar. Make sure the Recordset Type property is Dynaset
or Dynaset (Inconsistent Updates). Dynaset is the most flexible
setting, but Dynaset (Inconsistent Updates) is also adequate for most
updateable forms. The Snapshot setting doesn't allow updates at all.
Sometimes, even specifying the Dynaset option isn't enough. If the
query is based on a one-to-many relationship, you can edit only the
one side of the relationship. You can update both sides by updating
the cascade options in your relationship. To do so, click the
Relationships button on the Database toolbar and double-click the line
that connects the two tables. In the Relationships dialog box, click
the Cascade Update Related Fields option and click OK. If you select
this option, Access will update foreign key values in the many table
when you update a primary key value in the one table.
Printing a table is easy enough, but occasionally you may need to
limit the records you print. When this is the case, simply select the
records you want to print by clicking the appropriate row headings
(the gray cells to the left of each record in Datasheet view). Then,
choose File, Print. In the Print dialog box, select the Print Select
Record(s) option in the Print Range section. After setting any other
print options you might need, click OK. When selecting those
records, you can hold down the Shift key to select contiguous sections
of records. Select the first record, then hold down the Shift key and
click the last record in the section.
You probably know how to create shortcuts to files, but did you
realize you could create a shortcut to an Access object? Access
objects are stored together in the same MDB file. That means you must
launch Access and open the database to work with an Access
object--say, view a report. If you routinely work with the same
object, a shortcut may be just what you need. To create a shortcut to
an Access object, right-click its icon in the Access Database window
and choose Create Shortcut. Access will offer to save your shortcut on
the Windows desktop. You can go with this option, or click the Browse
button to find a new location.
Can you set a combo box to allow multiple selections the same way
you can a list box. The answer is no, and when you think about the
differences between the two controls, that makes sense. When you make
a selection in the drop-down list of a combo box, Access copies the
selected item to the text box portion of the control. There's really
no way to copy multiple items to the text box.
If you have a combo box and you want it to act like a list box,
right-click the combo box in Design View, choose Change To from the
context list, and select List Box. Then, you can set the new list box
control's Multi Select property to either Single or Extended to allow
multiple selections in the drop-down list.
Most of the time, the field order (in a table) isn't too important.
However, if you decide to change the order of fields, you must open
the table in Design View and rearrange the field rows. On the other
hand, you can move fields around in Datasheet View, and even if you
save the new layout, Access won't modify the order of those fields in
Design View.
To rearrange fields (columns) in Datasheet View, select the column
you want to move by clicking its heading cell (the gray cell at the
top of the column). Then, click the cell again, but don't release the
mouse. Drag the column to its new position, and then release the
mouse. You'll notice that Access highlights the left border of each
column as you pass each one. Access will drop the column to the right
of the highlighted border.
A while back, we recommended you trim imported data using Trim,
LTrim, or RTrim before committing that data to a field. Imported
data isn't the only data that might contain unnecessary space
characters. Sometimes people enter space characters during the data
entry process without even realizing it. For instance, a data entry
operator might rest his or her thumb a bit too heavily on the
spacebar. For this reason, you might want to trim string entries by
attaching Trim, RTrim, or LTrim to each control's update event.
When working in Design View, you probably open the Properties
window to modify object properties. Once you modify a property, do you
close the Properties window and then reopen it again if you need it?
If so, you don't need to. The Properties window is modeless, which
means you can access other objects while it's open. In fact, leaving
the window open is very convenient, because it displays the properties
of the selected object. That means you can jump from one object to
another without having to update the Properties window.
If you enter data in Datasheet View (instead of through a form),
you probably find existing records a nuisance. That's because you have
to go hunting for the new record (the blank record at the bottom of
all the records), and if there are a lot of records, that could take a
while. You could always select Insert, New Record, but even that's
unnecessary.
When viewing the existing records isn't important, choose Records,
Data Entry. Access will hide all the existing records, so all that's
visible is a new record (blank record).
Queries allow you to exclude records that meet certain criteria.
You simply include the Not operator. Did you know you could do almost
the same thing in a filter? Perhaps the easiest way to go about this
is to select a portion of an entry or an entire cell that represents
the value you want to exclude. Then, choose Records, Filter, Filter
Excluding Selection. Access will hide any records that contain the
data you selected before running the filter.
It's easy to print a table. Simply open the table in Datasheet view
and click the Print button. However, sometimes you'll want to exclude
some data from printed copies. Fortunately, you can hide the fields
you want to exclude before printing. To do so, follow these simple
steps.
Begin by selecting the field(s) you want to exclude by clicking the
field's header cell (the gray cell at the top of the field). Then,
choose Format, Hide Columns.
Once you've hidden all the fields you want to exclude from your
printout, simply click the Print button. Or click Print Preview to
check the results before you print. That's all there is to it! Access
will print only the visible fields.
Later, you can unhide your columns by choosing Format, Unhide
Columns and selecting the fields you want to unhide. Or close your
table without saving the changes.
You can learn a lot about objects in the Database window--more than
just the object's name. For instance, you can see a description of the
object, the date the object was last modified, the date and time the
object was created, and the object's type. To learn more about each
object in your database, in the Database window select View, Details.
Or simply click the Details button on the Database Window toolbar.
In our previous tip, we explained how to display helpful
information about each object in your database. One of those tidbits
of information is a description. However, if you clicked the Details
button on the Database Window toolbar, you may have found many or even
all of your objects lack a description. To add a description for any
database object, first right-click the object in the Database window
and choose Properties from the context menu. Enter descriptive text in
the Description control, and then click OK to return to the Database
window. Access will display the descriptive text in the Description
column when details are displayed.
There are a couple of ways you can delete a record in Datasheet
View. Perhaps the easiest way is to select any cell in the record you
want to delete and click the Delete button on the Table Datasheet
menu. Clicking the row cell to the left of the record and then
pressing the Delete key will remove the current record.
You can't combine the two methods. If you position the cursor in a
cell within the record you want to delete and press the Delete key,
Access won't respond. If you select the entire cell, Access will
delete the contents of that cell. However, neither method will delete
the record.
When assigning the Number data type to a field, you may have
trouble deciding which Field Size property to use--there are several.
You can use the following guidelines to help when this happens:
Byte: This type requires the least memory and is also the most
limiting because you can enter only integer values from 0 to 255.
Integer: Stores integer values, both positive and negative, between
-32,768 and 32,767.
Long Integer: Stores integer values, both positive and negative,
inclusive of -2,147,483,648 through 2,147,483,647.
Single: Stores values with decimal components (fractions) between
-3.402823E38 and -1.401298E-45 for negative values and 1.401298E-45
and 3.402823E38 for positive values (four or fewer decimal places).
Double: Stores values with decimal components between
-1.79769313486232E308 and -4.94065645841247E-324 for negative values
and 4.94065645841247E-324 and 1.79769313486232E308 for positive
values.
Replication ID: This one most of us won't use because it accepts GUIDs
(globally unique identifiers).
Decimal: Stores values from -10^28 - 1 through 10^28 - 1.
Decimal is new to Access 2000.
Entering a date can require a lot of keystrokes by the time you
enter the month, the day, and the year. However, there's a shortcut
you can take advantage of--skip the year component by using the form
m/d
where m equals the month integer and d equals the day. This
shortcut applies only when you want the date belonging to the current
year. For instance, if the current year is 2000 and you enter 11/27,
Access will store the date November 27, 2000.
Conditional formats are new to Access 2000, and they're easy to
work with (we've had a few tips on the feature in the past). What you
might not realize is that you can quickly copy conditional formats
from one control to another using Format Painter. After assigning the
conditional format to a control, select that control and click Format
Painter on the Form Design toolbar. Then, select the control you want
to copy the conditional formatting to--it's that simple.
The imported data included a field of Julian dates, which he needed
to convert to dates that Access could interpret correctly.
Julian dates imported as Numeric fields convert easily to regular
dates. Simply change the field's Number data type to Date/Time.
Julian dates imported as Text fields aren't as easy to work with. If
you can get the Import Wizard to change the data type of the field
from Text to Number, you solve your problem early, but don't count on
the Import Wizard being so generous. If you must import the data as
text, then use the Val() function in a query expression in the form
ConvertedJulianDate: Format(Val([JulianDateText]),"formatcode")
You can use the CDate() function to convert the date. To do so, use
the form
CDate("JulianDateString")
If you apply a conditional format to a calculated control, you
might notice some unexpected results. The most likely culprit is the
condition you've chosen. When you're working with calculated controls,
it's very important that you remember to select the Expression Is
condition instead of using the default Field Value Is condition. After
specifying the Expression Is condition, be sure to enter the
expression itself (excluding the equal sign).
If you use a lot of graphic images in your work, you might want to
take a look at Clip Gallery Live, a Microsoft site that offers more
than 100,000 graphic images and sounds, including clip art,
photographs, and Web animations. You can use these clips in your
Office documents--just be sure to read the licensing data carefully
regarding redistribution and usage. To visit this site, point your
browser to
http://cgl.microsoft.com/clipgallerylive/default.asp
Before printing reports, you may need to offer your users the
opportunity to set print options. If so, you could solicit information
through several of input boxes, but why not just display the Print
dialog box and let them set those options themselves? If this sounds
like a good idea to you, try the procedure shown below to display the
Print dialog box. The users can choose from default printers, change
the default print range, specify the number of printed copies, and so
on.
Public Function PrintOut()
On Error GoTo ErrorTrap
DoCmd.RunCommand acCmdPrint
Exit Function
ErrorTrap:
If Err.Number = 2501 Then
Exit Function
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function
Many of us use calculated controls on reports to summarize data--we
generally put these controls in a section's header or footer. You can
add a calculated control to any section of a report. Just don't forget
the equal sign at the beginning of the expression--without that equal
sign, Access will try to refer to a procedure or field and return an
error when it doesn't find one.
Sometimes, calculated controls can slow down a report. When this
happens, consider basing your report on a query and adding the
expression to your query instead of trying to calculate the results in
your report. This isn't always possible, but chances are, if your
calculated control isn't in a header or footer you can rearrange your
report in this manner.
If you always use the Simple Query Wizard, you're not realizing the
full power of an Access query. Besides selecting a specific set of
records, a query can return the results of expressions for each
record. For instance, you might want to return the total cost of a
particular item. You'd create a SELECT query and include what's known
as a calculated field.
Instead of dragging a field to the grid, you'd enter an expression.
In this case, the expression in your calculated field might resemble
Total: [Quantity] * [UnitPrice]
When you run the query, the above expression would return the cost
for the total number of items purchased.
Before, we talked about those 0s that appear out of nowhere in a
new record. Access automatically enters a 0 in a Number or Currency
field when you create a new record. That way, your Number or Currency
field is never null. If you're working with some other data types, you
can use the Default Value or Required property to ensure a field
doesn't remain null.
The Default Value property enters a value automatically for all new
records.
The Required property won't allow you to leave that field blank (or
null).
If you change a table's structure, you have to save it manually.
However, when you're entering records, you don't have to do a
thing--Access saves the new data automatically when you move to a new
row. If you'd like to save the current record without moving to a new
record, you can force Access to do so. Simply press Shift-Enter, and
Access will save any changes you've made to the current row.
Eliminating old data is a fairly common task. However, most of the
time you'll want to archive that data, not delete it. Fortunately,
doing so is a simple process. First, run a Make Table query using
criteria that selects the data you want to remove. Once you've
completed that task, create a new query using the same criteria. This
time, make the query a delete query to remove the records you just
saved to a new table from your active table.
To specify a Make Table or Delete query, simply create a Select
query as you normally would. Then, select the appropriate Action query
from the Query menu. Before running any Action query, you should
review the results of the query as a Select query. That way, if your
query isn't exactly right, you haven't wrecked your data. You might
want to create a copy of the table you're modifying, just in case you
do make a mistake.
The Access QueryDef object is a useful tool for automating queries,
but a QueryDef object stores a query definition--not the results. The
results are held in a Recordset object.
When you want to save a QueryDef, you give it a name when you first
create it. For instance, the following code declares and then sets a
QueryDef object, which we'll save as "qryExample":
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qryExample")
When creating QueryDef objects, be careful about the names you give
them. If you try to create a QueryDef object using a name already
given to an existing query, Access will return a run-time error.
You work a lot with field names, so they deserve your attention.
Fortunately, naming fields is rather simple, as long as you stick to a
few basic rules:
Give the field a descriptive name--something that describes its
purpose.
A field name can consist of up to 64 characters, including letters,
numbers, spaces, and many special characters.
A field name can't contain a period (.), an exclamation point (!), an
accent grave (`), or either bracket ([ or ]).
A field name can contain a space character, but it can't be the first
character.
You're probably familiar with the navigational toolbar that Access
displays at the bottom of most forms and tables. There are five
buttons in all, and they're all fairly self-explanatory. However, on
occasion you might see that the New Record button is dimmed. This is
the last button on the right, and it displays a new blank record in
your form. When this button is dimmed, it means that you can't access
a new record. Developers inhibit this feature when you need to view
existing data but they don't want you to enter new data.
Most Access objects have a Description property, which you can use
to identify the object's purpose or other pertinent information. For
instance, you may have a table or form that you never seem to use. In
this situation, if the object also has a escription property you
can learn more about it and perhaps why you never seem to use it.
Another example: During the development process, you might create
objects that you don't end up using. Usually, most of us hang onto
those objects until the project is almost complete--just in case. The
Description property comes in handy at this time, because you can
enter the phrase Don't delete, but not currently using or some
other explanation. That way, you won't accidentally delete something
until you know you're really done with it. Should you put the object
back into use, just remember to update the Description property
accordingly. To access the Description property, right-click the
object in the Database window and choose Properties from the context
menu. You'll find the Description property on the General page.
You probably know that you can undo changes in the current field
while editing records in Datasheet View, but there's a bit more:
When editing a record in Datasheet View, you can press the Esc key
to undo any changes you've made to the current field.
If you decide to undo all changes you've made to the current record,
simply press the Esc key twice. You must take advantage of this
keyboard shortcut before you begin editing another record.
Most Access users are familiar with lookup fields. A lookup field
is similar to a combo box, only at the table level. If you use them
frequently, you may have noticed that Access doesn't sort the list,
which can be a bit of a nuisance if you're used to scanning an ordered
list.
Fortunately, the problem is simple to solve. First, open the table
with the lookup field in Design view and select the lookup field in
the upper pane. Then, click the Lookup tab, select the Row Source
property, and click the Builder button to launch it. (The Builder is
the little button with the three ellipses to the right of the field.)
The Builder launches a window that resembles the query design
grid--The SQL Statement: Query Builder. Locate the field that contains
the list items you want to sort and choose the appropriate order from
that cell's Sort cell. Your choices are the same as if you were
working in an ordinary query: Ascending, Descending, and Not Sorted.
After identifying a sort order, close the window and save your
changes when prompted. The next time you open the table's lookup list,
the items will appear in the specified sort order.
You probably know that you can link to tables in other databases.
Did you know you could link to an Excel spreadsheet? You can--as long
as the data is organized in database format (columns as fields). Once
you link to an Excel spreadsheet, any changes you make to the data in
Access will also be made to the actual Excel spreadsheet, so be
careful when you use this feature. You'll recognize a linked table or
spreadsheet by the black arrow and the icon that indicates the type of
application for the original data (in the Database window).
To link an Excel spreadsheet, begin by selecting File, Get External
Data, Link Tables. In the Link dialog box, browse to the appropriate
folder and be sure to update the Files Of Type control to Microsoft
Excel (*.xls). Next, select the spreadsheet file you want to link, and
then click the Link button. Doing so will launch the Link Spreadsheet
Wizard.
From here, simply respond to the wizard's questions.
You can alter a table's design or a field's properties only by
opening the table in question in Design view. Once in Design view, you
must identify the field and then jump to the Field Properties pane by
clicking one of the field properties. You can also access the Field
Properties pane by pressing F6. If you prefer keystrokes to mouse
clicks, you'll probably find this alternative a helpful timesaver.
When you're working in Datasheet view (with a table), you can
quickly access the first field in the first row (or the last field in
the last row) by using the following keyboard shortcuts:
Ctrl-Home: Selects the first field in the first record.
Ctrl-End: Selects the last field in the last record.
What you might not realize is that these keystrokes will work the
same way when you are working in Form view. Regardless of the current
record, Ctrl-Home displays the first record, and Ctrl-End displays the
last record in the underlying data source.
In our previous tip, we explained why the New Record button on the
navigational toolbar is sometimes dimmed. It means that you can't
enter new records in the current form. If you'd like to inhibit this
capability, simply open the form in Design view and choose No from the
form's Allow Additions property. When you return to Form view, you'll
find that the New Record button is dimmed. Now you can view existing
data, but you can't access a blank (new) record to enter new data. You
can click the button, but Access will ignore you and do nothing.
When you enter text in a label control, Access enlarges the control
so you can see all of the content. Later, if you add more text, Access
will automatically increase the size of the label. However, if you
ever delete text, making the label smaller, you'll have to adjust the
size of the label control yourself. You can do so quickly just by
double-clicking any of that control's sizing handles. These handles
are the small, black squares that appear at each corner of the control
when selected in Design View.
Access 2000 has the capability to display related records in
Datasheet view--this capability is known as subdatasheets. When you
open a table, you may see a small plus sign to the left of a record.
When this is the case, you know that record has related data in
another table. Inserting a subdatasheet has an odd side effect. If you
use the AutoForm to create a form based on the table that constitutes
the one side of the relationship between those two related tables, the
wizard will automatically include a subform in your form. This may or
may not be what you want. If you don't want the subform, you can
always open the new form in Design view and simply delete it. Or you
can delete the subdatasheet in the table before you run the wizard. To
delete a subdatasheet, first open the table in Datasheet view. Next,
select Format, Subdatasheet, Remove. Then, save and close the table.
Unlike the AutoForm, the Form wizard ignores subdatasheets.
In our previous tip, we talked about launching the SQL Statement:
Query Builder feature to sort the items in a field's lookup list.
Builders are available in many places; the one we discussed is just
one among many. Whenever you see the Builder button (the small button
with three ellipses), you know a Builder is available. Most people
probably launch a Builder by selecting the property field and then
clicking the button, but there's a quicker way--if you know the
property has a Builder. That's because the Builder button doesn't
appear until you select the property field. However, the temporarily
invisible Builder is still available. If you know the property has a
Builder, just click to the right of the field and Access will launch
the Builder, even though you didn't select the property field first.
Filters are similar to queries in that they select records that
meet specific conditions. However, filters are used primarily to view
a subset of records in a form or datasheet. There are three ways to
filter your records:
There are several ways to report data in Access 2000. You can rely
on the standard report feature. You can also export a report to static
HTML format. New to Access 2000 is the ability to work with report
snapshots and to view those reports in the Snapshot Viewer. (Access 97
needed an add-in to view report snapshots.) Also new to Access 2000 is
the Pages object--a type of interactive Web page.
Here is a brief description to help you decide which reporting
option is appropriate for you:
