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?"
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 handlesæ¢ ne 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 major factor of concern
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.
Figure P-2. Use the Tools
Options dialog from within VBA to turn on the Require Variable Declaration
option
Don't Skip This Step!
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.
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 "..." 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
Property Names Versus Event NamesThe naming of event properties, as opposed to the events |
When you create a new event procedure, Access creates the
subroutine name, fills in the parameters that it passes, and places the
subroutine into the form or report's class module. The name of the procedure is
always the name of the object, followed by an underscore and the name of the
event. For example, had you created the Click event procedure for the cmdClose
command button, you'd see a code skeleton like this:
Sub cmdClose_Click( )
End Sub
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
Section P.5.2,
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
Section P.5.6. If you are instructed to place code in a global module,
follow the steps in Section
P.5.2. In most cases, you'll just import an existing module and won't type
anything at all.
No comments:
Post a Comment