|
Programming in Access
Access offers three programming levels:
- Macros
- Switchboard
- Modules
Macros
Access provides a user interface that lets you select from
a list of common activities and functions. You can often select specific
properties for a given step. When you save the macro, Access writes the
VBA code for you. Most users never see the actual code. The user interface
appears below
Note that, having chosen to "Close" an object,
the properties offered were "Object Type" and Object Name".
I selected a Form, and the Object Name combo-box displays the list of
available forms.
Macros are limited to the list of available options. For
instance, the action list includes MessageBox, but it only allows the
programmer to display the simple message with a single button marked "OK".
If you wanted to create a "Yes/No" box, you'd need to create
a switchboard form, or write VBA code in a module.
Switchboard
The switchboard is simply a special form with no bound objects,
but with buttons that activate the access objects you select. It is usually
used to create a "menu" from which the user can select the forms,
reports, and queries you create.
Access provides a utility to create this from. Select Tools->Database
Utilities ->Switchboard Manager. If no switchboard exists, it asks
if you want to create one. You click the buttons to add items. Each item
will appear as a button with the selected text appearing beside it. Clicking
that button will cause the command action to occur. A typical selection
is shown below:
The switchboard, with the button described is shown below:
Again, this is simply a form, which you can edit to change
background colors, button sizes and captions, layouts, etc. You can create
your own switchboard without using the switchboard manager by writing
macros to describe the steps to be taken by each button, and then creating
a form with buttons linked to those macros.
Modules
Finally, you can write VBA code directly, just as you did
with Excel, by creating a module. You will need to identify the objects
you want to control. In the case of forms or objects like text boxes,
you need to know their names. You can create some very powerful programs
by creating queries and forms to display them, and then writing VBA code
to control how the form appears based on user selections.
As an example, you may have noticed that when we create
combo boxes, they don't always update as you scroll through the available
records. Hands-on Excercise No. 1 on page 373 in the Access book walks
you through the procedure to write VBA code to make this update happen.
You need to know the name of the combo box (In their example it's named
"Combo37"), and the name of the field you want it to display
(In this example, you select from a list of names, but when selected,
it shows the student's SSN. That field is named "SocialSecurityNumber")
The program is still event-controlled: you need to decide
what event triggers the program. In this example, the box should update
each time the form refreshes. The name for that event is "On Current".
In the design screen for the form, turn on properties, select the Event
tab, and find "On Current". Select the code builder, and you
should see a new sub called "Form_Current( )" That sub will
run each time the form updates. What you want to happen is the text in
you combobox be reset to the information for the record currently being
displayed. this requires one line of code:
<Combo box name> = <Field name>
In the textbook example, this reads:
Combo37 = SocialSecurityNumber
Whenever a new record is selected, this event is triggered. VBA looks
in the filed named SocialSecurityNumber, finds a text string,and puts
it in the combobox. What you see on the form is that the text displayed
in the combobox corresponds to the current record.
People who program a lot with Access end up learning enough
SQL, a generic database control language, to select specific parts of
a table for use. If you need those skills, I recommend you find a book
like "Access 2000 Programming: A weekend crash course" by Prauge
and Reardon.
Startup
Programmers often want a specific action, like opening the
switchboard, to occur when the database is opened. You can do this in
two ways. One is to name a macro "AutoExec". Access looks for
this macro name as it loads, and runs this macro automatically. If that
macro exists, and you don't want it to run, hold down the shift key as
the database loads.
Another option is to use the Tools->Startup menu to define
the actions to take. The following form appears:
The application title and icon appear where the default
words "MS-Access" show in the main frame. This method allow
the user to control menus and decide what and how much of Access appears
when the database loads.
|
