Ch 8 -- Special Features and Programming
Microsoft® Access 97 Quick Reference
- 8 -
Special Features and Programming
If you develop your database into an application for others to use, there
are many features you can use to help users navigate through your database and to
keep them where you want them to be. You can manage the toolbars and menus that come
with Access as well as create your own.
On forms and reports, each time a user performs an action, an event occurs.
You can program responses to these events. The programming of Access is divided into
two pieces: macros and Visual Basic for Applications (VBA). The response to
an event can be either a macro or VBA procedure (also called code).
You can also create VBA code for general use throughout Access.
When you have large databases and multiple users, you might also be concerned
with optimizing your database as well as startup, multiuser, keyboard, and other
options.
Events: Creating Event Response
Events are user actions such as clicking the mouse, pressing a key, or
opening a form. Events can also be triggered by a Visual Basic statement or
by the program itself. Events are associated with forms and reports, sections
on forms and reports, and controls on forms. You can respond to an event by
creating a VBA procedure or choosing a macro.
Steps
1. Open the form or report in Design View and double-click the
Form/Report Selector, a section, or a control (on a form) to open the properties
sheet.
2. Move to an Event Property (on the Event tab).
3. Type or choose the name of a macro from the drop-down list or click
the build button (...) to bring up the Choose Builder dialog box.
4. Double-click Macro Builder to enter in macro statements or Code
Builder to enter VBA statements.
5. When finished building the macro or code, click the Close (X) button.
If you are building a macro, give the macro a name and choose OK.
For more help on building a macro, see also "Macros: Create in Design Window."
For more help on building code, see also "Programming: Create a Procedure."
Hyperlink Appearance
The default appearance of hyperlinks is blue underlined text for links
that have not been accessed. The default is purple for links that have been accessed
(also called followed hyperlinks) since you opened the database. This
is a general standard for Internet programs. However, you can change the appearance
if you like.
Steps
1. Choose Tools, Options and click the Hyperlinks/HTML
tab.
2. Choose a color from the H_yperlink Color and Followed Hyperlink
Color drop-down lists and check whether you want to Underline Hyperlinks.
3. If you want the hyperlink address shown in the status bar, check
Show Hyperlink Address in Status Bar. Choose OK when finished.
Locking Records
You have some options on what you want to happen when two people try to edit the
same record at the same time. The Record Locks options deal with multiple
users. You can set Record Locks globally for all databases, or for a specific form
or report. There are three possible choices:
- Locks. There is no locking. The second user to save a record gets a prompt notifying
that the record was changed by another user. The second user can choose which changes
to keep.
- All Records. The table is not accessible for editing for anyone
else (and any related tables that the form uses).
- Edited Record. Not available for reports; where the current record (and
possibly some surrounding records) is not available for editing.
Steps
1. To set record locking for all databases, choose Tools, Options;
click the Advanced tab.
2. Choose one of the following: No Locks, All Records, Edited
Records; choose OK when finished.
NOTE: To set record locking for a form,
open the form in Design View, click the Properties button, and choose No Locks,
All Records, or Edited Records from the Record Locks property (on the Data
tab). On a report, choose No Locks or All Records on the Property Sheet Other tab.
Macros: Create in Design Window
A macro is a series of stored actions that you can use for automating often-used
procedures. You can attach macros to command buttons, menu choices, or other
events for a form or report. When you use the Menu Builder in versions prior
to Access 97, you automatically create a series of macros. (See also "Menu:
Create Custom.")
For most macros, you can also create a VBA procedure. VBA procedures give
you more control than macros, especially the ability to control errors. (See also
"Programming: Create a Procedure.")
Steps
1. Click the Macros tab in the Database window and choose the New
command button. The Macro Design Window opens.
2. In the Action column, type or choose an action from the drop-down list.
3. If desired, type a comment on the same row in the Comment column.
4. In the lower half of the screen, type or choose any Action Arguments
for the specific action you chose in Step 2. For some arguments, you have the choice
of a Build button to help you create the argument.
5. Repeat steps 2-4 for each action you want in the macro. Click the Save
button to save the macro and give it a name, then choose OK.
To attach a macro to an event, type the macro name in the Event property.
(See also "Events: Creating Event Response.")
NOTE: There is a special macro that will run
when you open your database. Name your macro AutoExec. If you don't want this macro
to run when you open the database, hold down the Shift key as you open the file.
Macros: Create with Database Window
You can also create macros by using drag-and-drop from the Database window. This
is especially useful if you want to open multiple forms or print multiple reports.
After you drag-and-drop objects into the Macro Design window, you can modify
the macro actions to suit your needs. Before you begin, first close all windows except
the Database window.
Steps
1. Click the Macros tab in the Database window and choose the New command
button. The Macro Design window opens.
2. Choose Window, Tile Vertically to move the Database window
and Macro Design window side-by-side.
3. Click the object tab button (such as Reports) and drag the object
names into each line of the Macro Design window.
4. If desired, change the arguments for each object.
5. Click the Save button to save the macro and give it a name and choose
OK.
After you create a macro, you can attach it to a button or menu item. (See also
"Controls: Command Button Create" in the Forms and Reports
part of this book.)
NOTE: The defaults for each object you drop in
the Macro Design window are as follows:
- Table and query in Datasheet View and Edit mode
- Form in Form View and Normal mode
- Report in Print View (prints reports)
- A macro will run, and a module will open for editing.
Macros: Groups
If you use a separate macro name for every macro you create, your macro
list can become long and unwieldy. You might have problems finding and managing your
macros. An alternative is to keep related macros together by creating macro groups
and name the macros within each group. First, create the actions for a macro.
(See "Macros: Create with Database Window.")
Steps
1. Select a macro in the Database window and choose Design.
2. If the Macro Name column is not visible, click the Macro Names button
on the toolbar.
3. Type the name of the macro in the Macro Name column in the first row
of the macro.
4. Click the Save button to save the macro group and give it a name (if
you haven't already), and choose OK.
To choose the macro within a group in an event procedure (or the RunMacro
action within another macro), choose or type the macro group name, a period, and
then the macro name.
NOTE: If you always want the Macro Name column
to appear by default, choose Tools, Options; click the View tab; and
choose Names Column.
Macros: Keyboard Shortcuts
If you want to create your own keyboard shortcuts that will operate throughout
your database, you can use the Macro Name column and type characters
representing keyboard combinations. Be careful, however, not to include keyboard
combinations you often use such as Ctrl+C for Copy or Alt plus any of the underlined
letters of each of the menus. First, complete the procedures in the previous "Macros:
Groups" section before proceeding with this task.
NOTE: As an alternative to creating global
keyboard shortcuts, you can create keyboard shortcuts for each form. In the
Caption property of a button or of a label attached to another
control, type an ampersand (&) before the letter you want to be the shortcut.
Then pressing Alt+the letter is the same as clicking the option.
Steps
1. Select a macro group in the Database window and choose Design (or
create a new macro and the actions to be called with shortcut keys).
2. Type the keyboard representation in the Macro Name column in the first
row of the macro (to the left of where the macro actions start). Use a caret (^)
to represent Ctrl and plus (+) to represent Shift. Include function keys and
edit keys in curly braces such as {F1} or {Delete}. ^p would
mean Ctrl+P.
3. Repeat Step 2 to add more keys.
4. Choose File, Save As/Export to save the macro group, type
AutoKeys in the New Name text box, and choose OK. Close the macro group.
Macros: Save as VBA Module
There has been some speculation whether Microsoft will continue to have macros
in future versions of Access (see Special Edition Using Microsoft Access 97
by Roger Jennings). According to Jennings, macros are only used for backward compatibility
and probably will not be included in the next version of Access. Therefore, he recommends
that you work entirely in VBA. To ease the transition, you can create a macro
and then save it as a VBA module.
Steps
1. In the Database window, select a macro. You can also have the macro
open in Design View.
2. Choose File, Save As/Export.
3. In the File Save As dialog box, click Save as Visual Basic Module
and choose OK.
4. In the Convert Macro dialog box, choose whether you want to add error
handling and add comments, and choose Convert.
Your converted macro is now listed on the Modules tab of the Database window.
To rename the module, right-click and choose Rename from the shortcut menu.
If what you converted was a macro group, each macro is a separate procedure
in the module.
Macros: Set Conditions
While you are working with macros, there are cases when you want to run one action
if one condition exists and another action if another condition exists. For example,
you can create a dialog box that prompts the user before a report is
generated or create alphabetical filter buttons on a form.
Steps
1. In the Database window, click the Macro tab and choose New.
2. If the Conditions column is not visible, click the Conditions button
on the toolbar.
3. Type an expression in the Conditions column in the first row
of the macro. Remember to include field names in square brackets. If
you want to include more complex conditions, you can click the Build button.
4. Create the macro action and its arguments on the same line as
the condition. The action will run if the condition evaluates to True. If
you want more than one action to run, include three periods (...) in the next
row(s) in the Conditions column and choose the action(s).
5. On the next row (without ...), include any action you want to run if
the condition evaluates to False.
6. Click the Save button to save the macro, give it a name (if you haven't
already), and choose OK.
NOTE: If you always want the Conditions column
to appear by default, choose Tools, Options; click the View tab; and
choose Conditions Column.
Menu: Create Custom
A menu bar contains the words that appear under the title bar of your application.
The default menu bar starts with File and ends with Help. Each menu drops down from
one of the words on the menu bar (the File menu and Help menu). When you create an
application for someone else to use, you can limit menu options and toolbars to make
the job easier for the user or to help the user avoid getting in trouble. You can
attach menu bars and toolbars to forms and reports and have a menu appear at startup
as well.
NOTE: In Access 95 and Access 2, you could use
the Menu Builder to create menu bars. In a form or report, go
to the Menu Bar property and click the Build button.
Steps
1. Choose View, Toolbars, Customize; click the Toolbars
tab; and choose the New command button.
2. Type the name of the menu bar in the New Toolbar dialog box and
choose OK. If necessary, drag the title bar of the Customize dialog box to see the
new menu/toolbar.
3. On the Customize dialog box, the focus should be on your new
toolbar name. Choose the Properties command button and change the Type to
Menu Bar. To have your menu bar be a shortcut menu, choose Popup. When finished choose
Close.
4. Click the Commands tab of the Customize dialog box. To put one of Access'
menus on your menu, choose Built-in Menu in the Cate_gories list and drag one of
the menus from the Commands list onto your menu. To create your own menu,
choose New Menu from the Cate_gories list and drag New Menu from the Commands
list onto your menu.
5. To change the menu name (or item on the menu), right-click the name
and type the new menu name or menu item name in the shortcut menu's Name box.
To add a hotkey, type an ampersand (&) before the letter. For menu items,
you can also change the button image that appears next to the menu item or choose
just to have the text appear.
6. To add or delete items from one of your menus on the menu bar, click
the menu to first open it. Drag items off. To add items, drag them from the Commands
list in the Customize dialog box. When finished, choose Close in the Customize dialog
box.
To have this menu appear as the default menu for your application, see "Menu:
Startup." To add this menu to a form or report, see "Menus and Toolbars:
Add to Form/Report."
NOTE: If you are creating a shortcut menu, you
need to check the Shortcut Menus item in the Toolbars list of the Customize
dialog box to display the shortcut menu bar with your menu name (and others) in order
to add or remove items.
Menu: Startup
If you want your database application to start with a menu other than the
default Access menu, you can choose a menu you've already created. The first step
is to create the menu. (See "Menu: Create Custom.")
Steps
1. Choose Tools, Startup.
2. In the Menu Bar drop-down list, pick your menu.
3. If you do not want Access' menu bars visible (when you open any object
or go to Design View), uncheck Allow Full Menus.
4. You can also choose another menu for the shortcut menu in the Shortcut
Menu Bar list box and uncheck Allow Default Shortcut Menus.
5. When finished with all choices, choose OK.
NOTE: If you uncheck Allow Full Menus
in Step 3, the next time you open your database, Access' menus will not be available.
If you need access to the default menus (such as going back to the Startup dialog
box), hold down Shift as you open your database to override the Startup options.
Menus and Toolbars: Add to Form/Report
When you design an application for other users, generally you have a startup switchboard
form (see "Controls: Command Button Create" in the Forms and Reports
part of this book) to add buttons that will make your switchboard. In addition to
the buttons on the form itself, you can add a menu and toolbar that are different
from the Access default choices. You can add a menu and toolbar to any form or report
through properties. To accomplish this procedure, you must first create a menu or
a toolbar. (See "Menu: Create Custom" and "Toolbar: Create Custom.")
Steps
1. Open the form or report in Design view and double-click the Form/Report
selector to open the Property sheet.
2. Move to the Menu Bar, Shortcut Menu, or Toolbar property (on the Other
tab) and type or choose the name of your menu or toolbar from the drop-down list.
NOTE: To see or change which menus or toolbars
are available for form or report menu bars, shortcut bars, or toolbars, choose View,
Toolbars, Customize; select your toolbar or menu on the Toolbars
tab; and click the Properties command button. Choose one of the items on the Type
combo box. Use Popup for shortcut menus.
Optimization: General Suggestions
Because Access writes all of its data and design to a single file, as data and
objects are deleted and created, free space and fragmented data structures are created.
This is similar to what happens with your computer's file system. Over time, the
database file grows larger than it needs to be and performance can suffer.
To optimize performance, you can compact the database. Doing so creates a new file
where all of the data is stored efficiently and in sequence.
Steps
1. Save any design changes for any open object (form, report, and so on) you
have been working on.
2. To compact a database, choose Tools, Database Utilities,
Compact Database.
NOTE: To compact the current database, see the
topic "Database: Compact" in the File Management part of this book.
Additional Ways to Optimize Your Database
Compacting the database is just one of many ways you can improve the performance
of your database (See also other Optimize tasks in this section). Some additional
options for optimizing the performance of a database include:
- Running in Exclusive mode. You can run the database in Exclusive mode
by setting that check box in the Open dialog box when you open the
database.
- Installing to a hard drive. You can install the database file to a local
hard drive instead of a network server.
- Assigning more memory. You can assign more memory to Access, or close
other programs or unnecessary system utilities like wallpaper, to free up additional
RAM. (You can add additional RAM to your computer, but do not set up a RAM disk because
Access creates its own.)
- Defragmenting the hard drive. You can defragment your hard drive using
the Disk Defragmentor. Choose the Windows 95 Start button, Programs, Accessories,
System Tools, Disk Defragmentor.
- Upgrading. You could improve your processor by upgrading your computer.
Optimization: Performance Analyzer
The performance of your database might be limited by the design of the
database itself. If you have tables with redundant data, you can have poorly constructed
indexes, inappropriate data types, or the wrong join definition for
relationships. Run the Performance Analyzer to see suggestions on optimizing database
objects, and then perform the suggestions as desired.
CAUTION: The Performance Analyzer can take a
significant amount of time to run. Experiment with it first on a small database.
Also, make sure you back up your database before you run the Performance Analyzer.
(See "Backup Data" in the File Management part of this book)
Steps
1. Choose Tools, Anal_yze, Performance.
2. Click an object tab or the All tab in the Database window
and select the objects you want to analyze (or click the Select All button). Choose
OK.
3. The results appear in the Analysis Results list box. Icons appear
for recommendations, suggestions, ideas, and whether something was fixed. Move to
each item on the list and look at the lower half of the screen (Analysis Notes) for
a description of the item and additional tips for fixing the problem.
4. For recommendations and suggestions, you can click the Optimize
button to make the change. Choose Close when finished.
Optimization: Split Database
You can split a database into two files: one that contains the tables,
and another that contains all of the other objects in the database that act
on the base tables (queries, forms, reports, macros,
and modules). This is useful when you want to put the table object(s)
on a network server and the other object(s) on the user's machine. This is also useful
when you want to provide a means for users to access data while maintaining their
own forms and reports or database interface.
You can use the Database Splitter Wizard to perform the function of splitting
an existing database into its tables and other component objects.
Steps
1. Choose Tools, Add-Ins, Database Splitter.
2. Choose the Split Database command button.
3. Enter the name for the tables database in the File Name text
box on the Create Backend Database dialog box, then click the Split command
button.
Access creates a new database based on your current data and structure. In the
original database, Access attaches the tables to the backend database created.
Optimization: Table Analyzer
You can analyze your database table structure using the Table Analyzer
Wizard. This wizard checks for redundant data, and can create smaller related tables
and a query with relationships between the former tables to improve performance.
CAUTION: Because Access makes significant changes
throughout your database when you use the Table Analyzer, you should back up your
database first. (See "Backup Data" in the File Management part of this
book.)
Steps
1. To run the Table Analyzer Wizard, choose Tools, Analyze,
Table. On the first two steps of the wizard, Access can show you some examples
of duplicating information and how to deal with updating information. Choose Next
twice if these screens appear.
2. Choose the table in the list you want to analyze. Also, uncheck Show
Introductory Pages if you don't want to see the pages mentioned in Step 1. Choose
Next.
3. Tell the wizard to suggest fields to go into the tables and choose Next.
4. On the next screen of the wizard, you can confirm the suggestions that
the wizard made for you, create a new table by dragging fields into a blank area,
and move fields to a table by dragging between field lists. To name a table,
click the table and then click the Rename Table button and enter the new name. Choose
Next.
5. Select any primary keys you want by clicking the field and then
clicking the Set Unique Key button. You can also create an AutoNumber primary
key field by clicking the Add Generated Unique Key button. Choose Next.
6. The wizard might find close duplications in the values for tables. In
the Correction column, choose Leave As Is to ignore the corrections. Choose Next;
on the last step of the wizard, tell Access to create a query that will be used in
place of your original table in forms and reports. Choose Finish.
Access creates the tables and query in your Database window and renames
your original table with an _OLD extension.
TIP: If you are finding and sorting records,
creating an index specifically composed for this purpose can improve performance
considerably. (See the tasks relating to Index in the Table and Database Design part
of this book.)
Programming: Create a Procedure
Procedures are the basic programming units of VBA. They are alternatives
to creating macros and offer much greater flexibility, including the ability to trap
and handle errors. (See also "Macros: Create with Database Window.")
There are two types of procedures: sub procedures and function procedures. The
major difference between the two is that a function procedure can return a value
where a sub procedure cannot. To create a function procedure, see also "Programming:
Create Function." Many of the other following tasks in this part help you create
procedures.
TIP: This book covers only some of the fundamentals
of Visual Basic. For more details, you can choose from several Que titles. These
titles include Special Edition Using Microsoft Access 97, which is the companion
piece to this Quick Reference; Access 97 Expert Solutions; and Access 97
Power Programming.
Steps
1. From the Database window, click the Modules tab and then click the New
button to create a new module. Make sure that the Option Explicit statement
appears at the top of the module.
2. Type Sub followed by the procedure name.
3. If there are any arguments that are required to run the procedure,
include those in parentheses as well as the keyword As and the data type
for each argument. Press Enter. Access will automatically add End Sub at
the end of the procedure.
4. Type any statements between the Sub and End Sub. As
you type, Access' AutoComplete feature might show a drop-down list. You can click
one of the entries in the drop-down list or press Ctrl+Enter to enter the selected
item.
5. To add comments to your procedure, type an apostrophe (') and
then the comment. The apostrophe can begin a line or can be added after any
statement.
6. When finished writing the procedure, choose the Save button and give
the module a name.
NOTE: This task shows how to create a new module
and new procedure within the module. If you want to add a procedure to an existing
module, select the module and click the Design button. Move to the end of
the module (press Ctrl+End) and type the Sub statement mentioned in Step 2.
The following is an example of a Sub procedure:
Sub ShowTableNames() `Names of tables in current database
Dim x As Integer, tdef As TableDef
`CurrentDb is the Current Database
For Each tdef In CurrentDb.TableDefs
Debug.Print tdef.name
Next tdef
End Sub
Programming: Create Function
You might need a function that Access does not supply as one of the built-in
functions. You can try creating one from Excel or you can create one of your own.
(See also "Programming: Function Create from Excel.")
Steps
1. Open an existing module in Design View, or click the New
button on the Modules tab to create a new module.
2. In a blank area outside of any procedures, type Function Functionname(,
any arguments you need to run your function, and a closed parenthesis),
and press Enter. If you have arguments, include the keyword Type after the
name and the data type. Replace Functionname with the name of your
function. For example, type Function FahrenheitToCelsius (Fdegree as Single).
Access will automatically add End Function at the end of the procedure.
3. If necessary, type any lines for declaring variables additional to the
arguments or any other statements required for processing the function. In the line
that will return the value, type the function name again, an equal sign, and the
formula that calculates the function. For example, type FahrenheitToCelsius =
5/9*(Fdegree - 32).
4. Click the Save button to save the module, and then test the procedure.
You can use this function within another procedure or as part of a calculated
field in a query, form, or report. (See "Calculated
Fields: Queries--Create by Typing" and "Calculated Fields: Forms and Reports--Create
by Typing" in the Calculations part of this book.)
NOTE: To see a list or use a built-in function
from Access, click the Build button when you are creating a formula or expression
in Design View of a query, form, or report. Choose Expression Builder and
double-click Functions in the folder area. To see Access functions, click the Built-In
Functions folder. To see functions you've created, click your database name
folder.
CAUTION: Be aware that there are two similar
but distinctive Build Buttons in Access. There is the toolbar Build button, denoted
by a wand over an ellipses (...); and there is the Properties tab build button, denoted
by simply an ellipses (...).
Programming: Create Function from Excel
Access has a significant number of built-in functions, but Excel has even more.
If you are familiar with an Excel function that you want to use in Access,
follow these steps. Excel has to be loaded for this procedure to work. For steps
on creating a function in Access, see also "Programming: Create Function."
Steps
1. Go into Excel and look up the syntax for the function including
any required arguments within the parentheses. For example, the Round
function requires a value and number of decimal places.
2. Return to Access and open an existing module in Design View
or click the New button on the Modules tab to create a new module.
3. Make sure Excel is registered by choosing Tools, References
and checking Microsoft Excel in the Available References list box. Choose
OK.
4. In the function statement, include the new function name (it can be
the same name as the Excel function) and any arguments required. For example, type
Function ExRound (Number as Single, Places as Byte). Access will automatically
add End Function at the end of the procedure.
5. On the line that returns the value, type Functionname = Excel.Application.ExcelFunctionname
and include the names of the arguments you added in Step 3. Replace Functionname
with the name you want to use in Access, and ExcelFunctionname with the name
of Excel's function. For example, type ExRound = Excel.Application.Round (Number,Places).
6. Save the module and test the procedure.
The first time you run this function, it will take a few moments to run as Excel
is opened and the function is retrieved. You can use this function in VBA
procedures, queries, forms, reports and the Debug window.
Programming: Create Messages
Often you will want to create messages that tell users what is going on in your
program or ask users for input. You add the Msgbox statement as part of
your procedure. You first need to create a procedure or function. (See "Programming:
Create a Procedure" or "Programming: Create Function.")
Steps
1. Open the module with the procedure in Design view and click
the Procedure list box (the arrow on the right side below the VBA module window
title bar) to choose your procedure.
2. In the upper portion of your procedure, declare a return value for the
message box. For example, type Dim RetValue as Integer. You do not have to
use the return value in your procedure, but if you do, Access will save the value
associated with the button that the user chooses so you can use it later in the procedure.
3. Move to the location in your code to insert the message box and
type RetValue=MsgBox("Prompt",Buttons, "Title") where
Prompt is whatever you want the dialog box to say, and Title
is the title of the dialog box. Buttons is a Visual Basic constant(s) identifying
the buttons and icons on the dialog box. Some examples include vbYes, vbYesNo,
vbOKOnly, vbQuestion, and vbExclamation. You can include
a button and an icon by using an expression such as vbYes + vbQuestion
where the Buttons argument is needed.
4. If you want to use the RetValue later in your procedure, test
it with some statement like If RetValue = vbYes Then.
5. When finished with your procedure, click the Save and test it.
NOTE: For more choices for the Buttons
argument, look up MsgBox function in the online help.
You can also create message boxes by choosing the MsgBox action in a macro
and filling in the action arguments in the lower half of the screen.
Programming: Debug with Breakpoint
If you've tried debugging your program (see "Programming: Debugging")
and there are no visible errors, and you know there is still something wrong with
your program, you might need to step through your program one line at a time until
an error occurs. When you want to see the changing values associated with your variables,
you can set a watch expression. As you move through your procedure, the watch expression
changes. A breakpoint enables you to stop the procedure at a specific statement and
then step through the rest of the program to look for errors.
Steps
1. Open the procedure in Design View and click the mouse in the first
line of questionable code. The cursor cannot be on a blank line, a line with
only a comment, or on lines declaring variables.
2. Click the Toggle BreakPoint button.
3. If you want to see the value of variables or expressions during the
procedure, select the variable or expression and click the Quick Watch
button.
4. Run the procedure by performing the event that triggers the procedure
or clicking the Debug Window button and typing the procedure name. Include
values for arguments in parentheses if the procedure requires arguments, and
type a ? before function names. After you press Enter, Access will run until
it reaches the location where you set the breakpoint, then display the code window
and Debug window. Any variables or expressions you chose will appear on the Watch
tab of the Debug window.
5. Click the Step In button to go to the next line of your code. If the
line of code calls another procedure, click Step Over if you don't want to step through
the sub procedure. Continue this step, viewing the Debug window for any watches you
set until you find your error.
6. If you want to run your code to the end without going step-by-step,
click the Go/Continue button. If you want to stop running your code, click the End
button.
Programming: Debugging
When you create a procedure (see also "Programming: Create a Procedure"
or "Programming: Create a Function"), there are a few things you can do
to find errors with your program.
NOTE: To help you avoid misspelling variable
names, choose Tools, Options; click the Modules tab; and check Require
Variable Declaration. To view this statement or add it manually, choose General on
the Object drop-down list (on the left) and Declarations on the Procedure
drop-down list (on the right). Type Option Explicit in this section if it
doesn't appear.
Steps
1. As you type statements and press Enter, syntax errors appear in
red with an error message.
2. If there are no apparent errors in your procedure, click the Compile
Loaded Modules button to see if you have any compile errors. If you have any errors,
Access will highlight the line or variable containing the error
3. If you have no apparent syntax or compile errors, try running the procedure
by clicking the Debug Window button or performing the event that calls
the procedure.
4. Fix the error in your program, and click the Save button to save your
changes.
If there are no syntax, compile, or runtime errors, and you know the program is
wrong, you have a logic error. You might need to add a breakpoint and a watch
expression and then step through the program. (See also "Programming:
Debug with Breakpoint.")
NOTE: In the preceding Step 3, the procedure
you test can be in different locations. If the procedure is on a module, click
the Debug Window button to open the Debug window, type the procedure name, and press
Enter. If the procedure is a function procedure that returns a value, type a question
mark and the function name. If either the sub procedure or function procedure require
arguments, enclose values in parentheses after the name in the Debug window.
If there is a runtime error, Access might describe the error well enough for you
to debug the program.
TIP: You can also press Ctrl+G to open the Debug
window.
Programming: Find Procedures
If you want to view or edit Visual Basic procedures, there are a number of ways
you can find the procedure.
Steps
1. In Design View of a module, click the Object Browser button on the
toolbar.
2. In the top drop-down list, find the name of the current database
file. In the second drop-down list, type the name of the procedure (or text within
the procedure) and then click the Search button.
3. In the Search Results area of the Object Browser window, double-click
the procedure.
NOTE: You can also go to procedures in other
ways depending on where the procedure is. If you are in the Design View of
a module and the current procedure calls another procedure, click the name
of the procedure and press Shift+F2 to move to the referenced procedure.
In Design View of a form or report, open the Property sheet
and go to the event procedure. Click the Build button.
If in Design View of a module with the current procedure, click the Object drop-down
list and choose the object associated with the procedure (if it is a form module,
for example). Then click the Procedure drop-down list and choose the procedure name
or event.
Programming: Printing
To document your work or show examples, you will want to print your Visual Basic
programming statements.
Steps
1. Open a module in Design View. If you are in Design View of a form or report,
click the Code button.
2. Click the Print button to print all procedures within the module.
3. If you want to print multiple modules at one time as well as see other
properties including owner, date created (and controls for forms and reports),
choose Tools, Anal_yze, Documentor and choose the objects you want.
Then click OK. The document will show in Print Preview. To print, click the Print
button.
CAUTION: If you use the Documentor, there will
be many pages for each form or report. If you want to print just the code, choose
the Options button on the Documentor dialog box and uncheck everything in
the include area except Code. In the Include for Sections and Controls
area, choose Nothing.
Programming: Variables Setting
At the beginning of each procedure, you need to identify the variables that you
will be using. Variables are useful if you will be referring over and over
to the same value or object. When you declare variables, it is a good idea
to also declare the data type instead of letting Access use the default Variant
type to help determine programming errors.
Steps
1. Open up the module in Design view.
2. To identify global variables which you will use in more than
one procedure, choose General from the Object drop-down list and Declarations from
the Procedure drop-down list. To identify local variables which you will only use
in the procedure, choose the object (such as a command button), if necessary,
from the Object drop-down list and choose the procedure name or event from
the Procedure drop-down list.
3. Type Dim, a space, and the variable name.
4. Type As, a space, and then a data type such as String
(for text), Integer, Single, Double, Boolean (for Yes/No),
Control, Form, or other data type. If you leave off As and the
data type, Access assumes a Variant type which can be any data type.
5. Repeat Steps 3 and 4 for all variables. Finish the procedure, save the
module, and test it.
NOTE: For more help on data types, search for
Data Type Summary on the Help Find tab.
Programming: View Button Wizard Results
You can have Access create a button and the programming behind it. (See also "Controls:
Command Button Create" in the Forms and Reports part of this book.) To see what
the programming is and modify it, you need to look at the Visual Basic code.
Steps
1. Open the form in Design View, right-click the command button,
and choose Build Event on the shortcut menu.
2. Access opens into a VBA code window. The name of the command
button appears in the Object drop-down list at the top left, and the Click
procedure appears in the Procedure drop-down list on the right. To go to a different
object and procedure, choose from these lists.
3. The procedure starts with Sub and then the procedure name.
The procedure name includes the command button's name, an underscore, and the word
Click. The procedure ends with End Sub.
4. ear the top of the procedure, Access declares any variables it needs
(with Dim statements) and then uses these variables later. A common variable
is strDocName which holds the name of a document (usually a form or report)
that you are opening. You can edit the statement strDocName = "NameofDocument"
and type any document of the same type in the quotes. Another common variable is
strLinkCriteria which holds the criteria for choosing which records to display.
You can edit the statement defining the criteria as well.
5. When finished viewing or modifying the procedure, click the Close (X)
button and save the form.
NOTE: Access also has a simple error handler
in the procedure which starts with the statement On Error GoTo and then
has the label Err, underscore, and then the procedure name. Near the End
Sub statement is the Err label which shows statements that will run
if Access encounters an error. For buttons created with the wizard on the Toolbox,
Access will simply show a message box with the error description and then exit the
procedure. You can modify these statements.
Programming: View Options
While you are working in VBA, you can set viewing options to make programming
easier. These options include the size and color of fonts for the code window,
and whether you want to see multiple procedures at one time.
Steps
1. Choose Tools, Options, and click the Modules tab.
2. To change the text that appears in the code window, choose one of the
types of text from the Text Area drop-down list (such as Keyword Text) and change
the options for the color. You can also change the font and size of the font for
all text in the code window.
3. To have Access indent the same for each line after you press tab, check
AutoIndent and choose the Tab Width. Also in the coding area, include
whether you want Access to check syntax, require you to declare variables,
compile modules automatically, and help complete statements with Auto
List Members and Auto _Quick Info. Check Auto Data Tips to show value of variables
as ScreenTips in Break mode.
4. If you want to see one procedure after the other (instead of one procedure
per screen), check Full Module View and Procedure Separator to add
a line between procedures.
5. Select additional choices in the Windows Settings area to allow drag-and-drop
editing, keep the Debug window on top, and show icons representing breakpoints
and bookmarks in the code window. When finished making all selections, choose OK.
NOTE: For more help on any option, click the
Help (?) button at the top right of the Options dialog box and click the option.
Startup Options
You can specify a number of options that are set when a database starts.
For example, you can open a particular form, display and customize toolbars,
and allow or disable shortcut menus. The Startup dialog box now covers almost anything
you would do with the AutoExec macro. (See also "Macros: Create in Design
Window.") If you are going to create a run-time version of Access with the Developer's
Tools, fill out the options on this dialog box.
Steps
1. Choose Tools, Startup with a database open.
2. Type the text you want to appear in the title bar in A_pplication Title,
and choose the icon you want in the title bar by clicking the Application Icon
button.
3. Choose the Menu Bar and Shortcut Menu Bar you want at
startup. Choose the check boxes determining which Access default menus and toolbars
should appear.
4. From the Display Form drop-down list, choose the form you want
to appear at startup and choose whether you want the Database window and status
bar to be visible or hidden.
5. Click the Advanced button and choose whether you want to see the code
after an error is made and allow the following keys to be available: F11 (Database
window), Ctrl+G (Debug window), Ctrl+F11 (toggle between menu from step 3
and built-in menu), and Ctrl+Break (stop procedures). Choose OK when finished setting
all options.
NOTE: If you don't want to have these startup
options run, hold down Shift when you open the database file.
Toolbar: Create Custom
When you design a form or report, you can display a toolbar different
than the default toolbar for that object. You can also set a non-default toolbar
to appear for your entire application. The first step is to create the toolbar.
Steps
1. Choose View, Toolbars, Customize; click the Toolbars
tab; and choose the New command button.
2. Type the name of the toolbar in the New Toolbar dialog box and
choose OK. If necessary, drag the title bar of Customize dialog box to see the new
small toolbar.
3. Click the Commands tab of the Customize dialog box. Choose one
of the names in the Cate_gories list and drag one of the items from the Commands
list onto your toolbar. To add a macro to a button, choose All Macros from
the Cate_gories list and drag a macro from the Commands list.
4. To add a Visual Basic function procedure to a button, drag any item
from the Commands list. Right-click the button on the new toolbar and choose
Properties. Type a name for the button in the Caption box, and
type text for a ToolTip if desired. In the On Action box, type =functionname()
where functionname is the name of your function (you cannot use a sub procedure).
5. To change the image of the button, right-click the button, choose Change
Button Image, and pick a picture.
6. Repeat Steps 3-5 for all the buttons on your toolbar, and choose the
Close button on the Customize dialog box when finished.
To delete a button on your customized toolbar, drag it off the toolbar while the
Customize dialog box is visible. To view or hide the toolbar, see also "Toolbars:
Display."
TIP: While customizing a toolbar you can also
right-click the button and choose Edit Button Image (and draw your own image)
or Copy Button Image from another button and then return to the button and
choose Paste Button Image. Another alternative is to type text in the Name
box and choose Text Only.
NOTE: There are some toolbar properties you can
change. On the Customize dialog box, choose the Properties command button
and decide if you want to allow customizing, moving, resizing, and show/hide for
your toolbar.
Toolbars: Display
Generally you should let Access display the toolbars that are the default for
each part of the program (the Table Design toolbar in Table Design View,
Form Design and Form/Report Formatting toolbars in Form Design View,
and so forth). If you display too many toolbars, it is difficult to tell which buttons
you need and you have much less space as your work area. However, you might want
to display the Web toolbar or custom toolbars you've created.
Steps
1. Choose View, Toolbars, Customize and click the Toolbars
tab on the Customize dialog box.
2. Place a check mark by each toolbar you want to see.
3. If a toolbar appears when it should not, select the toolbar name and
choose the Reset button.
4. If you want to see large buttons on the toolbars, show ScreenTips (names
of tools), or include shortcut keys with the ScreenTips, click the Options
tab and make your choices. Choose Close when finished.
NOTE: You can also use the macro action
Show Toolbar to display or hide a toolbar or type in a VBA procedure DoCmd.ShowToolbar
"Toolbarname", ShowConstant. Type the name of the toolbar
in quotes in place of Toolbarname. Type one of the following Access constants
in place of ShowConstant: acToolbarNo, acToolbarYes, or acToolbarWhereApprop.
The default is acToolbarYes if you leave off this option.
| 
