لطفا منتظر باشید ...
What We Left Out
To keep this book to a reasonable length, we have made some
assumptions about your skills. First and foremost, we take it for
granted that you are interested in using Microsoft Access and are
willing to research the basics in other resources. This
isn't a reference manual or a
"getting started" book, so we
assume you have access to that information elsewhere. We expect that
you've dabbled in creating Access objects (tables,
queries, forms, reports, and pages) and that you've
at least considered working with VBA (Visual Basic for Applications,
the programming language included with Access). We encourage you to
look in other resources for answers to routine questions, such as
"What does this Option
Explicit statement do?" For
example, see Access Database Design & Programming, Third Edition,
by Steven Roman (O'Reilly) or VB & VBA in a
Nutshell by Paul Lomax (O'Reilly)
To get you started, though, following are basic instructions for what
you'll need in order to use the solutions in this
book. For example, you'll encounter requests to
"create a new event procedure."
Rather than including specific steps for doing this in each case, we
have gathered the most common techniques you'll need
into this section. For each technique we've included
a help topic name from the Access online help, so you can get more
information. The procedures here are not the
only way to get the desired results, but rather
are single methods for achieving the required goals.
How Do I Set Control Properties?
In the steps for many of the
solutions in this book, you'll be asked to assign
properties to objects on forms or reports. This is a basic concept in
creating any Access application, and you should thoroughly understand
it. To assign properties to a control (or group of controls), follow
these steps:
In design mode, select the control or group of controls. You can use
any of the following methods (each of the items here refers to form
controls but works just as well with reports):
Single control
Click on a single control. Access will mark it with up to eight
sizing handlesone in each corner, and one in the middle of
each side of the control, if possible.
Multiple controls
Click on a single control, then Shift+Click on each of the other
controls you want to select. Access will mark each of them with
sizing handles.
Multiple controls
Drag the mouse through the ruler (either horizontal or vertical).
Access will select each of the controls in the path you dragged over.
If partially selected controls don't become part of
the selection and you'd like them to, open
Tools
Options Forms/Reports and look at the
Selection Behavior option. It should be set to Partially Enclosed.
Multiple controls
If you need to select all but a few controls, select them all and
then remove the ones you don't want. To do this,
choose the Edit Select All menu item. Then Shift+Click on
the controls you don't want included.
Make sure the properties window is visible. If it's
not, use View Properties (or the corresponding toolbar
button).
If you've selected a single control, all the
properties will be available in the properties window. If
you've selected multiple controls, only the
intersection of the selected controls' properties
will be available in the properties window. That is, only the
properties all the selected controls have in common will appear in
the list. As shown in Figure P-1. Select a property group and then
assign the value you need to the selected property. Repeat this
process for any other properties you'd like to set
for the same control or group of controls.
Figure P-1. The properties window shows the intersection of available properties when you've selected multiple controls
|
How Do I Create a New Module?
VBA code is stored in containers
called
modules , each consisting of a single
declarations section, perhaps followed by one or more procedures.
There are two kinds of modules in Access:
global
modules and
class modules . Global
modules are the ones you see in the database window, once you choose
the Modules tab. Class modules are stored with either a form or a
report and never appear in the database window. (Actually, you can
also create standalone class modules, which do appear in the database
window. The use of these types of modules, which allow you to define
the behavior for your own objects, is beyond the scope of this book.)
There are various reasons to use one or the other of the two module
types, but the most important consideration is the availability of
procedures and variables. Procedures that exist in global modules
can, for the most part, be called from any place in Access.
Procedures that exist in a class module generally can be called only
from that particular form or report and never from anywhere else in
Access.
You'll never have to
create a form or report module, because Access creates those kinds of
modules for you when you create the objects to which
they're attached. To create a global module, follow
these steps:
From the Database Explorer, click on the Modules tab to select the
collection of modules, then click on the New button (or just choose
the Insert
Module menu item).
When Access first
creates the module, it places you in the declarations section. A
discussion of all the possible items in the declarations section is
beyond the scope of this Preface, but you should always take one
particular step at this point: if you don't see
Option Explicit at the top of
the module, insert it yourself. Then use the Tools Options
menu from within the VBA editor to turn on the Require Variable
Declaration option (see Figure P-2). With this option turned on, all
new modules you create will automatically include the
Option Explicit statement. If
you don't insert this statement and Access
encounters a reference to an unknown variable, Access will create the
variable for you. With the Option
Explicit statement, Access forces you to declare
each variable before you use it.
Although this may seem like an unnecessary burden for a beginner,
it's not. It's an incredible time
saver for all levels of users. With the Option
Explicit statement in place, you can let Access
check your code for misspellings. Without it, if you misspell a
variable name, Access will just create a new one with the new name
and go about its business.
Figure P-2. Use the Tools Options dialog from within VBA to turn on the Require Variable Declaration option
If
you are asked to create a new function or subroutine, the simplest
way to do so is to use Insert Procedure. For example, if
the solution instructs you to enter this new procedure:Function SomeFunction(intX as Integer, varY as Variant)
you can use Insert Procedure to help you create the
function.
Click OK in the Add Procedure dialog, as shown in Figure P-3. Access
will create the new procedure and place the cursor in it. For the
example in Step 3, you must also supply some function parameters, so
you'll need to move back up to the first line and
enter intX as
Integer, varY
as Variant between the two
parentheses.
Figure P-3. The Add Procedure dialog helps you create a new function or subroutine
How Do I Import an Object?
In
this book's solutions, you'll often
be asked to import an object from one of the sample databases. Follow
these steps:
With your database open on the Access desktop, select the database
window by pressing F11. (If you're in the VBA
editor, first press Alt+F11 to get back to Access.)
Choose File Get External Data Import, or
right-click on the database window and choose Import.
Find the database from which you want to import a module, and click
Import.
In the Import Objects dialog, select all of the objects
you'd like to import, moving from object type to
object type. When you've selected all the objects
you want to import, click OK.
If a solution instructs you to import a module from one of the sample
databases that you've already imported (for a
different solution), you can ignore the instruction. Any modules with
matching names in the sample database contain the exact same code, so
you needn't import it again.
How Do I Create an Event Macro?
Programming in Access often depends on
having macros or VBA procedures reacting to events that occur as you
interact with forms. You'll find that most of the
solutions in this book use VBA code rather than macros, because code
provides better control and safety. But occasionally a macro is the
right tool for the job. To create a macro that will react to a user
event, follow these steps:
Select the appropriate object (report, form, or control) and make
sure the properties window is displayed.
Choose the Event properties page on the properties window, or just
scroll down the list until you find the event property you need.
Click on the
ellipsis (...) button to the right of the event name, as shown in
Figure P-4. This is the Build button; it appears next to properties
window items that have associated builders. In this case, clicking
the Build button displays the Choose Builder dialog, shown in Figure
P-5. Choose the Macro Builder item to create a new macro. (If you
don't often use macros, in the Tools
Options dialog, on the Forms/Reports page, you can choose to
"Always use event procedures". The
Build button will immediately take you to the Visual Basic Editor.)
Figure P-4. Press the Build button to invoke the Choose Builder dialog
Figure P-5. The Choose Builder dialog: choose Macro Builder for macros and Code Builder for VBA
Give the macro a name, so Access can save it and place its name in
the properties window. You can always delete it later if you change
your mind. Give your new macro the name suggested in the solution,
and fill in the rows as directed. When you're done,
save the macro and put it away.
Once you're done, you'll see the
name of the macro in the properties window, as shown in Figure P-6.
Whenever the event occurs (the Change event, in this case), Access
will run the associated macro (mcrHandleChange).
Figure P-6. The properties window with the selected macro assigned to the OnChange event property
If you want to call an existing macro from a given event property,
click on the drop-down arrow next to the event name, rather than the
Build button. Choose from the displayed list of available macros
(including macros that exist as part of a macro group).
|
How Do I Create an Event Procedure?
Programming
in Access often depends on having VBA procedures react to events that
occur as you interact with forms or reports. To create a VBA
procedure that will react to a user event, follow these steps:
Select the appropriate object (report, form, or control) and make
sure the properties window is displayed.
Choose the Event Properties page on the properties window, or just
scroll down the list until you find the event property you need.
Select the property, then click the down arrow button next to the
property. Select [Event Procedure] from the list of options.
Click the "..." button to the right
of the event name, as shown in Figure P-7. This is the Build button,
and it appears next to properties window items that have associated
builders. In this case, clicking the Build button takes you to a stub
for the event procedure you need to create.
Figure P-7. Press the Build button to invoke the Choose Builder dialog
Now follow these steps to complete the process:
If the solution asks you to enter code into the event procedure,
enter it between the lines of code that Access has created for you.
Usually, the code example in the solution will include the
Sub and End
Sub statements, so don't enter
them again.
When you're done, close the module window and save
the form. By saving the form or report, you also save the
form's module.
How Do I Place Code in a Form or Report's Module?
When a solution asks you to place a
procedure in a form or report's module that
isn't directly called from an event, follow these
simple steps:
With the form or report open in design mode, choose View
Code, press F7, or click on the Code button on the toolbar, as shown
in Figure P-8.
Figure P-8. Click on the Code toolbar button to view a form or report's module
To create a new procedure, follow the steps in How Do I Create a New Module?,
starting at Step 3.
Choose File Save, close the module, then save the form, or
just click on the Save icon on the toolbar.
How Do I Know What to Do with Code Examples?
In most cases, the solutions suggest that you import a module (or
multiple modules) from the sample database for the particular
solution, rather than typing in code yourself. In fact, code that
isn't referenced as part of the discussion
doesn't show up at all in the body of the solution.
Therefore, you should count on importing modules as directed. Then
follow the instructions in each solution to finish working with and
studying the code.
If the solution tells you to place some code in a
form's module, follow the steps in How Do I Place Code in a Form or Report's Module?. If
you are instructed to place code in a global module, follow the steps
in How Do I Create a New Module?. In most cases, you'll just import
an existing module and won't type anything at all.
How Do I Use Data Access Objects (DAO) in New Databases?
By default, new databases that you create in Access 2000 and later
assume that you'll want to use ActiveX Data Objects
(ADO) rather than the older set of objects for accessing data, DAO.
Many of the examples in this book take advantage of DAO, because
it's simpler, more consistent with earlier
programming techniques, and is in general just as efficient (or more
efficient) than using ADO for programming against Access data (that
is, data stored in an MDB or MDE file). Both ADO and DAO are simply
ActiveX/COM components provided for you by Windows and Microsoft
Office, and before you can use either, you must set a reference to
the appropriate type library.
If you use the projects that come with this book,
you'll find that the code already includes a
reference to the necessary type library so that each example works.
If you create your own projects that use the techniques you find
here, you may need to set a reference to the DAO type library
yourself. Follow these steps to set the reference:
Within the VBA code editor, select the Tools References
menu to display the References dialog box, shown in Figure P-9.
Figure P-9. Set a reference to the Microsoft DAO type library, which allows you to use DAO within applications in Access 2000 and later
Scroll down within the dialog box until you find the reference to
Microsoft DAO, and select it.
Click OK to dismiss the dialog box.
You'll use this same technique to set a reference to
any external component (including Word, Excel, PowerPoint, and
Outlook, in Chapter 14), but
you'll need to set a reference to DAO as shown here
for many of the samples in other chapters.
|
