New Perspectives on Microsoft Office Access 2003 Tutorial 11

1
Microsoft Office Access 2003

• Tutorial 11 Using and Writing Visual Basic for
  Applications Code

2
Learn about VBA

• When you work in Access, in the background,
  Visual Basic for Applications (VBA) code is being
  created.
• You can also write your own VBA code that will
  alter the properties of objects, perform
  calculations, and many other custom actions.
• Recall that an event is something that happens
  while using the database.
• You can write a statement that, when an event
  occurs, responds by executing a series of VBA
  statements.

3
Function procedures, Sub procedures, and modules

• Statements are grouped together into procedures,
  which can be either a function or a Sub
  procedure.
• Common procedures are usually stored together
  into a module.
• A module can be a standard module (stored in
  memory with other database objects) or it can be
  a class module (stored in association with a
  particular form or report).
• With class modules, the procedures are available
  by the form or report for which the class module
  was created.

4
The structure of a VBA module
5
Learning VBA takes time and effort

• Learning to write VBA code efficiently and
  accurately is the hardest part of learning to use
  Access.
• In this tutorial, you are getting just a small
  view of VBA code.
• You should keep in mind that you will not be
  considered a proficient VBA programmer following
  this tutorial.
• Rather, if you want to be a database developer,
  you should probably take another course geared
  towards VBA programming.

6
Review and modify an existing Sub procedure in an
event procedure

• When a class module already exists, you can view
  the code for the procedures in the VBA editor
• Open the property sheets for the object and then
  locate the event for which the procedure is
  written
• Click the Build button to open the Visual Basic
  window, which reveals the code stored in the
  class module
• The window in which the code appears is called
  the Code window.

7
Use assignment statements

• Procedures are enclosed between the Sub statement
  and the End Sub statement.
• Within a procedure, you will see statements that
  work together called control structures.
• Assignment statements assign a value to a field
  or property.
• In some cases, an assignment statement might
  assign a new value to an object property.
• You can make changes to VBA code in the Code
  window.

8
Event properties for a form
9
The Visual Basic Code window
10
Recognize VBA statements

• In the Code window (shown in the previous slide)
  you would view, edit, and test your VBA
  statements.
• You will probably not understand the statements
  that appear in this window.
• However, there are a couple of statements that
  you will recognize
• The first statement is the Private Sub statement
• The last statement is the End Sub statement
• In this sample code, the ForeColor is changed
  under certain circumstances.
• The code used the RGB function, which will return
  a specified colored by using combinations of Red,
  Green, and Blue

11
A function that modifies an objects color
12
RGB values for some colors
13
Create Function proceduresin a standard module

• To create new procedures click Modules on the
  objects bar and then click New.
• This will open the Code window in the Visual
  Basic window.
• To write a Sub procedure,begin with the Sub
  statement.
• To write a Function procedure, begin with the
  Function statement.
• The Function will end with the End Function
  statement.

14
Assign a name to a function

• The Function statement is then followed with the
  name of the function.
• If you will be passing values to the Function,
  the Function name is followed by a Parameter list
  (which could be just one item).
• When you pass a value to a Function, it is called
  an argument.
• When naming a Sub procedure or a function, you
  must follow the rules for naming objects.

15
Define a function in the Code window
16
Name and save your module

• Once you have written your Function, you will
  need to save the module that holds the function.
• When you click the Save button, you will be
  prompted to supply a name for the module.
• This is not the same as naming the Function and
  you do not have to follow the name rules
  mentioned previously.
• Recall that a module will hold a collection of
  procedures so you will want to name the module
  something that will indicate what is inside the
  module.

17
Use comments in your functions
18
Create event procedures

• VBA is an event-driven language, meaning that
  when events takes place, procedures are
  triggered.
• All event procedures are Sub procedures.
• Access will automatically name the event in a
  standard way, which includes
• The name of the control
• An underscore
• The event name

19
Add an event procedure

• To add an event procedure to a class module
• Open the object to which it will be attached
• Open its property sheet
• On the Event tab, select the event that will
  trigger the function
• This will open the code window for the object you
  have selected.
• Before you run your procedure, it must first be
  compiled.
• The process of compilation is the process of
  converting the code to a format the computer can
  understand.

20
Use the If statement

• A commonly used control structure is the If
  statement
• The If statement provides a condition and then if
  the condition is true, a set of statements is
  executed if it is false, a different set of
  statements is executed.
• The If statement created in your tutorial
  incorporates the IsNull function into it.
• The IsNull function returns a True value if the
  argument is empty (no text) and it returns a
  False value if the argument is not empty.

21
An example of an If statement
22
The function execution process
23
An example of an event procedure
24
Declare variables in functions and procedures
25
Primary VBA Data Types
26
Compile and test Function procedures, Sub
procedures and event procedures

• When you compile a module, Access will look for
  errors in syntax.
• If any errors are found, an error message will
  display. You will need to fix all syntax errors
  before the modules can complete compilation.
• When no errors exist, Access will translate the
  procedure.
• You should compile a module any time you make
  changes to make sure there are no syntax errors
  in its procedures.
• You should also test each procedure to make sure
  it performs as you expect.

27
Modify object properties in VBA code

• Sometimes you will want values in your database
  to appear differently in certain circumstances.
• For example, you might want unpaid balances to
  appear in red and paid balances to appear in
  green.
• You might also want to hide a control under
  certain circumstances but display it in other
  circumstances.
• These kinds of things can be accomplished with
  VBA statements.

28
Hide text and change display colors

• The ForeColor property of an object represents
  the color of the text in the object.
• You can write statements that will alter this
  property when a certain condition arises
• The Visible property of an object is set to True
  when you want the object to be displayed and
  False when you want it hidden.
• This property can be altered by writing VBA
  statements to change the property under certain
  circumstances.
• Finally, you can use the DoCmd statement to
  execute an action in a function.

29
Hiding and Changing Display Colors
30
An event procedure to modify properties