TIP #1: Wrapping Text
(All Versions)
Wrapping a field
[Long titles/text]
- Go into Report
Design to make changes - At main database window:
- Select Report
- Click on Design
button - [this takes you into report design screen]
- Click on field to be
wrapped
- Click Properties
Button from toolbar [or right mouse click and select Properties
from dropdown]
Change the
Option: Can grow? [YES]
TIP #2: SET A PARAMETER QUERY
(All Versions)
Getting a prompt at
the beginning of a query to enter conditions and ranges
In Query Design:
- At Criteria area, go to
the field to have prompted - and then within brackets, put in message:
[Enter name of
department here]
- For date ranges
type in:
Between [Enter
beginning date here] AND [Enter end date here]
When Query is run, it
prompts the person to enter the information.
TIP #3: FIND BLANK CELLS IN A FIELD USING CRITERIA
OPTION
(All Versions)
If you want to find
all the blank cells in a particular field in your table, try out the
following:
- Click on Query
Tab
- Click on NEW
- Select Design
View and then OK
- Highlight the table
that you want to use and then click ADD and then Close
- Pull down the fields
that you would like to use in your query
- Click in the
CRITERIA area of the field you want to search for blank cells
- Type in the word
NULL
- Run
the query
Access will pull up all
the records that contain blank cells.
TIP #4: DRAG & DROP AN ACCESS TABLE INTO MS WORD
USING THE TASKBAR
(All Versions)
You can drag an Access
Table into a Word document as easy as this:
- Make sure you're in the
Tables Tab in Access (or Tables Object in versions 2000 - 2003) -
highlight the table that you want to use
- Make sure that you've
got Word running - minimize Word to the Taskbar
- Simply drag the
highlighted Table down on to the Word button - Hold there until Word is
restored. (never letting up on the mouse button)
Once Word is restored,
drag the table up into the Word document and let go. (You will notice
a little piece of paper attached to the mouse pointer - this is your Table)
TIP #5: KEYBOARD SHORTCUTS
(All Versions)
In the Datasheet
view:
Ctrl/'
(apostrophe) to insert a copy of the contents from the cell above.
Ctrl/:
(colon) to insert the current time.
Ctrl/;
(semicolon) to insert the current date.
TIP #6: SPEEDY SELECTIONS
(All Versions)
With a record
selected in the Datasheet view
- Select an entire row by
pressing Shift+Spacebar.
- Select the entire
datasheet, press Ctrl+Shift+Spacebar.
- Select and entire
column, press Ctrl+Spacebar
Note: You must have
a record selected (highlighted) before using these shortcuts.
TIP #7: NUMBERING RECORDS IN A REPORT
(All Versions)
If you want to number
each detail record in a report; this is what you do:
- In report Design view,
add a calculated text box to the 'detail' section [View the Toolbox
toolbar - select the textbox button to draw the box]
Once the text box is in
the detail section of your report:
- Double-click the text
box to display its property sheet.
- Set the Control Source
property to =1.
- Set the Running Sum
property to Over All.
When you preview or print
the report, each record number will be incremented by 1 (1, 2, 3...).
HINT: You can see
an example of numbered detail records, the 'Sales by Year' report in the
Northwind sample database the comes with Access has an example of numbered
detail records. To view this report, you can open the Northwind database in
the Samples subfolder of your Microsoft Access program folder. Once your
database is opened, in the Database window, click the Reports tab, click on
Sales by Year, and then click Design button.
TIP #8: GIVE YOUR ACCESS TABLE A DESCRIPTION
(All Versions)
Right mouse click on a
Table and select Properties.
In the Description
area, you can type information that you may need to remember regarding the
Table. Such as (this table contains SS#'s and DOB's for employees)
Simply click on OK
when you're finished entering your description information.
[NOTE: To display the
description (in addition to your table name), from the menu, select
VIEW/Details (or DETAILS button on Toolbar)
TIP #9:
EXPLODING FIELDS
(All Versions)
When you're editing a
data field, you can get an exploded view of the field by pressing
Shift + F2.
TIP #10: BRING EXCEL DATA INTO ACCESS
(Versions 7.0 & 97)
To bring data from a
Microsoft Excel spreadsheet into Access, first open both programs
and minimize Access.
Highlight the data
in Excel that you want to import into Access. Point to the edge of the
highlighted data, then ctrl/drag to the Taskbar and hold above
the Access icon (DO NOT release the mouse button) until Access opens
up.
Access will automatically
import the data.
TIP #11: SPLIT DATABASE & IT'S QUERIES/REPORTS
(All Versions)
You can split a
database into 2 files - one that contains the tables, and one that contains
the queries, forms, macros, and modules.
This way, users who
need to access the data can customize their own forms, reports, and other
objects while maintaining a single source of data on the network.
For Versions 2000
- 2003
- Make a Backup
copy of your current database
- From the Tools
menu, choose Database Utilities and then Database Splitter
- Follow the
Database Splitter Wizard.
For Earlier
versions
- Go to TOOLS/Add-Ins/Database
Splitter
- Follow the
instructions in the Database Splitter Wizard dialog boxes
TIP #12 -
COMPACTING & REPAIRING CAN IMPROVE PERFORMANCE OF ACCESS DATABASE
(All Versions)
One of the most
important things you can do to improve your database's performance is to
regularly repair and compact it. Compacting a database reorganizes the file
and lets you regain space from deleted records. The Repair option lets you
fix a corrupted database, which occurs in most cases when Access closes
unexpectedly before saving the database. If your database behaves in
strange ways, it may need to be repaired. When you repair a database, you
should also compact it afterwards.
For Versions 2000
- 2003
- Select the
database you want repaired and compacted
- From the
TOOLS/Database Utilities/Compact and Repair Database
For Earlier
versions
- Select the
database you want repaired
-
TOOLS/Database Utilities/Repair Database
- To compact a
database:
- Select the
database you want compacted
-
TOOLS/Database Utilities/Compact Database
TIP #13 - CREATE MULTI-FIELD PRIMARY KEYS
(All Versions)
To quickly
create a multi-field primary key, while in the table's Design
View:
- In table design
view, press and hold
the [Ctrl] key and click each field that you want to make up the
primary key.
- As you select
each field, Access highlights it.
- Next, choose
EDIT/Primary Key from the menu bar, or click the Primary Key Button
on the toolbar.
Access creates the
composite primary key in the blink of an eye.
TIP #14 - USEFUL SHORTCUT KEYS
(All Versions)
Note: Plus
signs indicate 'and', to press two keys together. You do not want to
actually press the + sign on your keyboard. For example, CTRL+P indicates
to press the CTRL key and with it still held down, press the P.
CTRL+; -
Insert today's date
CTRL+: - Insert the current time
CTRL+Enter - Insert a carriage return in a
memo or text field
CTRL+' - Insert the data from the same field
in the previous record
ESC - Undo the changes you have made to the
current field
ESC ESC (press ESC twice) - Undo the changes
you have made to the current record
F11 - Display the database window
CTRL+N - Open a new database
CTRL+O - Open an existing database
ALT+F11 - Switch between the Visual Basic
Editor and the previous active window
CTRL+F - Find and replace
CTRL+C - Copy
CTRL+P - Paste
CTRL+Z - Undo
CTRL+S - Save
CTRL+P - Print
TIP #15 - FIND OUT THE TABLE OR QUERY USED TO CREATE
A REPORT
(All Versions)
- Go into REPORT
DESIGN
- In the upper
left corner of the ruler bar area, right mouse click on the gray
box and select ‘Properties’
<the properties window appears>
- Click the
‘Data’ tab (or the ‘All’ tab)
- Find the
‘Record Source’ line [the first option listed] – it will tell you
what query or table was used –
Note: You
can quickly go to a query if you’d need to make changes by clicking the
[…] button next to that Record Source line. If you’d do this and go
into the query design, when you close out of it, you’ll be back at your
Report Design again.
TIP #16 - FOR A NEW/BLANK LINE IN A MESSAGE BOX (All
Versions)
If you want to
force a new line in a message box, you can include either:
- The Visual
Basic for Applications constant for a carriage return and line feed,
vbCrLf.
-or-
- The character
codes for a carriage return and line feed, Chr(10) & Chr(13).
For example, if you
had the following message
NOTICE: This is an
Important Message!
and you wanted the
message to be displayed as
NOTICE:
This is an Important Message!
you would enter the
message as a string expression as in either of the following examples:
- Example Using
the Visual Basic for Applications Constant:
MsgBox "NOTICE:" &
vbCrLf & "This is an Important Message!"
- Example Using
the Character Codes:
MsgBox "NOTICE:" &
Chr(10) & Chr(13) & "This is an Important Message!"
Note: You
can also use the vbCrLf constant to create multiple lines in a text
box on a form or on a data access page.
TIP # 17 -
DECIDING WHEN TO USE ACCESS VS. EXCEL
(All Versions)
Refer to the
Following General Rules When You Need to Decide Between Access or Excel to
Store Your Data:
Use Access When
You Need:
- A relational
database (multiple tables) to store your data.
- May need to add
more tables in the future to an originally flat or non-relational data
set.
For example, if you want to keep track of customer
information such as first and last names, addresses, and telephone
numbers, but that information may grow to include actions by customers
such as orders, then consider starting your data project in Access.
- Have a very large
amount of data (thousands of entries).
For example, if you work in a large company and are
required to store personnel information, then use Access.
- Have data that is
mostly of the long text string type (not numbers or defined as numbers).
- Rely on multiple
external databases to derive and analyze the data you need.
For example, if you need to import or export data
regularly from Access databases, it may make the most sense to work in
Access to maximize compatibility.
- Need to maintain
constant connectivity to a large external database such as one built with
Microsoft SQL Server.
- Want to run
complex queries.
For example, if you work in a large company that
takes customer orders, you might have to look up customer names stored in
a SQL Server database while taking new customer orders in Access. You can
maintain a connection to the SQL Server database from within the Table
view in Access. When you add or look up a customer name, you are working
against the SQL Server database, but the new order details you just took
are stored locally in Access tables.
- Have many people
working in the database and want robust options to expose that data for
updating.
For example, Access offers data access pages for
the more technical user and forms if you want to be more user friendly.
Use Excel When
You Need:
- A flat or
non-relational view of your data (you do not need a relational database
with multiple tables).
This is especially true if that data is mostly
numeric — for example, if you want to maintain a financial budget for a
given year.
- Want to run
primarily calculations and statistical comparisons on your data — for
example, if you want to show a cost/benefit analysis in your company's
budget.
- Know your dataset
is manageable in size (no more than 15,000 rows).
Tip # 18 - Warning Using the Label Wizard for
Three-Across Labels
(Versions 2002 & 2003)
You may receive the
following warning when you are using the label wizard for three-across
labels: "Some data may not be display. There is not enough horizontal
space on the page for the number of columns and column spacing you
specified. Click Page Setup on the File menu, click the Customs tab, and
then reduce the number of columns or the size of the columns."
Simply ignore this
warning message. You should not experience any loss of data when
printing your labels. The message appears because the label dimensions for
certain Avery labels were modified in Access 2002/2003. Plus, some labels may
have a smaller left margin than what is permitted by certain printer
drivers. Therefore, whether or not you receive this warning message can also
depend on your printer.
Tip #19 - TIMESAVER--COPY any Object You’ve Created in
Access to Save Time
(All Versions)
You can save time by copying and pasting any object that you create
in Access.
Example: If you have a query that you’ve created and you want to create
another one almost identical to it, you can copy and paste the query, then
make minor changes to it, saving you time!
1. Right click on the object (query, report, form, table, macro,
etc.), select ‘Copy’ .
2. Then right click white background and select ‘Paste.’
3. Give the new object a name and click [OK].
4. Once new object is pasted, with it highlighted, you can click [DESIGN]
button to make any changes to the copied object.
Tip #20 - Jet Database 4.0 SP8 Issues for Access
(Version 2003)
When you initially open MS Access
2003 after being upgraded from an earlier version and open a
database file, you may receive an update notice. There is
a link available from the MS Access dialog box that will take you to
Microsoft's site where a security update can be downloaded and installed to
your PC. This update is an Update for Jet 4.0 Service
Pack 8 (KB829558). For step-by-step instructions on how to
download and install this update, please visit:
Access 2003
Security Update/Jet 4.0 Service Pack 8.
Tip #21 - Creating a MailTo: Hyperlink on a Form
(All Versions)
You can create a text box
that contains a "Mail To" hyperlink allowing the user to click on the field
and have their email program automatically open with the email address
displayed in the To field. Here's how:
- Open the form in form
design and insert a new label. Type the person's full email
address into the label so this appears on the form.
- Right click on
the inserted label and choose Properties.
- Select the Event tab
and click on the build button (...) for the 'On Click'
event.
- Choose Code Builder
and click Ok.
- In the sub that appears
in Visual Basic, type Application.FollowHyperlink "mailto:" & "the_person's_complete_email_address"
- Click on the Save
icon from the standard toolbar and from the File menu, select
Close and Return to Microsoft Access.
- Close the
Properties dialog box.
- Save your form
and open it in Form view.
- Click once on
the label displaying the person's email address. MS Outlook will open
with the person's email address in the To: field.
Tip #22 - Remove Blank Lines in a Mailing Label Report Caused by Blank
Fields (All
Versions)
You may have some fields that are blank or null and these may cause
mailing label reports to print with blank lines.
You can use the IIf()
and IsNull() functions to determine if a field is blank and if it is,
insert a carriage return and a line feed.
Here is sample code
for a Full Address text box and for the ControlSource property:
=IIf(ISNull([FirstName]),"",[FirstName]
& " " & _
IIf(IsNull([LastName]),"",[LastName] & Chr(13) & Chr(10)) & _
IIf(IsNull([Address]),"",[Address] & Chr(13) & Chr(10)) & _
IIf(IsNull([CIty]),"",[City] & ", ") & _
IIf(IsNull([Region]) ,"",[Region] & " ") & _
IIf(IsNull([PostalCode]),"",[PostalCode])
Set the Can Grow
Property to Yes
Set the Can Shrink Property to Yes
Tip #23 - Inserting Images in Forms - Several Options
(Versions 2002 &
2003)
First Option -
Inserting an OLE Object/Field in the Underlying Table
Access also has the option for
inserting an OLE Object field into a table. This can be used to store the
images themselves along with the table's data (Insert menu, Object). When
you create a form from a table which has an OLE Object field, the picture
(or Word documents or whatever) that is linked to the field will display.
This makes it a lot easier to create and synchronize records with pictures,
but … since it stores the image with the database, your database file can
become huge.
When inserting an object into the field, it will embed the object or you can
choose the Link check box to link the object. If you embed the object, it
becomes part of the database and if you make changes to the object from the
original source (for example an Image file changed in Photoshop), these
changes will not be reflected in Access. If you link the object, a link is
created between the source and the Access database. If you make changes to
the source data, these changes will also occur in the linked information in
Access.
Insert an OLE Object field into the underlying table (for example, the
Employees Table in the Northwind database) and embed a picture file in one
record and link a picture file in another record. Create a new form and
include this new field. Preview the form in Form View and the associated OLE
pictures appear with the appropriate records.
You should be able to move the picture file to a different directory on your
hard drive and the pictures should still appear without any problem. You
should also be able to move the database file to a different directory, and
the pictures will still appear with the right records.
Second
Option - Inserting a Path Field in the Underlying Table
It gets pretty complicated
when you want to include a picture on your form that changes with each
record (like it does for the Employee form in the Northwind database) and
you don't want to use an OLE Object field in your table. Here's how you do
that:
1. Make sure you have a field (text field) in the underlying table that
contains the path to the image files. In my case (Employee table), this
field was called Photo.
2. Create a Form and open it in Design View.
3. Show the Field List and from the Field List, drag the field that contains
the path to the image files onto the Form. In my case, this field was called
Photo.
4. From the toolbox, choose the Image tool and draw it onto your Form. This
Image frame was called Image30 in my example.
5. An Insert picture dialog box will appear, browse to choose any one of the
pictures. For example, you can browse to the picture for the first record in
the table.
6. Go into Form Properties, and click the Build button next to the OnCurrent
Event and choose Code Builder.
7. Create the Event Procedure:
Private Sub Form_Current( )
On Error Resume Next
Me![Image30].Picture = Me![Photo]
End Sub
8. Close out the Visual Basic Editor.
9. Back on the Form, click on the Photo control (the one bound to the
underlying table's text field which stores the path to the picture file).
10. Go into the Properties and choose the Build button for the AfterUpdate
Event.
11. Create the Event Procedure:
Private Sub Photo_AfterUpdate( )
On Error Resume Next
Me![Image30].Picture = Me![Photo]
End Sub
12. Close out the Visual Basic Editor and if you open the Form in Form View,
you should see a new photo for each record.
With the way the Northwind database is by default, if you move an employee
picture from its original path, that picture will not show on the form for
that particular record - unless you go into the Employee table and update
the path in the text field (Photo). Also, if you go into the Employees Table
and look at the Photo field, you will notice the full path isn't indicated.
It just has the filename for the image. Because of this, Access is
defaulting to looking in the same directory as the database file. If the
pictures are moved and not in the same directory as the mdb file (or if the
database file itself is moved), this is when it won't find the picture file.
If you use the full path for image files, this is not a problem. And of
course, if you have the full path for the image recorded in the table's
field and you move the image file without updating the record in Access, it
will not find the image.
Third Option - Using Image Control to Insert a Static Picture (one that does
not change with each record)
You can add a photo to a form
by using the Image control. Simply use the Image control and browse to a
picture on the Hard Drive. The form will open with the picture. The
properties for this image can be set as:
Name: Image4
Picture: C:\Program Files\Microsoft Office\Office10\Samples\EMPID1.BMP
Picture Type: Embedded
When you move the database or even the picture files themselves, the form
will still open with the picture.
Tip #24 - Use the ‘RepeatSection’ property in Access
Reports to repeat a group header (All
Versions)
You can use the RepeatSection property to specify whether a group header is
repeated on the next page or column when a group spans more than one page or
column.
Tip #25 - Quickly Move from One Record to the Next in a
Form (All
Versions)
The following shortcuts make it very easy to move from record to record,
while remaining in the same field location. If you edit the same field
within records, these shortcuts can save time.
Ctrl+PgDn - to
Move to the next record
Ctrl+PgUp - to
Move to the previous record
Tip #26 - Adding a $
Currency Symbol Inside an Access Field Code within a Word Form Letter -
(All Versions)
When you create letters
with merged currency fields from a MS Access database, your currency field
will not display with currency symbols (such as a dollar sign $ or
appropriate commas and decimals). To show currency fields as currency,
follow these steps:
- Inside your Word form
letter with the Access field codes showing, right mouse click on the
field you want to set as currency and select Toggle Field Codes.
This will show the field code in addition to the field name and this is
where you will add the addition code "switch" to tell it to place a $ with
the field data.
- With the field code
showing, type in the code information AFTER your field name and
BEFORE the brace }
{MERGEDFIELD Freight
\# $#,###.00}
Tip #27 - Receiving an
"Undefined Function" Error when using the TRIM, LEFT or RIGHT Function in a
Query or Report? -
(All Versions)
The cause may be due to a
missing Data Access Object (DAO) in the MS Access Object Library. To remove
this error and use the TRIM, LEFT, and/or RIGHT functions successfully,
follow these steps:
1. Open any form in
your database in design view.
2. Select the code view
icon from the toolbar  to open MS
Visual Basic for Applications.
3. In the Visual Basic
editor, select the Tools menu and choose References.
4. In the References dialog
box that appears, uncheck the option starting with "Missing..." and
check Microsoft DAO 3.6 Object Library (any MS DAO Object Library
version will work). Click Ok to close the References dialog box.
5. Close MS Visual Basic
for Applications by selecting the File menu and choosing Close and
Return to MS Access.
6. Back in MS Access, click
the Save icon to save the open Form and then Close the Form.
7. Run your query/report.
Tip #28 - Calculate a
Total or Other Aggregate Values -
(All Versions)
To calculate totals or
other aggregate values, you can create a Report. Then, open the
Report in design view and complete the following:
1. Click on the Text box
control  from the toolbox and
determine if you want a total or average for a group of records or
for all records in the report.
- If you want a total or
average for a Group of records (such as the total or
average sales for each order), add the text box to the group header or
footer.
- If you want a total or
average for All the records in the report (such as the total
or average sales for all orders), add the text box to the report header
or footer.
2. Right click on
the Text box and select Properties from the menu that appears
(or you can click on the Text box and then click on the
Properties icon from the toolbar).
3. In the ControlSource
property box, type an expression that uses the SUM function to
calculate a total or the AVG function to calculate an average. For example,
=SUM([SAT_VERB]) would display the sum of all SAT, Verbal scores and
=AVG([SAT_VERB]) would display the average of all SAT, Verbal scores.
Tip: You can
also include the Round function to round the total or average to a
specified number of decimal places. For example, =Round(Avg([CUM_GPA]),2)
would find the average CUM_GPA score and round this to two decimal
places.
Tip #29 - What To Do If
You Cannot Change, Add, or Delete Data in Tables that are Linked to a MS
Excel Workbook -
(Versions 2002 and higher)
If you build a query,
create a Form, or update tables programmatically using tables that are
linked to an Excel Workbook, you may find that you are unable to change,
add, or delete data in the tables. If this happens, try one of the
following:
1. Open the linked Excel
Workbook to make your changes within MS Excel.
or
2. Import the Excel
Workbook data into a MS Access table (rather than linking). Once the data
is imported into a table, open the table to make your necessary changes.
In addition, you can export the data back to MS Excel.
|
